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>createtablet1(idnumber,namechar(1200));

Tablecreated.

zx@MYDB>insertintot1values(1,'1');

1rowcreated.

zx@MYDB>insertintot1values(3,'3');

1rowcreated.

zx@MYDB>insertintot1values(5,'5');

1rowcreated.

zx@MYDB>insertintot1values(7,'7');

1rowcreated.

zx@MYDB>insertintot1values(9,'9');

1rowcreated.

zx@MYDB>insertintot1values(11,'11');

1rowcreated.

zx@MYDB>insertintot1values(13,'13');

1rowcreated.

zx@MYDB>insertintot1values(15,'15');

1rowcreated.

zx@MYDB>insertintot1values(17,'17');

1rowcreated.

zx@MYDB>insertintot1values(19,'19');

1rowcreated.

zx@MYDB>insertintot1values(2,'2');

1rowcreated.

zx@MYDB>insertintot1values(4,'4');

1rowcreated.

zx@MYDB>insertintot1values(6,'6');

1rowcreated.

zx@MYDB>insertintot1values(8,'8');

1rowcreated.

zx@MYDB>insertintot1values(10,'10');

1rowcreated.

zx@MYDB>insertintot1values(12,'12');

1rowcreated.

zx@MYDB>insertintot1values(14,'14');

1rowcreated.

zx@MYDB>insertintot1values(16,'16');

1rowcreated.

zx@MYDB>insertintot1values(18,'18');

1rowcreated.

zx@MYDB>insertintot1values(20,'20');

1rowcreated.

zx@MYDB>commit;

Commitcomplete.

zx@MYDB>createindexidx_t1ont1(id);

Indexcreated.

zx@MYDB>collocationfora10
zx@MYDB>selectid,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid)locationfromt1orderbylocation,id;

IDLOCATION
--------------------
14_300
34_300
54_300
74_300
94_300
114_301
134_301
154_301
174_301
194_301
24_302
44_302
64_302
84_302
104_302
124_303
144_303
164_303
184_303
204_303

20rowsselected.

从上述显示结果可以看出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>execdbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T1',method_opt=>'forallcolumnssizeauto',cascade=>true,estimate_percent=>100);

PL/sqlproceduresuccessfullycompleted.

zx@MYDB>selectindex_name,clustering_factorfromdba_indexeswhereindex_name='IDX_T1';

INDEX_NAMECLUSTERING_FACTOR
-----------------------------------------------------------------------------------------------------------
IDX_T120

在Oracle数据库中，能够降低目标索引的聚簇因子的唯一方法就是对表中的数据按照目标索引的索引键值排序后重新存储。需要注意的是，这种按某一个目标索引的索引键值排序后重新存储表中数据的方法确实可以降低该目标索引聚簇因子的值，但可能会同时增加该表上存在的其他索引值的聚簇因子的值。

将表T1的数据原封不动的照搬到表T2中，只不过表T2的数据在存储时已经按id列排好序了

zx@MYDB>createtablet2asselect*fromt1orderbyid;

Tablecreated.

zx@MYDB>createindexidx_t2ont2(id);

Indexcreated.

zx@MYDB>selectid,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid)locationfromt2orderbylocation,id;

IDLOCATION
--------------------
14_171
24_171
34_171
44_171
54_171
64_172
74_172
84_172
94_172
104_172
114_173
124_173
134_173
144_173
154_173
164_174
174_174
184_174
194_174
204_174

20rowsselected.

zx@MYDB>execdbms_stats.gather_table_stats(ownname=>'ZX',clustering_factorfromdba_indexeswhereindex_name='IDX_T2';

INDEX_NAMECLUSTERING_FACTOR
-----------------------------------------------------------------------------------------------------------
IDX_T24

重复与表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>createtablet1asselect*fromdba_objects;

Tablecreated.

zx@MYDB>createindexidx_t1ont1(object_id);

Indexcreated.

zx@MYDB>selectclustering_factorfromdba_indexeswhereindex_name='IDX_T1';

CLUSTERING_FACTOR
-----------------
1063

zx@MYDB>select/*+cluster_factor_expmple_1*/object_id,object_namefromt1whereobject_idbetween103and108;

OBJECT_IDOBJECT_NAME
----------------------------------------
103MIGRATE$
104DEPENDENCY$
105ACCESS$
106I_DEPENDENCY1
107I_DEPENDENCY2
108I_ACCESS1

6rowsselected.

zx@MYDB>select*fromtable(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sql_IDga3jv3kwwwmx5,childnumber0
-------------------------------------
select/*+cluster_factor_expmple_1*/object_id,object_namefromt1
whereobject_idbetween103and108

Planhashvalue:50753647

--------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time|
--------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||3(100)||
|1|TABLEACCESSBYINDEXROWID|T1|6|474|3(0)|00:00:01|
|*2|INDEXRANGESCAN|IDX_T1|6||2(0)|00:00:01|
--------------------------------------------------------------------------------------
......省略部分输出

sql走了索引范围扫描，成本值为3

使用Hint强制sql走全表扫描：

zx@MYDB>select/*+full(t1)*/object_id,'all'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sql_IDb7hjwuvmg2ncy,childnumber0
-------------------------------------
select/*+full(t1)*/object_id,object_namefromt1whereobject_id
between103and108

Planhashvalue:3617692013

--------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time|
--------------------------------------------------------------------------
|0|SELECTSTATEMENT||||287(100)||
|*1|TABLEACCESSFULL|T1|6|474|287(1)|00:00:04|
--------------------------------------------------------------------------
......省略部分输出

现在sql走全表扫描，成本值为287。

我们已经知道走索引范围扫描的成本可以近似看作是和聚簇因子成正比，所以如果想让上述sql的执行计划从索引范围扫描变为全表扫描，那么只需要调整聚簇因子的值，使走索引范围扫描的成本值大于走全表扫描的成本值346即可达到目的。

先将索引IDX_T1的聚簇因子的值手工调整为100万：

zx@MYDB>execdbms_stats.set_index_stats(ownname=>'ZX',indname=>'IDX_T1',clstfct=>1000000,no_invalidate=>false);

PL/sqlproceduresuccessfullycompleted.

zx@MYDB>selectclustering_factorfromdba_indexeswhereindex_name='IDX_T1';

CLUSTERING_FACTOR
-----------------
1000000

zx@MYDB>select/*+cluster_factor_expmple_2*/object_id,'all'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sql_ID1ucqsj4j0j432,childnumber0
-------------------------------------
select/*+cluster_factor_expmple_2*/object_id,object_namefromt1
whereobject_idbetween103and108

Planhashvalue:50753647

--------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time|
--------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||105(100)||
|1|TABLEACCESSBYINDEXROWID|T1|6|474|105(0)|00:00:02|
|*2|INDEXRANGESCAN|IDX_T1|6||2(0)|00:00:01|
--------------------------------------------------------------------------------------
......省略部分输出

从执行计划中可以看出，成本已经由3变为105(即增加了102)，这说明我们对索引IDX_T1的聚簇因子的调整生效了。

要使成本值大于287，只需要把聚簇因子的值调整到400万。

zx@MYDB>execdbms_stats.set_index_stats(ownname=>'ZX',clstfct=>4000000,no_invalidate=>false);

PL/sqlproceduresuccessfullycompleted.

zx@MYDB>selectclustering_factorfromdba_indexeswhereindex_name='IDX_T1';

CLUSTERING_FACTOR
-----------------
4000000

zx@MYDB>select/*+cluster_factor_expmple_3*/object_id,'all'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sql_IDcwkc8q61bypa6,childnumber0
-------------------------------------
select/*+cluster_factor_expmple_3*/object_id,object_namefromt1
whereobject_idbetween103and108

Planhashvalue:3617692013

--------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time|
--------------------------------------------------------------------------
|0|SELECTSTATEMENT||||287(100)||
|*1|TABLEACCESSFULL|T1|6|474|287(1)|00:00:04|
--------------------------------------------------------------------------

从上面显示的内容可以看出执行计划从索引范围扫描变成了全表扫描。

参考《基于Oracle的sql优化》

原文链接：https://www.f2er.com/oracle/210420.html

Oracle索引聚簇因子的含义及重要性

猜你在找的Oracle相关文章