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>