Oracle索引聚簇因子的含义及重要性
索引统计信息中需要我们最为重点关注的是CLUSTERING_FACTOR(聚簇因子)。
在Oracle数据库中，聚簇因子是指按照索引键值排序的索引行和存储于对应表中数据行的存储顺序和相似度。Oracle是按照如下的算法来计算聚簇因子的值：
聚簇因子的初始值为1。
Oracle首先定位到目标索引处于最左边的叶子块。
从最左边的叶子块的第一个索引键值所在的索引行开始顺序扫描，在顺序扫描的过程中，Oracle会比对当前索引行的rowid和它之前的那个索引行(它们是相邻的关系)的rowid，如果这两个rowid并不是指向同一个表块，那么Oracle就将聚簇因子的当前值递增1；如果这两个rowid是指向同一个表块，Oracle就不改变聚簇因子的当前值。注意，这里Oracle在比对rowid时不需要回表去访问相应的表块。
上述比对rowid的过程会一直持续下去，直到顺序扫描完目标索引所有叶子块里的所有索引行。
上述顺序扫描操作完成后，聚簇因子的当前值就是索引统计信息中的CLUSTERING_FACTOR，Oracle会将其存储在数据字典里。
从上述聚簇因子的算法可以知道，如果聚簇因子的值接近对应表的表块的数量，则说明目标索引索引行和存储于对应表中数据行的存储顺序相似程度非常高。这也就意味着Oracle走索引范围扫描后取得目标rowid再回表去访问对应表块的数据时，相邻的索引行所对应的rowid极有可能处于同一个表块中，即Oracle在通过索引行记录的rowid回表第一次读取对应的表块并将该表块缓存在buffer cache中后，当再通过相邻索引行记录的rowid回表第二次读取对应的表块时，就不需要再产生物理I/O了，因为这次要访问的和上次已经访问过的表块是同一个块，Oracle已经将其缓存在了buffer cache中。而如果聚簇因子的值接近对应表的记录数，则说明目标索引索引行和存储于对应表中数据行的存储顺序和相似程度非常低，这也就意味着Oracle走索引范围扫描取得目标rowid再回表去访问对应表块的数据时，相邻的索引行所对应的rowid极有可能不处于同一个表块中，即Oracle在通过索引行记录的rowid回表第一次去读取对应的表块并将表块缓存在buffer cache中后，当再通过相邻索引行记录的rowid回表第二次读取对应的表块时，还需要再产生物理I/O，因为这次要访问的和上次已经访问过的表块并不是同一个块。
换句话说，聚簇因子高的索引走索引范围扫描时比相同条件下聚簇因子低的索引要耗费更多的物理I/O，所以聚簇因子高的索引走索引范围扫描的成本会比相同条件下聚簇因子低的索引走索引范围扫描的成本高。
这里构造一个非常极端的例子，全索引中没有任何相邻的索引行记录的rowid指向表中相同的数据块:
根据上述聚簇因子的算法，我们可以算出此索引IDX_T1的聚簇因子的值应是20。
zx@MYDB>create table t1 (id number,name char(1200));
Table created.
zx@MYDB>insert into t1 values(1,'1');
1 row created.
zx@MYDB>insert into t1 values(3,'3');
1 row created.
zx@MYDB>insert into t1 values(5,'5');
1 row created.
zx@MYDB>insert into t1 values(7,'7');
1 row created.
zx@MYDB>insert into t1 values(9,'9');
1 row created.
zx@MYDB>insert into t1 values(11,'11');
1 row created.
zx@MYDB>insert into t1 values(13,'13');
1 row created.
zx@MYDB>insert into t1 values(15,'15');
1 row created.
zx@MYDB>insert into t1 values(17,'17');
1 row created.
zx@MYDB>insert into t1 values(19,'19');
1 row created.
zx@MYDB>insert into t1 values(2,'2');
1 row created.
zx@MYDB>insert into t1 values(4,'4');
1 row created.
zx@MYDB>insert into t1 values(6,'6');
1 row created.
zx@MYDB>insert into t1 values(8,'8');
1 row created.
zx@MYDB>insert into t1 values(10,'10');
1 row created.
zx@MYDB>insert into t1 values(12,'12');
1 row created.
zx@MYDB>insert into t1 values(14,'14');
1 row created.
zx@MYDB>insert into t1 values(16,'16');
1 row created.
zx@MYDB>insert into t1 values(18,'18');
1 row created.
zx@MYDB>insert into t1 values(20,'20');
1 row created.
zx@MYDB>commit;
Commit complete.
zx@MYDB>create index idx_t1 on t1(id);
Index created.
zx@MYDB>col location for a10
zx@MYDB>select id,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location from t1 order by location,id;
ID LOCATION
---------- ----------
1 4_300
3 4_300
5 4_300
7 4_300
9 4_300
11 4_301
13 4_301
15 4_301
17 4_301
19 4_301
2 4_302
4 4_302
6 4_302
8 4_302
10 4_302
12 4_303
14 4_303
16 4_303
18 4_303
20 4_303
20 rows selected.从上述显示结果可以看出1、3、5、7、9在4号文件的300号数据块内，11、13、15、17、19在4号文件的301号数据块内，2、4、6、8、10在4号文件的第302号数据块内，12、14、16、18、20在4号文件的第303号数据块内。
收集统计信息并查看聚簇因子的值
#收集统计信息并查看聚簇因子的值
zx@MYDB>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T1',method_opt=>'for all columns size auto',cascade=>true,estimate_percent=>100);
PL/SQL procedure successfully completed.
zx@MYDB>select index_name,clustering_factor from dba_indexes where index_name='IDX_T1';
INDEX_NAME CLUSTERING_FACTOR
------------------------------------------------------------------------------------------ -----------------
IDX_T1 20在Oracle数据库中，能够降低目标索引的聚簇因子的唯一方法就是对表中的数据按照目标索引的索引键值排序后重新存储。需要注意的是，这种按某一个目标索引的索引键值排序后重新存储表中数据的方法确实可以降低该目标索引聚簇因子的值 ，但可能会同时增加该表上存在的其他索引值的聚簇因子的值。
将表T1的数据原封不动的照搬到表T2中，只不过表T2的数据在存储时已经按id列排好序了
zx@MYDB>create table t2 as select * from t1 order by id;
Table created.
zx@MYDB>create index idx_t2 on t2(id);
Index created.
zx@MYDB>select id,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location from t2 order by location,id;
ID LOCATION
---------- ----------
1 4_171
2 4_171
3 4_171
4 4_171
5 4_171
6 4_172
7 4_172
8 4_172
9 4_172
10 4_172
11 4_173
12 4_173
13 4_173
14 4_173
15 4_173
16 4_174
17 4_174
18 4_174
19 4_174
20 4_174
20 rows selected.
zx@MYDB>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T1',method_opt=>'for all columns size auto',cascade=>true,estimate_percent=>100);
PL/SQL procedure successfully completed.
zx@MYDB>select index_name,clustering_factor from dba_indexes where index_name='IDX_T2';
INDEX_NAME CLUSTERING_FACTOR
------------------------------------------------------------------------------------------ -----------------
IDX_T2 4重复与表T1相同的一系列的操作，从结果可以看出索引IDX_T2的聚簇因子降为了4。而相邻的数据也都在同一数据块中。
在Oracle数据库里，CBO在计算索引范围扫描(Index Range Scan)的成本时会使用如下公式：
IRS Cost = I/O Cost + CPU Cost
而I/O Cost的计算公式为：
I/O Cost = Index Access I/O Cost + Table Access I/O Cost
Index Access I/O Cost = BLEVEL + CEIL(#LEAF_BLOCKS * IX_SEL)
Table Access I/O Cost = CEIL(CLUSTERING_FACTOR * IX_SEL_WITH_FILTERS)
从这个公式可以推断走索引范围扫描的成本可以近似看作是和聚簇因子成正比，因此，聚簇因子值的大小实际上对CBO判断是否走相关的索引起着至关重要的作用。
演示一个例子，通过修改聚簇索引的值就让原本走索引范围扫描的执行计划变成了走全表扫描：
zx@MYDB>create table t1 as select * from dba_objects;
Table created.
zx@MYDB>create index idx_t1 on t1(object_id);
Index created.
zx@MYDB>select clustering_factor from dba_indexes where index_name='IDX_T1';
CLUSTERING_FACTOR
-----------------
1063
zx@MYDB>select /*+ cluster_factor_expmple_1 */ object_id,object_name from t1 where object_id between 103 and 108;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1
6 rows selected.
zx@MYDB>select * from table(dbms_xplan.display_cursor(null,null,'all'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ga3jv3kwwwmx5, child number 0
-------------------------------------
select /*+ cluster_factor_expmple_1 */ object_id,object_name from t1
where object_id between 103 and 108
Plan hash value: 50753647
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 6 | 474 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 6 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
......省略部分输出SQL走了索引范围扫描，成本值为3
使用Hint强制SQL走全表扫描：
zx@MYDB>select /*+ full(t1) */ object_id,object_name from t1 where object_id between 103 and 108;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1
6 rows selected.
zx@MYDB>select * from table(dbms_xplan.display_cursor(null,null,'all'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b7hjwuvmg2ncy, child number 0
-------------------------------------
select /*+ full(t1) */ object_id,object_name from t1 where object_id
between 103 and 108
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 287 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 6 | 474 | 287 (1)| 00:00:04 |
--------------------------------------------------------------------------
......省略部分输出现在SQL走全表扫描，成本值为287。
我们已经知道走索引范围扫描的成本可以近似看作是和聚簇因子成正比，所以如果想让上述SQL的执行计划从索引范围扫描变为全表扫描，那么只需要调整聚簇因子的值，使走索引范围扫描的成本值大于走全表扫描的成本值346即可达到目的。
先将索引IDX_T1的聚簇因子的值手工调整为100万：
zx@MYDB>exec dbms_stats.set_index_stats(ownname=>'ZX',indname=>'IDX_T1',clstfct=>1000000,no_invalidate=>false);
PL/SQL procedure successfully completed.
zx@MYDB>select clustering_factor from dba_indexes where index_name='IDX_T1';
CLUSTERING_FACTOR
-----------------
1000000
zx@MYDB>select /*+ cluster_factor_expmple_2 */ object_id,object_name from t1 where object_id between 103 and 108;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1
6 rows selected.
zx@MYDB>select * from table(dbms_xplan.display_cursor(null,null,'all'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1ucqsj4j0j432, child number 0
-------------------------------------
select /*+ cluster_factor_expmple_2 */ object_id,object_name from t1
where object_id between 103 and 108
Plan hash value: 50753647
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 105 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 6 | 474 | 105 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 6 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
......省略部分输出从执行计划中可以看出，成本已经由3变为105(即增加了102)，这说明我们对索引IDX_T1的聚簇因子的调整生效了。
要使成本值大于287，只需要把聚簇因子的值调整到400万。
zx@MYDB>exec dbms_stats.set_index_stats(ownname=>'ZX',indname=>'IDX_T1',clstfct=>4000000,no_invalidate=>false);
PL/SQL procedure successfully completed.
zx@MYDB>select clustering_factor from dba_indexes where index_name='IDX_T1';
CLUSTERING_FACTOR
-----------------
4000000
zx@MYDB>select /*+ cluster_factor_expmple_3 */ object_id,object_name from t1 where object_id between 103 and 108;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1
6 rows selected.
zx@MYDB>select * from table(dbms_xplan.display_cursor(null,null,'all'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cwkc8q61bypa6, child number 0
-------------------------------------
select /*+ cluster_factor_expmple_3 */ object_id,object_name from t1
where object_id between 103 and 108
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 287 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 6 | 474 | 287 (1)| 00:00:04 |
--------------------------------------------------------------------------从上面显示的内容可以看出执行计划从索引范围扫描变成了全表扫描。
参考《基于Oracle的SQL优化》
转载于:https://blog.51cto.com/hbxztc/1901258