what is clustered index in Oracle?

Answer 1

The concept behind indexes is to change the order of the data (clustered index) or to add metadata (non-clustered index) for improving the performance of queries.

Clustered indexes

1. Physically stored in order (ascending or descending)
2. Only one per table
3. When a primary key is created a clustered index is automatically created as well.
4. If the table is under heavy data modifications or the primary key is used for searches, a clustered index on the primary key is recommended.
5. Columns with values that will not change at all or very seldom, are the best choices.

A clustered index the table records are physically sorted on the basic of that index. A table can have only one clustered index.

A SQL*Plus routine to locate clustered indexes

rem idx_bad1.sql, ? 1997 by Donald K. Burleson
set pagesize 60;
set linesize 100;

column c0 heading 'Table'      format a8;
column c1 heading 'Index'      format a18;
column c2 heading 'Level'      format 999;
column c3 heading 'Clust Ftr'  format 9,999,999;
column c4 heading '# rows'     format 99,999,999;
column c5 heading 'Clust Pct'  format 999.9999;
column c6 heading 'dist. Keys' format 99,999,999;

spool idx_bad1.lst;

break on c0 skip 1;

select
  dba_indexes.table_name                            c0,
  index_name                                        c1,
  blevel                                            c2,
  clustering_factor                                 c3,
  num_rows                                          c4,
  decode(clustering_factor,0,1,clustering_factor)/
  decode(num_rows,0,1,num_rows)                     c5,
  distinct_keys                                     c6
from dba_indexes, dba_tables
where
dba_indexes.owner not in ('SYS','SYSTEM')
and
dba_tables.table_name = dba_indexes.table_name
and c5 < .25
order by c0, c5 desc;

spool off;

Here is a sample listing from this report:

SQL> @idx_bad1

Table    Index               Level Clust Ftr      # rows     reorg  dist. Keys
-------- ------------        ----- ---------- ----------   -------  ----------
INV_LINE INV_LINE_ITEM_PK       2     62,107   1,910,034     .0325   1,912,644
         ILI_FK_INV             2    164,757   1,910,034     .0339   1,659,625
         ILI_FK_ACT             2    283,343   1,910,034     .0436          47
         ILI_EK_CCHS_ACCT       3  1,276,987   1,910,034     .1450      25,041