Oracle 聚簇因子(Clustering factor)

分类： Oracle 2014-02-26 16:03 10人阅读 评论(0)收藏 举报

    聚簇因子是 Oracle 统计信息中在CBO优化器模式下用于计算cost的参数之一，决定了当前的SQL语句是否走索引，还是全表扫描以及是否作为嵌套连接外部表等。如此这般，那到底什么是聚簇因子，那些情况下会影响到聚簇因子，以及如何提高聚簇因子？本文将对此展开描述。

 

1、堆表的存储方式
    Oralce 数据库系统中最普通，最为常用的即为堆表。
    堆表的数据存储方式为无序存储，也就是任意的DML操作都可能使得当前数据块存在可用的空闲空间。
    处于节省空间的考虑，块上的可用空闲空间会被新插入的行填充，而不是按顺序填充到最后被使用的块上。
    上述的操作方式导致了数据的无序性的产生。
    当创建索引时，会根据指定的列按顺序来填充到索引块，缺省的情况下为升序。
    新建或重建索引时，索引列上的顺序是有序的，而表上的顺序是无序的，也就是存在了差异，即表现为聚簇因子。
    
2、什么是聚簇因子(clustering factor/CF)
    聚簇因子是基于表上索引列上的一个值，每一个索引都有一个聚簇因子。
    用于描述索引块上与表块上存储数据在顺序上的相似程度，也就说表上的数据行的存储顺序与索引列上顺序是否一致。
    在全索引扫描中，CF的值基本上等同于物理I/O或块访问数，如果相同的块被连续读，则Oracle认为只需要1次物理I/O。
    好的CF值接近于表上的块数，而差的CF值则接近于表上的行数。
    聚簇因子在索引创建时就会通过表上存存在的行以及索引块计算获得。

 

3、Oracle 如何计算聚簇因子
    执行或预估一次全索引扫描。
    检查索引块上每一个rowid的值，查看是否前一个rowid的值与后一个指向了相同的数据块，如果指向了不相同的数据块则CF的值增加1。
    当索引块上的每一个rowid被检查完毕，即得到最终的CF值。

 

4、聚簇因子图示

a、良好的索引与聚簇因子的情形

   

b、良好的索引、差的聚簇因子的情形

       

c、差的索引、差的聚簇因子的情形

      

 

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

 

6、提高聚簇因子
    堆表的数据存储是无序存储，因此需要使无序变为有序。下面是提高聚簇因子的办法。
    a、对于表上的多个索引以及组合索引的情形，索引的创建应考虑按应该按照经常频繁读取的大范围数据的读取顺序来创建索引。
    b、定期重构表(针对堆表)，也就是使得表与索引上的数据顺序更接近。注意，是重构表，而不是重建索引。
       重建索引并不能显剧提高CF的值，因为索引列通常是有序的，无序的是原始表上的数据。
       提取原始表上的数据到一个临时表，禁用依赖于该表的相关约束，truncate原始表，再将临时表的数据按索引访问顺序填充到原始表。

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

 

7、实战聚簇因子随索引结构变化的情形

[sql] view plaincopy

1. a、演示环境  
2. scott@SYBO2SZ> select * from v$version where rownum<2;  
3.   
4. BANNER  
5. ----------------------------------------------------------------  
6. Oracle Database 10g Release 10.2.0.3.0 - 64bit Production  
7.   
8. b、列顺序对CF的影响  
9. --列顺序指索引列值顺序与表中的列值的顺序，一致，则CF良好，不一致，CF较差。  
10. scott@SYBO2SZ> create table t as select * from dba_objects order by object_name;  
11.   
12. scott@SYBO2SZ> create index i_obj_name on t(object_name);  -->基于object_name列创建索引  
13.   
14. scott@SYBO2SZ> create index i_obj_id on t(object_id);      -->基于object_id列创建索引  
15.   
16. cott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);  
17.   
18. PL/SQL procedure successfully completed.  
19.   
20. scott@SYBO2SZ> @idx_stat  
21. Enter value for input_table_name: T  
22. Enter value for owner: SCOTT  
23.   
24.                                          AVG LEAF BLKS AVG DATA BLKS  
25. BLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED        TB_BLKS    TB_ROWS  
26. ---- ------------- ---------- ---------- ------------- ------------- ---------- ----------------- ---------- ----------  
27.    1 I_OBJ_NAME           241      29476             1             1        675 20130418 17:00:42        695      48931  
28.    1 I_OBJ_ID             108      48931             1             1      24887 20130418 17:06:10        695      48931  
29.   
30. --从上面的查询可以看出，索引I_OBJ_NAME的聚簇因子小于表上的块数，一个良好的CF值，因为object_name列是有序插入的。  
31. --而索引I_OBJ_ID上的CF接近于表上行数的一半，说明该索引上的CF值不是很理想，因为object_id在插入到table时是无序的。  
32. --从上可知，一个表只能有一种有序的方式来组织数据。因此对于多出一个索引的表，且顺序按照非插入时的顺序时，则其他索引上的聚簇因子很难获得理想的值。  
33.   
34. c、组合索引对CF的影响  
35. --对于组合索引，列的顺序影响聚簇因子的大小   
36.   
37. --我们创建如下组合索引   
38.   
39. scott@SYBO2SZ> create index i_obj_name_id on t (object_name, object_id);  
40.   
41. scott@SYBO2SZ> create index i_obj_id_name on t (object_id, object_name);  
42.   
43. scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true)  
44.   
45. PL/SQL procedure successfully completed.  
46.   
47. scott@SYBO2SZ> @idx_stat  
48. Enter value for input_table_name: T  
49. Enter value for owner: SCOTT  
50.   
51.                                                      AVG LEAF BLKS AVG DATA BLKS  
52. BLEV IDX_NAME          LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED        TB_BLKS    TB_ROWS  
53. ---- ---------------- ---------- ---------- ------------- ------------- ---------- ----------------- ---------- ----------  
54.    1 I_OBJ_NAME              241      29476             1             1        675 20130418 17:17:17        695      48931  
55.    1 I_OBJ_ID                108      48931             1             1      24887 20130418 17:17:17        695      48931  
56.    1 I_OBJ_NAME_ID           274      48931             1             1        945 20130418 17:17:17        695      48931  
57.    1 I_OBJ_ID_NAME           274      48931             1             1      24887 20130418 17:17:18        695      48931  
58.   
59. --从上面的结果可知，   
60. --新创建的组合索引，I_OBJ_NAME_ID(object_name, object_id),object_name是前导列，因此CF值尽管比单列是大，依然表现良好。  
61. --而索引I_OBJ_ID_NAME(object_id, object_name)，object_id作为前导列，CF值与单列索引I_OBJ_ID相同。  
62. --上面的四个索引来看，无论是单列还是符合索引，当索引列(leaf)的顺序接近于表上行的顺序，CF表现良好。  
63.   
64. d、反向索引对CF的影响  
65. --反转索引主要是重新分布索引值，也就是将相连比较紧密地索引键值分散到不同或相距比较远的快上以避免竞争。  
66. --下面基于表t来新创建表t2   
67. scott@SYBO2SZ> create table t2 nologging as select * from t;  
68.   
69. scott@SYBO2SZ> create index i_obj_name_reverse on t2(object_name) reverse;  -->创建反向索引  
70.   
71. scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','T2',cascade=>true)  
72.   
73. PL/SQL procedure successfully completed.  
74.   
75. scott@SYBO2SZ> @idx_stat  
76. Enter value for input_table_name: T2  
77. Enter value for owner: SCOTT  
78.   
79.                                               AVG LEAF BLKS AVG DATA BLKS  
80. BLEV IDX_NAME            LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED        TB_BLKS    TB_ROWS  
81. ---- ------------------ ---------- ---------- ------------- ------------- ---------- ----------------- ---------- ----------  
82.    1 I_OBJ_NAME_REVERSE        241      29476             1             1      28104 20130418 17:22:49        695      48931  
83.   
84. --上面创建的反向索引的CF较之前的都要大，因索引键上的值是反向的，也就是说是无序的。  
85.   
86. --在段空间管理基于手动管理的方式下，如果使用freelist可以避免段操作上DML的竞争，但索引列上将具有较比较糟糕的聚簇因子(演示省略)  

[sql] view plaincopy

1. a、演示环境  
2. scott@SYBO2SZ> select * from v$version where rownum<2;  
3.   
4. BANNER  
5. ----------------------------------------------------------------  
6. Oracle Database 10g Release 10.2.0.3.0 - 64bit Production  
7.   
8. b、列顺序对CF的影响  
9. --列顺序指索引列值顺序与表中的列值的顺序，一致，则CF良好，不一致，CF较差。  
10. scott@SYBO2SZ> create table t as select * from dba_objects order by object_name;  
11.   
12. scott@SYBO2SZ> create index i_obj_name on t(object_name);  -->基于object_name列创建索引  
13.   
14. scott@SYBO2SZ> create index i_obj_id on t(object_id);      -->基于object_id列创建索引  
15.   
16. cott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);  
17.   
18. PL/SQL procedure successfully completed.  
19.   
20. scott@SYBO2SZ> @idx_stat  
21. Enter value for input_table_name: T  
22. Enter value for owner: SCOTT  
23.   
24.                                          AVG LEAF BLKS AVG DATA BLKS  
25. BLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED        TB_BLKS    TB_ROWS  
26. ---- ------------- ---------- ---------- ------------- ------------- ---------- ----------------- ---------- ----------  
27.    1 I_OBJ_NAME           241      29476             1             1        675 20130418 17:00:42        695      48931  
28.    1 I_OBJ_ID             108      48931             1             1      24887 20130418 17:06:10        695      48931  
29.   
30. --从上面的查询可以看出，索引I_OBJ_NAME的聚簇因子小于表上的块数，一个良好的CF值，因为object_name列是有序插入的。  
31. --而索引I_OBJ_ID上的CF接近于表上行数的一半，说明该索引上的CF值不是很理想，因为object_id在插入到table时是无序的。  
32. --从上可知，一个表只能有一种有序的方式来组织数据。因此对于多出一个索引的表，且顺序按照非插入时的顺序时，则其他索引上的聚簇因子很难获得理想的值。  
33.   
34. c、组合索引对CF的影响  
35. --对于组合索引，列的顺序影响聚簇因子的大小  
36.   
37. --我们创建如下组合索引  
38.   
39. scott@SYBO2SZ> create index i_obj_name_id on t (object_name, object_id);  
40.   
41. scott@SYBO2SZ> create index i_obj_id_name on t (object_id, object_name);  
42.   
43. scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true)  
44.   
45. PL/SQL procedure successfully completed.  
46.   
47. scott@SYBO2SZ> @idx_stat  
48. Enter value for input_table_name: T  
49. Enter value for owner: SCOTT  
50.   
51.                                                      AVG LEAF BLKS AVG DATA BLKS  
52. BLEV IDX_NAME          LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED        TB_BLKS    TB_ROWS  
53. ---- ---------------- ---------- ---------- ------------- ------------- ---------- ----------------- ---------- ----------  
54.    1 I_OBJ_NAME              241      29476             1             1        675 20130418 17:17:17        695      48931  
55.    1 I_OBJ_ID                108      48931             1             1      24887 20130418 17:17:17        695      48931  
56.    1 I_OBJ_NAME_ID           274      48931             1             1        945 20130418 17:17:17        695      48931  
57.    1 I_OBJ_ID_NAME           274      48931             1             1      24887 20130418 17:17:18        695      48931  
58.   
59. --从上面的结果可知，  
60. --新创建的组合索引，I_OBJ_NAME_ID(object_name, object_id),object_name是前导列，因此CF值尽管比单列是大，依然表现良好。  
61. --而索引I_OBJ_ID_NAME(object_id, object_name)，object_id作为前导列，CF值与单列索引I_OBJ_ID相同。  
62. --上面的四个索引来看，无论是单列还是符合索引，当索引列(leaf)的顺序接近于表上行的顺序，CF表现良好。  
63.   
64. d、反向索引对CF的影响  
65. --反转索引主要是重新分布索引值，也就是将相连比较紧密地索引键值分散到不同或相距比较远的快上以避免竞争。  
66. --下面基于表t来新创建表t2  
67. scott@SYBO2SZ> create table t2 nologging as select * from t;  
68.   
69. scott@SYBO2SZ> create index i_obj_name_reverse on t2(object_name) reverse;  -->创建反向索引  
70.   
71. scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','T2',cascade=>true)  
72.   
73. PL/SQL procedure successfully completed.  
74.   
75. scott@SYBO2SZ> @idx_stat  
76. Enter value for input_table_name: T2  
77. Enter value for owner: SCOTT  
78.   
79.                                               AVG LEAF BLKS AVG DATA BLKS  
80. BLEV IDX_NAME            LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED        TB_BLKS    TB_ROWS  
81. ---- ------------------ ---------- ---------- ------------- ------------- ---------- ----------------- ---------- ----------  
82.    1 I_OBJ_NAME_REVERSE        241      29476             1             1      28104 20130418 17:22:49        695      48931  
83.   
84. --上面创建的反向索引的CF较之前的都要大，因索引键上的值是反向的，也就是说是无序的。  
85.   
86. --在段空间管理基于手动管理的方式下，如果使用freelist可以避免段操作上DML的竞争，但索引列上将具有较比较糟糕的聚簇因子(演示省略)  

8、实战聚簇因子随DML变化的情形

[sql] view plaincopy

1. a、创建演示环境  
2. scott@SYBO2SZ> @cr_big_tb 1000000  -->创建一张百万记录的表  
3.   
4. Table created.  
5.   
6. scott@SYBO2SZ> @idx_stat           -->查看表与索引相关信息(CF为14489,TB_BLKS为14652)  
7. Enter value for input_table_name: BIG_TABLE  
8. Enter value for owner: SCOTT  
9.   
10.                                                      AVG LEAF BLKS AVG DATA BLKS  
11. BLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  
12. ---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  
13.    2 BIG_TABLE_PK        2088    1000000             1             1      14489 20130422 12:27:43       14652     999712  
14.   
15. b、模拟DML操作  
16. --创建一个临时表来存储将要从表big_table删除的记录   
17. scott@SYBO2SZ> create table big_table_tmp nologging as select * from big_table where id>=10000 and id<=200000;  
18.   
19. scott@SYBO2SZ> delete from big_table nologging where id>=10000 and id<=200000;  -->从表big_table删除一些记录  
20.   
21. scott@SYBO2SZ> commit;       
22.   
23. -->查看表与索引相关信息(从下面的查询结果可知，删除记录并不使得CF发生变化)  
24. scott@SYBO2SZ> @idx_stat       
25. Enter value for input_table_name: BIG_TABLE  
26. Enter value for owner: SCOTT  
27.   
28.                                           AVG LEAF BLKS AVG DATA BLKS  
29. BLEV IDX_NAME        LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  
30. ---- -------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  
31.    2 BIG_TABLE_PK         2088    1000000             1             1      14489 20130422 12:27:43       14652     999712  
32.   
33. scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);   -->收集统计信息  
34.   
35. scott@SYBO2SZ> @idx_stat          -->查看表与索引相关信息(在收集统计信息后，删除记录后CF为11732,TB_BLKS依然为14652)  
36. Enter value for input_table_name: BIG_TABLE            --(TB_BLKS块数未发生变化是因为空闲空间没有释放，需要shrink)  
37. Enter value for owner: SCOTT  
38.   
39.                                          AVG LEAF BLKS AVG DATA BLKS  
40. BLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  
41. ---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  
42.    2 BIG_TABLE_PK        1692     809999             1             1      11732 20130422 12:31:45       14652     808497  
43.   
44. -->接下来将删除的数据插入到big_table以模拟表上新增数据，分两次插入，以使得id变得无序     
45. scott@SYBO2SZ> insert into big_table nologging select * from big_table_tmp where id>=150000 and id<=200000  
46.   2  order by object_name;  
47.     
48. scott@SYBO2SZ> insert into big_table nologging select * from big_table_tmp where id>=10000 and id<150000  
49.   2  order by object_name;  
50.   
51. scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);  -->收集统计信息  
52.   
53. scott@SYBO2SZ> @idx_stat     -->查看表与索引相关信息(此时CF的值由原来的14489增大到114256，呈数量级变化)  
54. Enter value for input_table_name: BIG_TABLE  
55. Enter value for owner: SCOTT  
56.   
57.                                          AVG LEAF BLKS AVG DATA BLKS  
58. BLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  
59. ---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  
60.    2 BIG_TABLE_PK        2088    1000000             1             1     114256 20130422 12:33:31       14652     998513  
61.      
62. --下面尝试move table是否对CF有向影响   
63. scott@SYBO2SZ> alter table big_table move;      
64.   
65. scott@SYBO2SZ> @idx_stat   -->查看表与索引相关信息(move table之后，无任何变化)  
66. Enter value for input_table_name: BIG_TABLE  
67. Enter value for owner: SCOTT  
68.   
69.                                           AVG LEAF BLKS AVG DATA BLKS  
70. BLEV IDX_NAME        LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  
71. ---- -------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  
72.    2 BIG_TABLE_PK         2088    1000000             1             1     114256 20130422 12:33:31       14652     998513  
73.   
74. -->尝试收集统计信息后，在看CF的变化   
75. -->下面的错误表明，move之后，索引失效   
76. scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);  
77. BEGIN dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true); END;  
78.   
79. *  
80. ERROR at line 1:  
81. ORA-20000: index "SCOTT"."BIG_TABLE_PK"  or partition of such index is in unusable state  
82. ORA-06512: at "SYS.DBMS_STATS", line 13182  
83. ORA-06512: at "SYS.DBMS_STATS", line 13202  
84. ORA-06512: at line 1  
85.   
86. scott@SYBO2SZ> alter index big_table_pk rebuild nologging;   ---->重建索引  
87.   
88. scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);  -->再次收集统计信息  
89.   
90. PL/SQL procedure successfully completed.  
91.   
92. scott@SYBO2SZ> @idx_stat  -->重建索引后，CF的值反而增大了  
93. Enter value for input_table_name: BIG_TABLE  
94. Enter value for owner: SCOTT  
95.   
96.                                          AVG LEAF BLKS AVG DATA BLKS  
97. BLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  
98. ---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  
99.    2 BIG_TABLE_PK        2088    1000000             1             1     118384 20130422 12:36:31       14649     999427  
100.   
101. c、重建big_table  
102. -->下面通过重建big_table来缩小CF的值，新的表名为big_table_tmp  
103. scott@SYBO2SZ> drop table big_table_tmp purge;  --->删除之前的临时表  
104.   
105. scott@SYBO2SZ> create table big_table_tmp nologging as select * from big_table order by id;  
106.   
107. scott@SYBO2SZ> create unique index big_table_tmp_pk on big_table_tmp(id);  
108.   
109. scott@SYBO2SZ> alter table big_table_tmp add constraint big_table_tmp_pk primary key(id) using index big_table_tmp_pk;  
110.   
111. scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE_TMP',cascade=>true);  
112.   
113. scott@SYBO2SZ> @idx_stat    --->表big_table_tmp上的CF值(14486)小于原始的CF值(14489)  
114. Enter value for input_table_name: big_table_tmp  
115. Enter value for owner: scott  
116.   
117. --Author : Robinson   
118. --Blog   : http://blog.csdn.net/robinson_0612  
119.   
120.                                             AVG LEAF BLKS AVG DATA BLKS  
121. BLEV IDX_NAME          LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  
122. ---- ---------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  
123.    2 BIG_TABLE_TMP_PK       2088    1000000             1             1      14486 20130422 12:38:37       14649     995891  
124.   
125. d、比较不同的CF对查询性能的影响  
126. -->下面来基于表big_table与big_table_tmp来比较一下不同的CF对查询的影响  
127. scott@SYBO2SZ> set autot trace;  
128. scott@SYBO2SZ> select * from big_table where id between 10000 and 15000;  
129.   
130. 5001 rows selected.  
131.   
132. Execution Plan  
133. ----------------------------------------------------------  
134. Plan hash value: 3747652938  
135.   
136. --------------------------------------------------------------------------------------------  
137. | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |  
138. --------------------------------------------------------------------------------------------  
139. |   0 | SELECT STATEMENT            |              |  5001 |   478K|   606   (0)| 00:00:08 |  
140. |   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE    |  5001 |   478K|   606   (0)| 00:00:08 |  
141. |*  2 |   INDEX RANGE SCAN          | BIG_TABLE_PK |  5001 |       |    13   (0)| 00:00:01 |  
142. --------------------------------------------------------------------------------------------  
143.   
144. Predicate Information (identified by operation id):  
145. ---------------------------------------------------  
146.   
147.    2 - access("ID">=10000 AND "ID"<=15000)  
148.   
149. Statistics  
150. ----------------------------------------------------------  
151.           1  recursive calls  
152.           0  db block gets  
153.        2993  consistent gets  
154.         531  physical reads  
155.         116  redo size  
156.      287976  bytes sent via SQL*Net to client  
157.        4155  bytes received via SQL*Net from client  
158.         335  SQL*Net roundtrips to/from client  
159.           0  sorts (memory)  
160.           0  sorts (disk)  
161.        5001  rows processed  
162.   
163. --原始表上的查询的cost为606, consistent gets与physical reads分别为2993,531  
164.   
165. scott@SYBO2SZ> select * from big_table_tmp where id between 10000 and 15000;  
166.   
167. 5001 rows selected.  
168.   
169. Execution Plan  
170. ----------------------------------------------------------  
171. Plan hash value: 1127920103  
172.   
173. ------------------------------------------------------------------------------------------------  
174. | Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |  
175. ------------------------------------------------------------------------------------------------  
176. |   0 | SELECT STATEMENT            |                  |  4982 |   476K|    86   (0)| 00:00:02 |  
177. |   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE_TMP    |  4982 |   476K|    86   (0)| 00:00:02 |  
178. |*  2 |   INDEX RANGE SCAN          | BIG_TABLE_TMP_PK |  4982 |       |    13   (0)| 00:00:01 |  
179. ------------------------------------------------------------------------------------------------  
180.   
181. Predicate Information (identified by operation id):  
182. ---------------------------------------------------  
183.   
184.    2 - access("ID">=10000 AND "ID"<=15000)  
185.   
186. Statistics  
187. ----------------------------------------------------------  
188.           1  recursive calls  
189.           0  db block gets  
190.         750  consistent gets  
191.          76  physical reads  
192.           0  redo size  
193.      287976  bytes sent via SQL*Net to client  
194.        4155  bytes received via SQL*Net from client  
195.         335  SQL*Net roundtrips to/from client  
196.           0  sorts (memory)  
197.           0  sorts (disk)  
198.        5001  rows processed  
199.   
200. --新创建的表的cost 为86, consistent gets与physical reads分别为750,76. 呈数量级低低于原表的开销  
201.                
202. -->可以将原始表big_table上的数据删除(truncate)，删除前禁用依赖于该表的所有约束，然后将big_table_tmp的数据使用order by插入到big_table  
203. -->注上面的create table as ..方式并不适合用于生产环境的真实操作，因为表上的一些属性会被忽略掉.  

[sql] view plaincopy

1. a、创建演示环境  
2. scott@SYBO2SZ> @cr_big_tb 1000000  -->创建一张百万记录的表  
3.   
4. Table created.  
5.   
6. scott@SYBO2SZ> @idx_stat           -->查看表与索引相关信息(CF为14489,TB_BLKS为14652)  
7. Enter value for input_table_name: BIG_TABLE  
8. Enter value for owner: SCOTT  
9.   
10.                                                      AVG LEAF BLKS AVG DATA BLKS  
11. BLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  
12. ---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  
13.    2 BIG_TABLE_PK        2088    1000000             1             1      14489 20130422 12:27:43       14652     999712  
14.   
15. b、模拟DML操作  
16. --创建一个临时表来存储将要从表big_table删除的记录  
17. scott@SYBO2SZ> create table big_table_tmp nologging as select * from big_table where id>=10000 and id<=200000;  
18.   
19. scott@SYBO2SZ> delete from big_table nologging where id>=10000 and id<=200000;  -->从表big_table删除一些记录  
20.   
21. scott@SYBO2SZ> commit;       
22.   
23. -->查看表与索引相关信息(从下面的查询结果可知，删除记录并不使得CF发生变化)  
24. scott@SYBO2SZ> @idx_stat       
25. Enter value for input_table_name: BIG_TABLE  
26. Enter value for owner: SCOTT  
27.   
28.                                           AVG LEAF BLKS AVG DATA BLKS  
29. BLEV IDX_NAME        LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  
30. ---- -------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  
31.    2 BIG_TABLE_PK         2088    1000000             1             1      14489 20130422 12:27:43       14652     999712  
32.   
33. scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);   -->收集统计信息  
34.   
35. scott@SYBO2SZ> @idx_stat          -->查看表与索引相关信息(在收集统计信息后，删除记录后CF为11732,TB_BLKS依然为14652)  
36. Enter value for input_table_name: BIG_TABLE            --(TB_BLKS块数未发生变化是因为空闲空间没有释放，需要shrink)  
37. Enter value for owner: SCOTT  
38.   
39.                                          AVG LEAF BLKS AVG DATA BLKS  
40. BLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  
41. ---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  
42.    2 BIG_TABLE_PK        1692     809999             1             1      11732 20130422 12:31:45       14652     808497  
43.   
44. -->接下来将删除的数据插入到big_table以模拟表上新增数据，分两次插入，以使得id变得无序     
45. scott@SYBO2SZ> insert into big_table nologging select * from big_table_tmp where id>=150000 and id<=200000  
46.   2  order by object_name;  
47.     
48. scott@SYBO2SZ> insert into big_table nologging select * from big_table_tmp where id>=10000 and id<150000  
49.   2  order by object_name;  
50.   
51. scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);  -->收集统计信息  
52.   
53. scott@SYBO2SZ> @idx_stat     -->查看表与索引相关信息(此时CF的值由原来的14489增大到114256，呈数量级变化)  
54. Enter value for input_table_name: BIG_TABLE  
55. Enter value for owner: SCOTT  
56.   
57.                                          AVG LEAF BLKS AVG DATA BLKS  
58. BLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  
59. ---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  
60.    2 BIG_TABLE_PK        2088    1000000             1             1     114256 20130422 12:33:31       14652     998513  
61.      
62. --下面尝试move table是否对CF有向影响  
63. scott@SYBO2SZ> alter table big_table move;      
64.   
65. scott@SYBO2SZ> @idx_stat   -->查看表与索引相关信息(move table之后，无任何变化)  
66. Enter value for input_table_name: BIG_TABLE  
67. Enter value for owner: SCOTT  
68.   
69.                                           AVG LEAF BLKS AVG DATA BLKS  
70. BLEV IDX_NAME        LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  
71. ---- -------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  
72.    2 BIG_TABLE_PK         2088    1000000             1             1     114256 20130422 12:33:31       14652     998513  
73.   
74. -->尝试收集统计信息后，在看CF的变化  
75. -->下面的错误表明，move之后，索引失效  
76. scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);  
77. BEGIN dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true); END;  
78.   
79. *  
80. ERROR at line 1:  
81. ORA-20000: index "SCOTT"."BIG_TABLE_PK"  or partition of such index is in unusable state  
82. ORA-06512: at "SYS.DBMS_STATS", line 13182  
83. ORA-06512: at "SYS.DBMS_STATS", line 13202  
84. ORA-06512: at line 1  
85.   
86. scott@SYBO2SZ> alter index big_table_pk rebuild nologging;   ---->重建索引  
87.   
88. scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);  -->再次收集统计信息  
89.   
90. PL/SQL procedure successfully completed.  
91.   
92. scott@SYBO2SZ> @idx_stat  -->重建索引后，CF的值反而增大了  
93. Enter value for input_table_name: BIG_TABLE  
94. Enter value for owner: SCOTT  
95.   
96.                                          AVG LEAF BLKS AVG DATA BLKS  
97. BLEV IDX_NAME       LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  
98. ---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  
99.    2 BIG_TABLE_PK        2088    1000000             1             1     118384 20130422 12:36:31       14649     999427  
100.   
101. c、重建big_table  
102. -->下面通过重建big_table来缩小CF的值，新的表名为big_table_tmp  
103. scott@SYBO2SZ> drop table big_table_tmp purge;  --->删除之前的临时表  
104.   
105. scott@SYBO2SZ> create table big_table_tmp nologging as select * from big_table order by id;  
106.   
107. scott@SYBO2SZ> create unique index big_table_tmp_pk on big_table_tmp(id);  
108.   
109. scott@SYBO2SZ> alter table big_table_tmp add constraint big_table_tmp_pk primary key(id) using index big_table_tmp_pk;  
110.   
111. scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE_TMP',cascade=>true);  
112.   
113. scott@SYBO2SZ> @idx_stat    --->表big_table_tmp上的CF值(14486)小于原始的CF值(14489)  
114. Enter value for input_table_name: big_table_tmp  
115. Enter value for owner: scott  
116.   
117. --Author : Robinson  
118. --Blog   : http://blog.csdn.net/robinson_0612  
119.   
120.                                             AVG LEAF BLKS AVG DATA BLKS  
121. BLEV IDX_NAME          LEAF_BLKS   DST_KEYS       PER KEY       PER KEY CLUST_FACT LAST_ANALYZED         TB_BLKS    TB_ROWS  
122. ---- ---------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------  
123.    2 BIG_TABLE_TMP_PK       2088    1000000             1             1      14486 20130422 12:38:37       14649     995891  
124.   
125. d、比较不同的CF对查询性能的影响  
126. -->下面来基于表big_table与big_table_tmp来比较一下不同的CF对查询的影响  
127. scott@SYBO2SZ> set autot trace;  
128. scott@SYBO2SZ> select * from big_table where id between 10000 and 15000;  
129.   
130. 5001 rows selected.  
131.   
132. Execution Plan  
133. ----------------------------------------------------------  
134. Plan hash value: 3747652938  
135.   
136. --------------------------------------------------------------------------------------------  
137. | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |  
138. --------------------------------------------------------------------------------------------  
139. |   0 | SELECT STATEMENT            |              |  5001 |   478K|   606   (0)| 00:00:08 |  
140. |   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE    |  5001 |   478K|   606   (0)| 00:00:08 |  
141. |*  2 |   INDEX RANGE SCAN          | BIG_TABLE_PK |  5001 |       |    13   (0)| 00:00:01 |  
142. --------------------------------------------------------------------------------------------  
143.   
144. Predicate Information (identified by operation id):  
145. ---------------------------------------------------  
146.   
147.    2 - access("ID">=10000 AND "ID"<=15000)  
148.   
149. Statistics  
150. ----------------------------------------------------------  
151.           1  recursive calls  
152.           0  db block gets  
153.        2993  consistent gets  
154.         531  physical reads  
155.         116  redo size  
156.      287976  bytes sent via SQL*Net to client  
157.        4155  bytes received via SQL*Net from client  
158.         335  SQL*Net roundtrips to/from client  
159.           0  sorts (memory)  
160.           0  sorts (disk)  
161.        5001  rows processed  
162.   
163. --原始表上的查询的cost为606, consistent gets与physical reads分别为2993,531  
164.   
165. scott@SYBO2SZ> select * from big_table_tmp where id between 10000 and 15000;  
166.   
167. 5001 rows selected.  
168.   
169. Execution Plan  
170. ----------------------------------------------------------  
171. Plan hash value: 1127920103  
172.   
173. ------------------------------------------------------------------------------------------------  
174. | Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |  
175. ------------------------------------------------------------------------------------------------  
176. |   0 | SELECT STATEMENT            |                  |  4982 |   476K|    86   (0)| 00:00:02 |  
177. |   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE_TMP    |  4982 |   476K|    86   (0)| 00:00:02 |  
178. |*  2 |   INDEX RANGE SCAN          | BIG_TABLE_TMP_PK |  4982 |       |    13   (0)| 00:00:01 |  
179. ------------------------------------------------------------------------------------------------  
180.   
181. Predicate Information (identified by operation id):  
182. ---------------------------------------------------  
183.   
184.    2 - access("ID">=10000 AND "ID"<=15000)  
185.   
186. Statistics  
187. ----------------------------------------------------------  
188.           1  recursive calls  
189.           0  db block gets  
190.         750  consistent gets  
191.          76  physical reads  
192.           0  redo size  
193.      287976  bytes sent via SQL*Net to client  
194.        4155  bytes received via SQL*Net from client  
195.         335  SQL*Net roundtrips to/from client  
196.           0  sorts (memory)  
197.           0  sorts (disk)  
198.        5001  rows processed  
199.   
200. --新创建的表的cost 为86, consistent gets与physical reads分别为750,76. 呈数量级低低于原表的开销  
201.                
202. -->可以将原始表big_table上的数据删除(truncate)，删除前禁用依赖于该表的所有约束，然后将big_table_tmp的数据使用order by插入到big_table  
203. -->注上面的create table as ..方式并不适合用于生产环境的真实操作，因为表上的一些属性会被忽略掉.  

9、小结
  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

 

idx_stat

set linesize 190
col idx_name format a25 wrap
col last_analyzed format a18
col avg_leaf_blocks_per_key heading 'AVG LEAF BLKS|PER KEY'
col avg_data_blocks_per_key heading 'AVG DATA BLKS|PER KEY'
col blev format 99

SELECT dis.blevel blev
, dis.index_name idx_name
, dis.leaf_blocks leaf_blks
, dis.distinct_keys dst_keys
, dis.avg_leaf_blocks_per_key
, dis.avg_data_blocks_per_key
, dis.clustering_factor clust_fact
, dis.last_analyzed
, dts.blocks tb_blks
, dts.num_rows tb_rows
FROM dba_ind_statistics dis
JOIN
dba_tab_statistics dts
ON dis.table_name = dts.table_name
WHERE dts.table_name = UPPER ('&input_table_name')
AND dts.owner = UPPER ('&owner');