Oracle索引聚簇因子的含义及重要性
发表于：2024-11-15 作者：千家信息网编辑
千家信息网最后更新 2024年11月15日，索引统计信息中需要我们最为重点关注的是CLUSTERING_FACTOR(聚簇因子)。在Oracle数据库中，聚簇因子是指按照索引键值排序的索引行和存储于对应表中数据行的存储顺序和相似度。Oracle
千家信息网最后更新 2024年11月15日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 a10zx@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_30320 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_17420 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----------------- 1063zx@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_ACCESS16 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 t1where object_id between 103 and 108Plan 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_ACCESS16 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_idbetween 103 and 108Plan 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----------------- 1000000zx@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_ACCESS16 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 t1where object_id between 103 and 108Plan 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----------------- 4000000zx@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_ACCESS16 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 t1where object_id between 103 and 108Plan 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优化》
索引
因子
数据
范围
成本
目标
存储
顺序
相同
信息
文件
统计
调整
相似
公式
叶子
指向
数据库
物理
算法
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
网络安全政治思想教育
区委网信办网络安全
树莓派p1108打印机服务器
售后服务好的服务器监控工具
恒联科技互联网快速转店公司
原神不同服务器怎么登
南信的计算机网络技术好不好
客户通的服务器地址连不通
做网站需要自己有服务器吗
网络安全调研报告总结
互联网 科技中国小分队
义乌电脑软件开发要求
灵寿互联网软件开发设计质量保障
护卫神服务器安全设置
网络技术就业前景怎么样
对网络安全进行分级
三级数据库技术报考要求
广州大学网络安全方班
装配式软件开发教程
便携ai服务器
深圳项目软件开发费用是多少
服务器不识别sftp-p命令
家电类软件开发
社区服务中心网络安全
金融网络安全与信息化
我的世界远古之梦服务器图纸
加强党政机关网络安全管理
数据库class是什么类型
全球网络安全监控
北京万众凯旋互联网科技
