聚簇因子是 Oracle 统计信息中在 CBO 优化器模式下用于计算 COST 的参数之一,决定了当前的 SQL 语句是否走索引,还是走全表扫描以及是否作为嵌套连接外部表等。

当对堆表新建或重建索引时,由于索引列上的顺序是有序的,而表上的顺序是无序的,也就存在了差异,这种差异即表现为聚簇因子。

通过查询 dba_indexes 视图、user_indexes 视图以及 all_indexes 视图的 CLUSTERING_FACTOR 列,可以了解当前索引的聚簇因子值。

什么是聚簇因子

  • 聚簇因子是基于表上索引列上的一个值,每一个索引都有一个聚簇因子
  • 用于描述索引块上与表块上存储数据在顺序上的相似程度,也就说表上的数据行的存储顺序与索引列上顺序是否一致
  • 良好的CF值趋近于表上的块数,而较差的CF值则趋近于表上的行数
  • 聚簇因子在索引创建时就会通过表上存在的行记录以及索引块计算获得

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

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

聚簇因子的计算方法

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

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

聚簇因子图示

  • 良好的索引与聚簇因子的情形 clustering_factor01
  • 良好的索引、差的聚簇因子的情形 clustering_factor02
  • 差的索引、差的聚簇因子的情形 clustering_factor03

影响聚簇因子的情形

当插入到表的数据与索引列的顺序相同时,可以提高聚簇因子(趋近于表上的块数),因此,任何影响该顺序的情形都将导致索引列上的聚簇因子变差。如列的顺序,反向索引,空闲列表或空闲列表组。

提高聚簇因子

堆表的数据存储是无序存储,因此需要使无序变为有序,有如下方法提高聚簇因子:

  • 对于表上的多个索引以及组合索引的情形,索引的创建应考虑按经常频繁读取的大范围数据的读取顺序来创建索引
  • 定期重构表(针对堆表),也就是使得表与索引列上的数据顺序更接近,注: 是重构表,而不是重建索引   1. 重建索引并不能显剧提高CF的值,因为索引列通常是有序的,无序的是原始表上的数据
      2. 提取原始表上的数据到一个临时表,禁用依赖于该表的相关约束,truncate 原始表,再将临时表的数据按索引访问顺序填充到原始表
  • 使用聚簇表来代替堆表

聚簇因子趋势性

SQL> set linesize 120;
SQL> set pagesize 1000;
SQL> set long 10000;

--查看当前测试表的聚簇因子信息
--注意此时的LAST_ANALYZED为空,说明未收集过统计信息
SELECT 
  INDEX_NAME, B.TABLE_NAME, CLUSTERING_FACTOR,
  A.BLOCKS TB_BLOCKS, B.NUM_ROWS TB_ROWS,
  TO_CHAR(C.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
FROM DBA_SEGMENTS A, DBA_INDEXES B, DBA_TABLES C 
WHERE A.SEGMENT_NAME=B.TABLE_NAME 
  AND A.SEGMENT_NAME=C.TABLE_NAME 
  AND B.TABLE_NAME='TEST';

聚簇因子1

--分析收集表test最新的统计信息
begin
  dbms_stats.gather_table_stats(ownname => 'TZDM_DW', tabname => 'TEST', estimate_percent => 100, cascade => TRUE);  
end;

SELECT 
  INDEX_NAME, B.TABLE_NAME, CLUSTERING_FACTOR,
  A.BLOCKS TB_BLOCKS, B.NUM_ROWS TB_ROWS,
  TO_CHAR(C.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
FROM DBA_SEGMENTS A,DBA_INDEXES B ,DBA_TABLES C 
WHERE A.SEGMENT_NAME=B.TABLE_NAME 
  AND A.SEGMENT_NAME=C.TABLE_NAME 
  AND B.TABLE_NAME='TEST';

--查看收集后最新的信息,可以看到,结果集中CF值,明显小于数据块值,说明此时情况相似度很好。

聚簇因子2

--插入新的数据并进行提交
INSERT INTO TEST SELECT * FROM TEST;
COMMIT;

--重新收集表统计信息
begin
  dbms_stats.gather_table_stats(ownname => 'TZDM_DW', tabname => 'TEST', estimate_percent => 100, cascade => TRUE);  
end;

--随着insert的操作,CF值发生了改变,已经开始趋向行数。
SELECT 
  INDEX_NAME, B.TABLE_NAME, CLUSTERING_FACTOR,
  A.BLOCKS TB_BLOCKS, B.NUM_ROWS TB_ROWS,
  TO_CHAR(C.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
FROM DBA_SEGMENTS A,DBA_INDEXES B ,DBA_TABLES C 
WHERE A.SEGMENT_NAME=B.TABLE_NAME 
  AND A.SEGMENT_NAME=C.TABLE_NAME 
  AND B.TABLE_NAME='TEST';

注: 一个生产环境中,除了insert,还有update跟 delete,随着这些操作的增多,聚簇因子势必更加趋向行数

聚簇因子3

聚簇因子优化

由于影响CF(CLUSTERING_FACTOR)值的主要取决于数据表的数据在数据块中的存储分布情况,因此优化CF的重点还是在调整数据表本身

--定期按索引列顺序重建表
1. 建议通过 dbms_metadata.get_ddl 提取表结构完整的DDL语句,结合insert order by column以及rename table的方式进行表的重建。
2. 不建议采用CTAS方式(create table as select),该方式可能引起后续不必要的麻烦。

--通过调用dbms_metadata包的get_ddl函数,抽取原始表test的DDL结构语句
select dbms_metadata.get_ddl('TABLE',upper('TEST'),upper('TZDM_DW')) from dual;

DBMS_METADATA.GET_DDL('TABLE',UPPER('TEST'),UPPER('TZDM_DW'))
---------------------------------------------------------------
  CREATE TABLE "TZDM_DW"."TEST"
   (    "D_DATE" DATE NOT NULL ENABLE,
        "VC_INDEX_CODE" VARCHAR2(20) NOT NULL ENABLE,
        "VC_INDEX_TYPE" VARCHAR2(20),
        "VC_EXCHANGE" VARCHAR2(6),
        "F_INDEX_PRICE" NUMBER(20,4),
        "F_INDEX_YPRICE" NUMBER(20,4),
        "F_ID" NUMBER,
        "VC_SOURCE" VARCHAR2(20),
        "D_UPDATETIME" DATE,
        "F_VDTURNOVER" NUMBER(20,4),
        "F_VOAMOUNT" NUMBER(10,0)
   ) 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 "TZDM_DW"

-- 重命名原表
ALTER TABLE RENAME TO TEST2;

-- 按索引列排序的方式将原表数据写入新表
INSERT INTO /*+ append */ TEST SELECT * FROM TEST2 ORDER BY D_DATE, VC_INDEX_CODE;

-- 为新表添加索引
CREATE INDEX IDX_TEST ON TEST(D_DATE, VC_INDEX_CODE);

--重新收集表统计信息
begin
  dbms_stats.gather_table_stats(ownname => 'TZDM_DW', tabname => 'TEST', estimate_percent => 100, cascade => TRUE);  
end;

--查看重建表聚簇因子
SELECT 
  INDEX_NAME, B.TABLE_NAME, CLUSTERING_FACTOR,
  A.BLOCKS TB_BLOCKS, B.NUM_ROWS TB_ROWS,
  TO_CHAR(C.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
FROM DBA_SEGMENTS A,DBA_INDEXES B ,DBA_TABLES C 
WHERE A.SEGMENT_NAME=B.TABLE_NAME 
  AND A.SEGMENT_NAME=C.TABLE_NAME 
  AND B.TABLE_NAME='TEST';

聚簇因子4

聚簇因子对执行计划的影响

非索引列顺序存储数据

set linesize 120;
set pagesize 1000;
set long 5000;
alter session set nls_date_format='yyyy-mm-dd';

SQL> select count(*) from sei_industry_symbol;

  COUNT(*)
----------
    710084

# 创建索引
SQL> create index idx_sei_industry_symbol on sei_industry_symbol(vc_symbol);

# 收集统计信息
begin
  dbms_stats.gather_table_stats(ownname=>'TZDM_SODS', tabname => 'SEI_INDUSTRY_SYMBOL', estimate_percent => 100, cascade => TRUE);
end;
/
PL/SQL 过程已成功完成。

# 查看聚簇因子(数据无序)
SQL> select s.clustering_factor, s.num_rows, s.index_name, s.table_name, s.blevel, s.last_analyzed from user_indexes s where s.table_name = 'SEI_INDUSTRY_SYMBOL';

注: 此时聚簇因子趋向于数据表的行数, 说明CF值较差

image1

# 较差CF下的执行计划
<1> 逻辑读、物理读、递归调用
SQL> set autotrace on
SQL> select * from sei_industry_symbol t where t.vc_symbol = '000201';
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed
<2> 表的访问次数(Starts)、预估行、真实行, 逻辑读(Buffers)
SQL> alter session set statistics_level=all;
SQL> select * from sei_industry_symbol t where t.vc_symbol = '000201';
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

image2

按索引列顺序存储数据

# 重建表
SQL> create table sei_industry_symbol2 as select * from sei_industry_symbol;
SQL> truncate table sei_industry_symbol;
SQL> insert into /*+ append */ sei_industry_symbol select * from sei_industry_symbol2 order by vc_symbol;
SQL> select count(*) from sei_industry_symbol;

  COUNT(*)
----------
    710084

# 收集统计信息
begin
  dbms_stats.gather_table_stats(ownname=>'TZDM_SODS', tabname => 'SEI_INDUSTRY_SYMBOL', estimate_percent => 100, cascade => TRUE);
end;
/
PL/SQL 过程已成功完成。

# 查看聚簇因子(数据有序)
SQL> select s.clustering_factor, s.num_rows, s.index_name, s.table_name, s.blevel, s.last_analyzed from user_indexes s where s.table_name = 'SEI_INDUSTRY_SYMBOL';

注: 此时聚簇因子趋向于数据表的块数, 说明CF值良好

image3

# 良好CF下的执行计划
<1> 逻辑读、物理读、递归调用
SQL> set autotrace on
SQL> select * from sei_industry_symbol t where t.vc_symbol = '000201';
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed
<2> 表的访问次数(Starts)、预估行、真实行, 逻辑读(Buffers)
SQL> alter session set statistics_level=all;
SQL> select * from sei_industry_symbol t where t.vc_symbol = '000201';
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

image4

补充说明

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

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

参考资料

https://blog.csdn.net/leshami/article/details/8847959
https://www.cnblogs.com/yumiko/p/6036795.html

原创文章,转载请注明出处:http://www.opcoder.cn/article/5/