Oracle Index Clustering Factor(聚簇因子)

Posted on 2024-05-13 18:46 dclogs 阅读(15) 评论(0) 编辑收藏举报

转自：

一、本文说明：

今天在做测试的时候发现字段上有索引，但是执行计划就是不走索引，经过在网上查找才发现原来是索引的聚簇因子过高导致的。

二、官网说明

The index clustering factor measures row order in relation to an indexed value suches employee last name.The more order that exists in rowstorage for this value,the lower the clustering factor.

----row存储的越有序，clustering factor的值越低。

The clustering factor is useful as a rough measure of the number of I/Os required to read an entire table by means of an index:

(1)、If the clustering factor is high,then Oracle Database performs a relatively high number of I/Os during a large index range scan.The index entriespoint to random table blocks,so the database may have to read and reread the same blocks over and over again to retrieve the data pointed to by the index.

----当clustering factor很高时，说明index entry (rowid) 是随机指向一些block的，在一个大的index range scan时，这样为了读取这些rowid指向的block，就需要一次又一次重复的去读这些block。

(2)、If the clustering factor is low,then Oracle Database performs a relatively low number of I/Os during a large index range scan.The index keys in arange tend to point to the same data blcok,so the database does not have to read and reread the same blocks over and over.

----当clustering factor值低时，说明index keys (rowid) 是指向的记录是存储在相同的block里，这样去读row时，只需要在同一个block里读取就可以了，这样减少重复读取blocks的次数。

The clustering factor is relevant for index scans because it can show:

(1)、Whether the database will use an index for large range scans;

(2)、The degree of table organization in relation to the index key;

(3)、Whether you should consider using an index-organized table,partitioning,or table cluster if rows must be ordered by the index key.

三、Index Clustering Factor说明

简单的说，Index Clustering Factor是通过一个索引扫描一张表，需要访问的表的数据块的数量，即对I/O的影响，也代表索引键存储位置是否有序。

(1)、如果越有序，即相邻的键值存储在相同的block，此时CF因子越好，那么这时候Clustering Factor的值就越低

(2)、如果不是很有序，即键值是随机的存储在block上，此时CF因子不好，这样在读取键值时，可能就需要一次又一次的去访问相同的block，从而增加了I/O

（3)、需要注意的是：随着时间的推移，频繁的DML操作，会让CF值总是趋向于恶劣方向发展

Clustering Factor的计算方式如下：

(1)、扫描一个索引(large index range scan)；

(2)、比较某行的rowid和前一行的rowid，如果这两个rowid不属于同一个数据块，那么cluster factor增加1；

(3)、整个索引扫描完毕后，就得到了该索引的clustering factor。

如果clustering factor接近于表存储的块数，说明这张表是按照索引字段的顺序存储的。

如果clustering factor接近于行的数量，那说明这张表不是按索引字段顺序存储的。

在计算索引访问成本的时候，这个值十分有用。Clustering Factor乘以选择性参数(selectivity)就是访问索引的开销。

如果这个统计数据不能真实反映出索引的真实情况，那么可能会造成优化器错误的选择执行计划。另外如果某张表上的大多数访问是按照某个索引做索引扫描，那么将该表的数据按照索引字段的顺序重新组织，可以提高该表的访问性能。

四、索引块与相应数据块之间数据分布产生差异的原因

对于索引块的数据存储，这里以普通btree索引为例，索引块中键值的分布总是有序的，且根据键值及其相应的rowid信息，唯一定位一行记录在相应表的数据块中的分布。理想情况下，相同或相邻的键值，尽量定位在相同的数据块上，可以避免对于数据块多余的I/O操作。
对于数据块的数据存储，并不是有序存储的。且ORACLE为节省空间，会优先使用当前当水位线（HWM）以下的可用数据块，而不是按序使用最后被使用的块。当HWM以下无可用数据块时，再开辟新的数据块使用。
正因为数据块中数据存储的特点，随着时间的推移，数据在相应数据块间的分布越发零散，进而影响索引块中，相同或相邻键值对应的相应数据行信息（rowid），所指向的数据块越加分散，进而导致聚簇因子变差。

五、示例

本示例主要说明CF的趋势性

--查看当前测试表中索引的聚簇因子情况--注意此时的LAST_ANALYZED为空，说明未收集过统计信息Yumiko_sunny@OA01> select INDEX_NAME,b.TABLE_NAME,CLUSTERING_FACTOR,
  2  a.BLOCKS tb_blocks,b.NUM_ROWS tb_rows,
  3  to_char(c.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
  4  from dba_segments a,dba_indexes b ,dba_tables c 
  5  where a.segment_name=b.table_name 
  6  and a.segment_name=c.table_name 
  7  and b.table_name='TEST';

INDEX_NAME      TABLE_NAME      CLUSTERING_FACTOR   TB_BLOCKS    TB_ROWS LAST_ANALYZED       
--------------- --------------- -----------------  ---------- ---------- -------------------
TEST_IDX        TEST                        17381       18432    1217342                    



--分析收集表test最新的统计信息
Yumiko_sunny@OA01> analyze table test compute statistics;
Table analyzed.


--查看收集后最新的信息，可以看到，结果集中CF值，明显小于数据块值，说明此时情况相似度很好。
Yumiko_sunny@OA01> select INDEX_NAME,b.TABLE_NAME,CLUSTERING_FACTOR,
  2  a.BLOCKS tb_blocks,b.NUM_ROWS tb_rows,
  3  to_char(c.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
  4  from dba_segments a,dba_indexes b ,dba_tables c 
  5  where a.segment_name=b.table_name 
  6  and a.segment_name=c.table_name 
  7  and b.table_name='TEST';

INDEX_NAME      TABLE_NAME      CLUSTERING_FACTOR   TB_BLOCKS    TB_ROWS LAST_ANALYZED       
--------------- --------------- -----------------  ---------- ---------- ------------------- 
TEST_IDX        TEST                        17381       18432    1217342 2016-11-06 16:38:08



--插入新的数据并进行提交
Yumiko_sunny@OA01> insert into test  select * from test;
1217342 rows created.

Yumiko_sunny@OA01> commit;
Commit complete.



--再次收集表test相关的统计信息
Yumiko_sunny@OA01> analyze table test compute statistics;
Table analyzed.



--不难发现，随着insert的操作，CF值发生了改变，虽然目前该值在可接受范围内，但已经开始趋向行数。--可以想象下，一个生产环境中，除了insert，还有update跟delete，随着这些操作的增多，势必更加趋向行数。
Yumiko_sunny@OA01> select INDEX_NAME,b.TABLE_NAME,CLUSTERING_FACTOR,
  2  a.BLOCKS tb_blocks,b.NUM_ROWS tb_rows,
  3  to_char(c.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
  4  from dba_segments a,dba_indexes b ,dba_tables c 
  5  where a.segment_name=b.table_name 
  6  and a.segment_name=c.table_name 
  7  and b.table_name='TEST';

INDEX_NAME      TABLE_NAME      CLUSTERING_FACTOR   TB_BLOCKS    TB_ROWS LAST_ANALYZED       
--------------- --------------- -----------------  ---------- ---------- ------------------- 
TEST_IDX        TEST                       206123       35840    2434684 2016-11-06 16:39:58

六、聚簇因子的优化

由于影响CF（CLUSTERING_FACTOR）值的主要取决于数据表的数据，在数据块中的存储分布情况，因此优化CF的重点还是在调整数据表本身，具体方法如下：

定期按索引列顺序重建表
- 建议通过dbms_metadata.get_ddl提取表结构完整的DDL语句，结合insert order by column以及rename table的方式进行表的重建。
- 不建议采用CTAS方式（create table as select），该方式可能引起后续不必要的麻烦。具体影响可参阅链接中的案例 http://blog.csdn.net/leshami/article/details/7362156
使用聚簇表代替普通的数据表
- 频繁DML的表以及经常需要全表扫描的表，不适合建立聚簇表。
- 具体查阅作者前面关于“表簇索引”一文的介绍 “Oracle索引种类之表簇索引（cluster index）”

七、通过重建表进行聚簇因子优化的过程

本过程承接上示例：

--再次确认原始表test的CF值Yumiko_sunny@OA01> select INDEX_NAME,b.TABLE_NAME,CLUSTERING_FACTOR,
  2  a.BLOCKS tb_blocks,b.NUM_ROWS tb_rows,
  3  to_char(c.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED 
  4  from dba_segments a,dba_indexes b ,dba_tables c 
  5  where a.segment_name=b.table_name 
  6  and a.segment_name=c.table_name 
  7  and b.table_name='TEST';

INDEX_NAME      TABLE_NAME      CLUSTERING_FACTOR   TB_BLOCKS    TB_ROWS LAST_ANALYZED       
--------------- --------------- -----------------  ---------- ---------- ------------------- 
TEST_IDX        TEST                       206123       35840    2434684 2016-11-06 22:10:51 



--通过调用dbms_metadata包的get_ddl函数，抽取原始表test的DDL结构语句
Yumiko_sunny@OA01> set long 100000
Yumiko_sunny@OA01> set pages 0
Yumiko_sunny@OA01> select dbms_metadata.get_ddl('TABLE',upper('&table_name'),upper('&owner')) from dual;
Enter value for table_name: TEST
Enter value for owner: SCOTT
old   1: select dbms_metadata.get_ddl('TABLE',upper('&table_name'),upper('&owner')) from dual
new   1: select dbms_metadata.get_ddl('TABLE',upper('TEST'),upper('SCOTT')) from dual

  CREATE TABLE "SCOTT"."TEST"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NAMESPACE" NUMBER,
        "EDITION_NAME" VARCHAR2(30)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"


--利用抽取的原始表test的结构语句，创建新表test_tmp
Yumiko_sunny@OA01> CREATE TABLE "SCOTT"."TEST_TMP"
  2     (    "OWNER" VARCHAR2(30),
  3          "OBJECT_NAME" VARCHAR2(128),
  4          "SUBOBJECT_NAME" VARCHAR2(30),
  5          "OBJECT_ID" NUMBER,
  6          "DATA_OBJECT_ID" NUMBER,
  7          "OBJECT_TYPE" VARCHAR2(19),
  8          "CREATED" DATE,
  9          "LAST_DDL_TIME" DATE,
 10          "TIMESTAMP" VARCHAR2(19),
 11          "STATUS" VARCHAR2(7),
 12          "TEMPORARY" VARCHAR2(1),
 13          "GENERATED" VARCHAR2(1),
 14          "SECONDARY" VARCHAR2(1),
 15          "NAMESPACE" NUMBER,
 16          "EDITION_NAME" VARCHAR2(30)
 17     ) SEGMENT CREATION IMMEDIATE
 18    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 19   NOCOMPRESS LOGGING
 20    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 21    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 22    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 23    TABLESPACE "USERS";

Table created.



--通过对原始表test的索引列进行order by排序操作后，差入到新表test_tmp中--通过append hint的方法，虽然可以减少redo的产生，并且在hwm以上开辟数据块，加快了数据的加载速度。--但该方式，在commit或者rollback事物前，其他会话无法针对该表进行DML操作，生产环境中需要注意。
Yumiko_sunny@OA01> insert into /*+append */ test_tmp select * from test order by object_id;
2434684 rows created.

Yumiko_sunny@OA01> commit;


--为新表test_tmp的索引列添加索引
Yumiko_sunny@OA01> create index test_idx_new on test_tmp(object_id);
Index created.


--将原始表test进行重命名test_old
Yumiko_sunny@OA01> alter table test rename to test_old;
Table altered.


--将新表test_tmp重命名为test
Yumiko_sunny@OA01> alter table test_tmp rename to test;
Table altered.



--分析收集新表test的统计信息
Yumiko_sunny@OA01> analyze table TEST compute statistics;
Table analyzed.

--查看新建的表test的CF，不难发现，此时的CF值将较之前已经明显下降。--至此，CF的优化过程结束。
Yumiko_sunny@OA01> select INDEX_NAME,b.TABLE_NAME,CLUSTERING_FACTOR,
  2  a.BLOCKS tb_blocks,b.NUM_ROWS tb_rows,
  3  to_char(c.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
  4  from dba_segments a,dba_indexes b ,dba_tables c 
  5  where a.segment_name=b.table_name 
  6  and a.segment_name=c.table_name 
  7  and b.table_name='TEST';

INDEX_NAME    TABLE_NAME    CLUSTERING_FACTOR   TB_BLOCKS   TB_ROWS  LAST_ANALYZED       
----------------------------------------------------------------------------------------
TEST_IDX_NEW        TEST                39700       35840   2434684  2016-11-06 22:26:10

八、小结

对于append hint方式，如果是归档模式下且需要尽量减少redo日志，那么可以将新表test设置为nologging，alter table test nologging（非归档模式下，只需append就能大量减少redo的产生；归档模式下，只有append+nologging才能大量减少redo）

对于alter table move的操作，可以降低高水位线，但对于优化聚簇因子值而言，意义不大。

对于重建索引，通过实验发现（只进行了两个实验，可能结果集存在误差），聚簇因子值不但未降低，有时还存在些许的增加，需要注意

通过以上说明和测试，可以看到clustering factor也是索引健康的一个重要判断的标准。其值越低越好。它会影响CBO选择正确的执行计划。但是注意一点，clustering factor总是趋势与不断恶化的

会员力量，点亮园子希望

刷新页面返回顶部

DBA学习之路

导航

公告