In-depth understanding of Oracle indexes (16): Clustering factor

Its definition:

Indicates the order of rows in the table based on the index value, and notifies CBO about table row and index synchronization.
● This value is similar to the number of blocks, indicating that the same data is stored in a centralized manner.

● This value is similar to the number of rows, indicating that the order of the rows in the table is different from that of the index.

Its role:

● Table order relative to index
● Number of logical I/O operations performed on the table when reading the entire table through Indexes

Its query:

hr@ORCL> edWrote file afiedt.buf  1  select a.index_name,b.num_rows,  2         b.blocks,a.clustering_factor  3    from user_indexes a,user_tables b  4   where index_name in ('JOB_ID_PK','DEPT_ID_PK') and  5*        a.table_name=b.table_name  6  hr@ORCL> /INDEX_NAME                       NUM_ROWS     BLOCKS CLUSTERING_FACTOR------------------------------ ---------- ---------- -----------------DEPT_ID_PK                             27          5                 1JOB_ID_PK                              19          5                 1

Its significance:

The index is not always an appropriate access method. If you try to recreate the table to make the index have a good clustering factor, it is just a waste of time.