INDEX_NAME CLUSTERING_FACTOR NUM_ROWS
--------------- ----------------- ----------
JACK_IND 10327 725460 ------注意这里的Factor，已经变成10327，我们收集一下表的统计信息，然后与表的block进行一次比较。
SQL> exec dbms_stats.gather_table_stats(user,'jack',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select blocks from dba_tables where table_name='JACK';
BLOCKS
----------
10474 ----表jack实际使用的block是10474，clustering factor是10327基本还是比较接近了，这也说明相邻的row是存储在相同的block里。
SQL> select index_name,clustering_factor,num_rows from user_indexes where index_name='JACK_IND';
INDEX_NAME CLUSTERING_FACTOR NUM_ROWS
------------------------------ ----------------- ----------
JACK_IND 10327 725460
SQL> alter system flush buffer_cache;
System altered.
SQL> set autotrace traceonly;
----再次查看之前sql的执行计划----
SQL> select * from jack where object_id>1000 and object_id<2000;
9880 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2860868395
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9657 | 914K| 162 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| JACK | 9657 | 914K| 162 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | JACK_IND | 9657 | | 24 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">1000 AND "OBJECT_ID"<2000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1457 consistent gets
151 physical reads
0 redo size
988947 bytes sent via SQL*Net to client
7657 bytes received via SQL*Net from client
660 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9880 rows processed
----注意这里的cost已经降到了162，性能提升还是非常明显。
五、小结
通过以上说明和测试，可以看到clustering factor也是索引健康的一个重要判断的标准。其值越低越好。它会影响CBO选择正确的执行计划。但是注意一点，clustering factor总是趋势与不断恶化的。