Oracle PL/SQL Tutorial/System Tables Data Dictionary/user indexes
Getting Information on Indexes
You can get information on your indexes from user_indexes.
<source lang="sql">
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)</source>
show parameter optimizer_index
<source lang="sql">
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></source>
