谭怀远写的让oracle跑得更快2，10053事件那一章介绍trace文件输出内容时，提到一个概念clustoring factor，自己上网搜了下，并做了点实验，现记录一下。
--创建测试表，并在三个字段上分别创建索引
A>create table t as select * from all_objects;
Table created.
A>create index t_owner on t(OWNER);
Index created.
A>create index t_objnm on t(OBJECT_NAME);
Index created.
A>create index t_objid on t(OBJECT_ID);
Index created.
A>select INDEX_NAME as "index",LEAF_BLOCKS as "lb",CLUSTERING_FACTOR as "cluf",NUM_ROWS,AVG_LEAF_BLOCKS_PER_KEY as "lb/k",AVG_DATA_BLOCKS_PER_KEY as "db/k" from user_indexes where TABLE_NAME like 'T%';
index lb cluf NUM_ROWS lb/k db/k
--------------- ---------- ---------- ---------- ---------- ----------
T_OWNER 132 1607 57672 10 123
T_OBJNM 289 26808 57672 1 1
T_OBJID 128 830 57672 1 1
--可以看出同一个表上不同索引具有不同的聚合因子CLUF
A>select TABLE_NAME,INDEX_NAME,UNIQUENESS,DISTINCT_KEYS from user_indexes;
TABLE_NAME INDEX_NAME UNIQUENES DISTINCT_KEYS
--------------- ------------------------------ --------- -------------
T T_OWNER NONUNIQUE 13 //这个地方的13，说明了上边lb/k的10是怎么来的，lb的数量除以key的数量，即132/10=13，但db/k的123是怎么来的呢？
T T_OBJNM NONUNIQUE 32924
T T_OBJID NONUNIQUE 57672
--开始我以为是用总的块数除，但算出来结果不对（820/13并不等于123）
A>select TABLE_NAME,BLOCKS from user_tables;
TABLE_NAME BLOCKS
--------------- ----------
T 820
--知道123怎么来的了（其实db/k表示的是，每个索引Key值平均对应的block数量，所以一会我把每个key对应的块加起来，再除以总共13个key，得到的就是db/k了）
A>select owner,count(distinct dbms_rowid.rowid_block_number(rowid)) as "blocks" from t group by owner;
OWNER blocks
------------------------------ ----------
CTXSYS 7
OLAPSYS 15
PUBLIC 750
OUTLN 2
SYSTEM 27
EXFSYS 7
ORACLE_OCM 2
DBSNMP 2
A 1
XDB 10
APPQOSSYS 1
OWNER blocks
------------------------------ ----------
SYS 777
WMSYS 6
13 rows selected.
--上边每个用户都是一个Key，其对应的块之和计算如下
A>select sum(count(distinct dbms_rowid.rowid_block_number(rowid))) as "blocks" from t group by owner;
blocks
----------
1607
--计算每个owner key平均对应的块数量，正好=db/k
A>select 1607/13 from dual;
1607/13
----------
123.615385
--现在看看CLUF与表中有多少行的关系
A>select count(*) from t;
COUNT(*)
----------
57672
A>create table t2 as select * from all_objects where rownum<57672/2;
Table created.
A>select count(*) from t2;
COUNT(*)
----------
28835
A>create index t2_owner on t2(OWNER);
Index created.
A>create index t2_objnm on t2(OBJECT_NAME);
Index created.
A>create index t2_objid on t2(OBJECT_ID);
Index created.
A>select INDEX_NAME as "index",LEAF_BLOCKS as "lb",CLUSTERING_FACTOR as "cluf",NUM_ROWS,AVG_LEAF_BLOCKS_PER_KEY as "lb/k",AVG_DATA_BLOCKS_PER_KEY as "db/k" from user_indexes where TABLE_NAME like 'T%';
index lb cluf NUM_ROWS lb/k db/k
--------------- ---------- ---------- ---------- ---------- ----------
T_OWNER 132 1607 57672 10 123
T_OBJNM 289 26808 57672 1 1
T_OBJID 128 830 57672 1 1
T2_OWNER 66 802 28835 8 100
T2_OBJNM 138 12515 28835 1 1
T2_OBJID 63 411 28835 1 1
6 rows selected.
--发现CLUF随着行数的减半也跟着减半，即CLUF与行数成正比，1/4时也是如此
A>create table t3 as select * from all_objects where rownum<57672/4;
Table created.
A>create index t3_owner on t3(OWNER);
Index created.
A>create index t3_objnm on t3(OBJECT_NAME);
Index created.
A>create index t3_objid on t3(OBJECT_ID);
Index created.
A>select INDEX_NAME as "index",LEAF_BLOCKS as "lb",CLUSTERING_FACTOR as "cluf",NUM_ROWS,AVG_LEAF_BLOCKS_PER_KEY as "lb/k",AVG_DATA_BLOCKS_PER_KEY as "db/k" from user_indexes where TABLE_NAME like 'T%';
index lb cluf NUM_ROWS lb/k db/k
--------------- ---------- ---------- ---------- ---------- ----------
T_OWNER 132 1607 57672 10 123
T_OBJNM 289 26808 57672 1 1
T_OBJID 128 830 57672 1 1
T2_OWNER 66 802 28835 8 100
T2_OBJNM 138 12515 28835 1 1
T2_OBJID 63 411 28835 1 1
T3_OWNER 33 380 14417 4 47
T3_OBJNM 62 5628 14417 1 1
T3_OBJID 31 200 14417 1 1
9 rows selected.
--再来看看CLUF和数据存储顺序的关系
A>create table t4 as select OBJECT_NAME from all_objects;
Table created.
A>create index t4_objnm on t4(OBJECT_NAME);
Index created.
A>create table t5 as select OBJECT_NAME from all_objects order by OBJECT_NAME;
Table created.
A>create index t5_objnm on t5(OBJECT_NAME);
Index created.
A>create table t6 as select OBJECT_NAME from all_objects order by OBJECT_NAME desc;
Table created.
A>create index t6_objnm on t6(OBJECT_NAME);
Index created.
A>select INDEX_NAME as "index",LEAF_BLOCKS as "lb",CLUSTERING_FACTOR as "cluf",NUM_ROWS,AVG_LEAF_BLOCKS_PER_KEY as "lb/k",AVG_DATA_BLOCKS_PER_KEY as "db/k" from user_indexes where TABLE_NAME in ('T','T4','T5','T6') and INDEX_NAME like '%NM';
index lb cluf NUM_ROWS lb/k db/k
--------------- ---------- ---------- ---------- ---------- ----------
T_OBJNM 289 26808 57672 1 1
T4_OBJNM 289 24285 57685 1 1
T5_OBJNM 289 239 57687 1 1
T6_OBJNM 289 457 57689 1 1
--数据有序存储时，CLUF会显著降低（239<24285，457<24285），但我想不明白的是，为什么升序存储和降序存储，二者的CLUF竟然不一样？还有这个CLUF具体数值究竟是怎么算出来的？
--CLUF和数据块数量的关系
A>create table t7 as select OBJECT_NAME from all_objects where rownum<2;
Table created.
A>alter table t7 minimize records_per_block;
Table altered.
A>insert into t7 select OBJECT_NAME from all_objects;
57691 rows created.
A>create index t7_objnm on t7(OBJECT_NAME);
Index created.
A>select TABLE_NAME,BLOCKS from user_tables where TABLE_NAME in ('T','T4','T5','T6','T7');
TABLE_NAME BLOCKS
------------------------------ ----------
T 820
T4 239
T5 239
T6 239
T7 28851
A>select INDEX_NAME as "index",LEAF_BLOCKS as "lb",CLUSTERING_FACTOR as "cluf",NUM_ROWS,AVG_LEAF_BLOCKS_PER_KEY as "lb/k",AVG_DATA_BLOCKS_PER_KEY as "db/k" from user_indexes where TABLE_NAME in ('T','T4','T5','T6','T7') and INDEX_NAME like '%NM';
index lb cluf NUM_ROWS lb/k db/k
--------------- ---------- ---------- ---------- ---------- ----------
T_OBJNM 289 26808 57672 1 1
T4_OBJNM 289 24285 57685 1 1
T5_OBJNM 289 239 57687 1 1
T6_OBJNM 289 457 57689 1 1
T7_OBJNM 289 49895 57692 1 1
--用相同的手法创建表T8，只不过按照字段object_name升序来存储
A>create table t8 as select OBJECT_NAME from all_objects where rownum<2;
Table created.
A>alter table t8 minimize records_per_block;
Table altered.
A>insert into t8 select OBJECT_NAME from all_objects order by OBJECT_NAME;
57693 rows created.
A>commit;
Commit complete.
A>create index t8_objnm on t8(OBJECT_NAME);
Index created
A>select INDEX_NAME as "index",LEAF_BLOCKS as "lb",CLUSTERING_FACTOR as "cluf",NUM_ROWS,AVG_LEAF_BLOCKS_PER_KEY as "lb/k",AVG_DATA_BLOCKS_PER_KEY as "db/k" from user_indexes where TABLE_NAME in ('T','T4','T5','T6','T7','T8') and INDEX_NAME like '%NM';
index lb cluf NUM_ROWS lb/k db/k
--------------- ---------- ---------- ---------- ---------- ----------
T_OBJNM 289 26808 57672 1 1
T4_OBJNM 289 24285 57685 1 1
T5_OBJNM 289 239 57687 1 1
T6_OBJNM 289 457 57689 1 1
T7_OBJNM 289 49895 57692 1 1
T8_OBJNM 289 28884 57694 1 1
6 rows selected.
对比表T7和T8的索引，发现表T8索引的CLUF下降了
==========================================================
综上，CLUF和表的三个因素有关，分别是
1.行数
2.数据存储顺序
3.数据块数
最后看个特殊情况，看看索引刚刚创建时，CLUF是多少
A>create table one(id int);
Table created.
A>create index one_ind on one(id);
Index created.
A>select INDEX_NAME as "index",LEAF_BLOCKS as "lb",CLUSTERING_FACTOR as "cluf",NUM_ROWS,AVG_LEAF_BLOCKS_PER_KEY as "lb/k",AVG_DATA_BLOCKS_PER_KEY as "db/k" from user_indexes where TABLE_NAME='ONE';
index lb cluf NUM_ROWS lb/k db/k
---------- ---------- ---------- ---------- ---------- ----------
ONE_IND 0 0 0 0 0
其实CLUF应该是data blocks和num_rows两点形成的直线上的任意一点，它偏向data blocks，oracle就优先使用索引；反之当它偏向num_rows时，oracle就倾向于使用全表扫描。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29646619/viewspace-1169160/ ，如需转载，请注明出处，否则将追究法律责任。
