你的位置:首页 > 数据库

[数据库]Oracle索引梳理系列(九)


版权声明:本文发布于http://www.cnblogs.com/yumiko/,版权由Yumiko_sunny所有,欢迎转载。转载时,请在文章明显位置注明原文链接。若在未经作者同意的情况下,将本文内容用于商业用途,将保留追究其法律责任的权利。如果有问题,请以邮箱方式联系作者(793113046@qq.com)。


 1、聚簇因子的概念

  • 聚簇因子,是CBO优化器决定是否使用索引的因素之一,主要反映索引块上的数据(顺序存储),与该索引基于的表块上的数据(无序存储)的顺序相似程度的差异性。即表数据的存储顺序是否与相应索引数据的存储顺序一致。
  • 通过查询dba_indexes视图、user_indexes视图以及all_indexes视图的CLUSTERING_FACTOR列,可以了解当前索引的聚簇因子值。

 

 

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

  • 对于索引块的数据存储,这里以普通btree索引为例,索引块中键值的分布总是有序的,且根据键值及其相应的rowid信息,唯一定位一行记录在相应表的数据块中的分布。理想情况下,相同或相邻的键值,尽量定位在相同的数据块上,可以避免对于数据块多余的I/O操作。

  • 对于数据块的数据存储,并不是有序存储的。且ORACLE为节省空间,会优先使用当前当水位线(HWM)以下的可用数据块,而不是按序使用最后被使用的块。当HWM以下无可用数据块时,再开辟新的数据块使用。
  • 正因为数据块中数据存储的特点,随着时间的推移,数据在相应数据块间的分布越发零散,进而影响索引块中,相同或相邻键值对应的相应数据行信息(rowid),所指向的数据块越加分散,进而导致聚簇因子变差。

 

 

3、聚簇因子的计算方法

聚簇因子大致的计算方法顺序如下:

  1. 进行一次索引全扫描
  2. 检查索引块中的rowid信息。比较前一个rowid与一个rowid是否指向同一个数据块。若不同,则聚簇因子加1.
  3. 当完成整个的索引扫面后,即得到该索引的聚簇因子的数值。

 

 

4、聚簇因子好坏的判断 

良好的CF值,会趋向于数据表的块数。

较差的CF值,会趋向于数据表的行数。

需要注意的是:随着时间的推移,频繁的DML操作,会让CF值总是趋向于恶劣方向发展。

 

示例:

本示例主要说明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



 

 

5、聚簇因子的优化

由于影响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 100000Yumiko_sunny@OA01> set pages 0Yumiko_sunny@OA01> select dbms_metadata.get_ddl('TABLE',upper('&table_name'),upper('&owner')) from dual;Enter value for table_name: TESTEnter value for owner: SCOTTold 1: select dbms_metadata.get_ddl('TABLE',upper('&table_name'),upper('&owner')) from dualnew 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_tmpYumiko_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_oldYumiko_sunny@OA01> alter table test rename to test_old;Table altered.--将新表test_tmp重命名为testYumiko_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


 

需要补充说明的是:
对于alter table move的操作,可以降低高水位线,但对于优化聚簇因子值而言,意义不大。
对于重建索引,通过实验发现(只进行了两个实验,可能结果集存在误差),聚簇因子值不但未降低,有时还存在些许的增加,需要注意。