Oracle 聚簇因子(Clustering factor)

Oralce 数据库系统中最普通，最为常用的即为堆表。堆表的数据存储方式为无序存储，也就是任意的DML操作都可能使得当前数据块存在可用的空闲空间。处于节省空间的考虑，块上的可用空闲空间会被新插入的行随机的填充，而不是按顺序填充到最后被使用的块上。

上述的操作方式导致了数据的无序性的产生。

当创建索引时，会根据指定的列按顺序来填充到索引块，缺省的情况下为升序。

新建或重建索引时，索引列上的顺序是有序的，而表上的顺序是无序的，也就是存在了差异，即表现为聚簇因子。

聚簇因子是基于表上索引列上的一个值，每一个索引都有一个聚簇因子。

用于描述索引块上与表块上存储数据在顺序上的相似程度，也就说表上的数据行的存储顺序与索引列上顺序是否一致。

在全索引扫描中，CF的值基本上等同于物理I/O或块访问数，如果相同的块被连续读，则Oracle认为只需要1次物理I/O。

好的CF值接近于表上的块数，而差的CF值则接近于表上的行数。

聚簇因子在索引创建时就会通过表上存存在的行以及索引块计算获得。

执行或预估一次全索引扫描。

检查索引块上每一个rowid的值，查看是否前一个rowid的值与后一个指向了相同的数据块，如果指向了不相同的数据块则CF的值增加1。

当索引块上的每一个rowid被检查完毕，即得到最终的CF值。

CF应该尽可能的小

当插入到表的数据与索引的顺序相同时，可以提高聚簇因子(接近表上的块数)。

因此，任意影响该顺序的情形都将导致索引列上的聚簇因子变差。

如列的顺序，反向索引，空闲列表或空闲列表组。

堆表的数据存储是无序存储，因此需要使无序变为有序。下面是提高聚簇因子的办法。

    a、对于表上的多个索引以及组合索引的情形，索引的创建应考虑按应该按照经常频繁读取的大范围数据的读取顺序来创建索引。

    b、定期重构表(针对堆表)，也就是使得表与索引上的数据顺序更接近。注意，是重构表，而不是重建索引。

       重建索引并不能显著提高CF的值，因为索引列通常是有序的，无序的是原始表上的数据。

       提取原始表上的数据到一个临时表，禁用依赖于该表的相关约束，truncate原始表，再将临时表的数据按索引访问顺序填充到原始表。

    c、使用聚簇表来代替堆表。

a、演示环境  

SYS @ prod >select * from v$version where rownum <2;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

b、列顺序对CF的影响  

--列顺序指索引列值顺序与表中的列值的顺序，一致，则CF良好，不一致，CF较差。  

创建基础表

 基于object_name列创建索引  

基于object_id列创建索引  

收集基础表的状态

创建脚本idx_stat,收集索引的状态;

执行脚本，收集索引的状态

c、组合索引对CF的影响  

--对于组合索引，列的顺序影响聚簇因子的大小  

我们创建如下组合索引  

收集基础表的状态

收集索引的状态

d、反向索引对CF的影响  

--反转索引主要是重新分布索引值，也就是将相连比较紧密地索引键值分散到不同或相距比较远的快上以避免竞争。  

下面基于表t来新创建表t2  

创建反向索引  

收集表的状态

收集索引的状态

a、创建演示环境  

创建一张百万记录的表

收集基础表的状态

收集索引的状态

b、模拟DML操作  

--创建一个临时表来存储将要从表big_table删除的记录  

从表big_table删除一些记录  

  收集基础表的信息

查看表与索引相关信息(从下面的查询结果可知，删除记录并不使得CF发生变化)  

scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);   -->收集统计信息  

scott@SYBO2SZ> @idx_stat          -->查看表与索引相关信息(在收集统计信息后，删除记录后CF为11732,TB_BLKS依然为14652)  

Enter value for input_table_name: BIG_TABLE            --(TB_BLKS块数未发生变化是因为空闲空间没有释放，需要shrink)  

Enter value for owner: SCOTT  

                                         AVG LEAF BLKS AVG DATA BLKS  

BLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  

---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  

   2 BIG_TABLE_PK        1692     809999             1             1      11732 20130422 12:31:45       14652     808497  

-->接下来将删除的数据插入到big_table以模拟表上新增数据，分两次插入，以使得id变得无序     

scott@SYBO2SZ> insert into big_table nologging select * from big_table_tmp where id>=150000 and id<=200000  

  2  order by object_name;  

scott@SYBO2SZ> insert into big_table nologging select * from big_table_tmp where id>=10000 and id<150000  

  2  order by object_name;  

scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);  -->收集统计信息  

scott@SYBO2SZ> @idx_stat     -->查看表与索引相关信息(此时CF的值由原来的14489增大到114256，呈数量级变化)  

Enter value for input_table_name: BIG_TABLE  

Enter value for owner: SCOTT  

                                         AVG LEAF BLKS AVG DATA BLKS  

BLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  

---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  

   2 BIG_TABLE_PK        2088    1000000             1             1     114256 20130422 12:33:31       14652     998513  

--下面尝试move table是否对CF有向影响  

scott@SYBO2SZ> alter table big_table move;      

scott@SYBO2SZ> @idx_stat   -->查看表与索引相关信息(move table之后，无任何变化)  

Enter value for input_table_name: BIG_TABLE  

Enter value for owner: SCOTT  

                                          AVG LEAF BLKS AVG DATA BLKS  

BLEV IDX_NAME        LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  

---- -------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  

   2 BIG_TABLE_PK         2088    1000000             1             1     114256 20130422 12:33:31       14652     998513  

-->尝试收集统计信息后，在看CF的变化  

-->下面的错误表明，move之后，索引失效  

scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);  

BEGIN dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true); END;  

*  

ERROR at line 1:  

ORA-20000: index "SCOTT"."BIG_TABLE_PK"  or partition of such index is in unusable state  

ORA-06512: at "SYS.DBMS_STATS", line 13182  

ORA-06512: at "SYS.DBMS_STATS", line 13202  

ORA-06512: at line 1  

scott@SYBO2SZ> alter index big_table_pk rebuild nologging;   ---->重建索引  

scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);  -->再次收集统计信息  

PL/SQL procedure successfully completed.  

scott@SYBO2SZ> @idx_stat  -->重建索引后，CF的值反而增大了  

Enter value for input_table_name: BIG_TABLE  

Enter value for owner: SCOTT  

                                         AVG LEAF BLKS AVG DATA BLKS  

BLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  

---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  

   2 BIG_TABLE_PK        2088    1000000             1             1     118384 20130422 12:36:31       14649     999427  

c、重建big_table  

-->下面通过重建big_table来缩小CF的值，新的表名为big_table_tmp  

scott@SYBO2SZ> drop table big_table_tmp purge;  --->删除之前的临时表  

scott@SYBO2SZ> create table big_table_tmp nologging as select * from big_table order by id;  

scott@SYBO2SZ> create unique index big_table_tmp_pk on big_table_tmp(id);  

scott@SYBO2SZ> alter table big_table_tmp add constraint big_table_tmp_pk primary key(id) using index big_table_tmp_pk;  

scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE_TMP',cascade=>true);  

scott@SYBO2SZ> @idx_stat    --->表big_table_tmp上的CF值(14486)小于原始的CF值(14489)  

Enter value for input_table_name: big_table_tmp  

Enter value for owner: scott  

--Author : Robinson  

--Blog   : http://blog.csdn.net/robinson_0612  

                                            AVG LEAF BLKS AVG DATA BLKS  

BLEV IDX_NAME          LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  

---- ---------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  

   2 BIG_TABLE_TMP_PK       2088    1000000             1             1      14486 20130422 12:38:37       14649     995891  

d、比较不同的CF对查询性能的影响  

-->下面来基于表big_table与big_table_tmp来比较一下不同的CF对查询的影响  

scott@SYBO2SZ> set autot trace;  

scott@SYBO2SZ> select * from big_table where id between 10000 and 15000;  

5001 rows selected.  

Execution Plan  

----------------------------------------------------------  

Plan hash value: 3747652938  

--------------------------------------------------------------------------------------------  

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |  

--------------------------------------------------------------------------------------------  

|   0 | SELECT STATEMENT            |              |  5001 |   478K|   606   (0)| 00:00:08 |  

|   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE    |  5001 |   478K|   606   (0)| 00:00:08 |  

|*  2 |   INDEX RANGE SCAN          | BIG_TABLE_PK |  5001 |       |    13   (0)| 00:00:01 |  

--------------------------------------------------------------------------------------------  

Predicate Information (identified by operation id):  

---------------------------------------------------  

   2 - access("ID">=10000 AND "ID"<=15000)  

Statistics  

----------------------------------------------------------  

          1  recursive calls  

          0  db block gets  

       2993  consistent gets  

        531  physical reads  

        116  redo size  

     287976  bytes sent via SQL*Net to client  

       4155  bytes received via SQL*Net from client  

        335  SQL*Net roundtrips to/from client  

          0  sorts (memory)  

          0  sorts (disk)  

       5001  rows processed  

--原始表上的查询的cost为606, consistent gets与physical reads分别为2993,531  

scott@SYBO2SZ> select * from big_table_tmp where id between 10000 and 15000;  

5001 rows selected.  

Execution Plan  

----------------------------------------------------------  

Plan hash value: 1127920103  

------------------------------------------------------------------------------------------------  

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |  

------------------------------------------------------------------------------------------------  

|   0 | SELECT STATEMENT            |                  |  4982 |   476K|    86   (0)| 00:00:02 |  

|   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE_TMP    |  4982 |   476K|    86   (0)| 00:00:02 |  

|*  2 |   INDEX RANGE SCAN          | BIG_TABLE_TMP_PK |  4982 |       |    13   (0)| 00:00:01 |  

------------------------------------------------------------------------------------------------  

Predicate Information (identified by operation id):  

---------------------------------------------------  

   2 - access("ID">=10000 AND "ID"<=15000)  

Statistics  

----------------------------------------------------------  

          1  recursive calls  

          0  db block gets  

        750  consistent gets  

         76  physical reads  

          0  redo size  

     287976  bytes sent via SQL*Net to client  

       4155  bytes received via SQL*Net from client  

        335  SQL*Net roundtrips to/from client  

          0  sorts (memory)  

          0  sorts (disk)  

       5001  rows processed  

--新创建的表的cost 为86, consistent gets与physical reads分别为750,76. 呈数量级低低于原表的开销  

-->可以将原始表big_table上的数据删除(truncate)，删除前禁用依赖于该表的所有约束，然后将big_table_tmp的数据使用order by插入到big_table  

-->注上面的create table as ..方式并不适合用于生产环境的真实操作，因为表上的一些属性会被忽略掉.  

  a、任意情形下(堆表)，表上数据的存储只能按照一种特定的顺序进行存储。

  b、由上面的特性决定了表上的只有一个特定的索引列(单索引或组合索引)具有最佳的CF值。

  c、索引的创建应考虑按应该按照经常频繁读取的大范围数据的读取顺序来创建索引，以保证得到最佳的CF值。

  d、索引在被创建之时，基于该索引列上的CF值即被产生，但表上的DML操作后需要收集统计信息才可以更新CF的值。

  e、基于表上频繁的DML操作，尤其是delete后再新增记录，可用空闲空间被填充，将使得CF的值呈增大趋势。

  f、alter table move tabname并不会影响CF的值，该功能只是移动高水位线，且不释放空间。

  g、重建索引对CF的值收效甚微，因为原始表数据存储顺序未发生根本变化。

  h、CF的值是影响查询分析器对执行计划的评估与生成的因素之一(即是否走索引还是全表扫描，嵌套连接时哪个表为驱动表等)。

  i、通过重建表或使用聚簇表来改进CF的值，建议将原始表数据填充到临时表，禁用依赖于该表的所有约束后truncate该表，再从临时表导回数据(按顺序)，启用约束。

  j、不推荐使用create table as select(CTAS)，因为表上的一些特性会被忽略，可以用SQL*Plus copy方式来实现。具体参考: 当心 CREATE TABLE AS