Oracle PL/SQL Tutorial/System Tables Data Dictionary/user indexes
Getting Information on Indexes
You can get information on your indexes from user_indexes.
SQL> desc user_indexes;
Name Null? Type
---------------------------
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)
show parameter optimizer_index
SQL> create table clustered ( x int, data char(255) );
Table created.
SQL>
SQL> insert /*+ append */ into clustered (x, data)
2 select rownum, dbms_random.random from all_objects;
12652 rows created.
SQL>
SQL> alter table clustered
2 add constraint clustered_pk primary key (x);
Table altered.
SQL>
SQL> analyze table clustered compute statistics;
Table analyzed.
SQL>
SQL> create table non_clustered ( x int, data char(255) );
Table created.
SQL>
SQL> insert /*+ append */ into non_clustered (x, data)
2 select x, data from clustered ORDER BY data;
12652 rows created.
SQL>
SQL> alter table non_clustered
2 add constraint non_clustered_pk primary key (x);
Table altered.
SQL>
SQL> analyze table non_clustered compute statistics;
Table analyzed.
SQL>
SQL> select index_name, clustering_factor
2 from user_indexes
3 where index_name like "%CLUSTERED_PK";
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
CLUSTERED_PK 469
NON_CLUSTERED_PK 12626
SQL>
SQL> show parameter optimizer_index
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
SQL> set autotrace traceonly explain
SQL> select * from clustered where x between 50 and 2750;
Execution Plan
----------------------------------------------------------
Plan hash value: 1763666373
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 2702 | 683K| 108 (0)
| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| CLUSTERED | 2702 | 683K| 108 (0)
| 00:00:02 |
|* 2 | INDEX RANGE SCAN | CLUSTERED_PK | 2702 | | 7 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X">=50 AND "X"<=2750)
SQL> select * from non_clustered where x between 50 and 2750;
Execution Plan
----------------------------------------------------------
Plan hash value: 681052411
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 2702 | 683K| 130 (1)| 00:00:0
2 |
|* 1 | TABLE ACCESS FULL| NON_CLUSTERED | 2702 | 683K| 130 (1)| 00:00:0
2 |
--------------------------------------------------------------------------------
---
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"<=2750 AND "X">=50)
SQL> set autotrace off
SQL>
SQL> drop table clustered;
Table dropped.
SQL> drop table non_clustered;
Table dropped.
SQL>