Oracle 聚簇因子(Clustering factor)

  聚簇因子是 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 plain   copy       print  ?    

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 plain   copy       print  ?    

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 

---

---

SQL>@idx_stat  
这个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');


 

Oracle Index Clustering Factor(集群因子)

一、本文说明：  


    今天在做测试的时候发现字段上有索引，但是执行计划就是不走索引，经过在网上查找才发现原来是索引的集群因子过高导致的。本文属于转载+模拟。  


二、官网说明  


    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，那么这时候Clustering Factor的值就越低；  


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


    Clustering Factor的计算方式如下：  


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


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


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


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


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


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


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


四、测试  


  4.1、产生问题：  


复制代码  
  ----查看一下数据库的版本----  
  1 SQL> select * from v$version where rownum=1;  
  2   
  3 BANNER  
  4 --------------------------------------------------------------------------------  
  5 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  
  6   
   ----创建一张测试表jack----  
  7 SQL> create table jack as select * from dba_objects where 1=2;  
  8   
  9 Table created.  
 10   
  ----将数据无序的插入jack表中----  
 11 SQL> begin  
 12   2      for i in 1..10 loop  
 13   3        insert /*+ append */ into jack select * from dba_objects order by i;  
 14   4      commit;  
 15   5    end loop;  
 16   6  end;  
 17   7  /  
 18   
 19 PL/SQL procedure successfully completed.  
 20   
 21 SQL> select count(*) from jack;  
 22   
 23   COUNT(*)  
 24 ----------  
 25     725460  
 26   
  ----查看一下表的大小-----  
 27 SQL> set wrap off  
 28 SQL> col owner for a10;  
 29 SQL> col segment_name for a15;  
 30 SQL> select segment_name,blocks,extents,bytes/1024/1024||'M' "size" from user_segments where segment_name='JACK';  
 31   
 32 SEGMENT_NAME     BLOCKS    EXTENTS   size  
 33 ------------- ---------- ---------- --------  
 34 JACK             11264       82      88M  
 35   
  ----在object_id上创建索引----  
 36 SQL> create index jack_ind on jack(object_id);  
 37   
 38 Index created.  
 39   
  ----查看一下索引的大小----  
 40 SQL> select segment_name,segment_type,blocks,extents,bytes/1024/1024||'M' "size" from user_segments where segment_name='JACK_IND';  
 41   
 42 SEGMENT_NAME    SEGMENT_TYPE      BLOCKS     EXTENTS     size  
 43 ------------ ------------------ ---------- ---------- ---------  
 44 JACK_IND           INDEX           1664         28        13M  
  ----在没有收集相关的统计信息之前，查看一下index clustering factor----  
 45 SQL> select index_name,clustering_factor,num_rows from user_indexes where index_name='JACK_IND';  
 46   
 47 INDEX_NAME      CLUSTERING_FACTOR   NUM_ROWS  
 48 --------------- ----------------- ----------  
 49 JACK_IND              725460         725460  
 50   
  ----简单的收集一下统计信息----  
 51 SQL> exec dbms_stats.gather_table_stats(user,'jack',cascade=>true);  
 52   
 53 PL/SQL procedure successfully completed.  
 54   
  ----再次查看index clustering factor----  
 55 SQL> select index_name,clustering_factor,num_rows from user_indexes where index_name='JACK_IND';  
 56   
 57 INDEX_NAME     CLUSTERING_FACTOR   NUM_ROWS  
 58 -------------- ----------------- ----------  
 59 JACK_IND              725460       725460       ----显然统计信息收集前和后，clustering factor值不变，说在创建索引的时候，会收集表中的数据真正的行数。并且这里的clustering factor等num_rows，也说明表的clustering factor是无序的。  
 60   
  ----查看一个确定值，然后查看执行计划----  
 61 SQL> explain plan for select * from jack where object_id=1501;  
 62   
 63 Explained.  
 64   
 65 SQL> select * from table(dbms_xplan.display);  
 66   
 67 PLAN_TABLE_OUTPUT  
 68 --------------------------------------------------------------------------------  
 69 Plan hash value: 2860868395  
 70   
 71 --------------------------------------------------------------------------------  
 72 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Ti  
 73 --------------------------------------------------------------------------------  
 74 |   0 | SELECT STATEMENT        |           |    10 |   970 |    13     (0)| 00  
 75 |   1 |  TABLE ACCESS BY INDEX ROWID| JACK     |    10 |   970 |    13     (0)| 00  
 76 |*  2 |   INDEX RANGE SCAN        | JACK_IND |    10 |       |     3     (0)| 00  
 77 --------------------------------------------------------------------------------  
 78   
 79 Predicate Information (identified by operation id):  
 80   
 81 PLAN_TABLE_OUTPUT  
 82 --------------------------------------------------------------------------------  
 83   
 84   
 85    2 - access("OBJECT_ID"=1501)  
 86   
 87 14 rows selected.                 ----在这里走了索引，cost为13.  
 88   
 89 SQL> alter system flush buffer_cache;  
 90   
 91 System altered.  
 92   
 93 SQL> set autotrace traceonly;  
  ----查询一个范围的执行计划----  
 94 SQL> select * from jack where object_id>1000 and object_id<2000;  
 95   
 96 9880 rows selected.  
 97   
 98   
 99 Execution Plan  
100 ----------------------------------------------------------  
101 Plan hash value: 949574992  
102   
103 --------------------------------------------------------------------------  
104 | Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
105 --------------------------------------------------------------------------  
106 |   0 | SELECT STATEMENT  |     |  9657 |   914K|  1824   (1)| 00:00:22 |  
107 |*  1 |  TABLE ACCESS FULL| JACK |  9657 |   914K|  1824   (1)| 00:00:22 |  
108 --------------------------------------------------------------------------  
109   
110 Predicate Information (identified by operation id):  
111 ---------------------------------------------------  
112   
113    1 - filter("OBJECT_ID"<2000 AND "OBJECT_ID">1000)  
114   
115   
116 Statistics  
117 ----------------------------------------------------------  
118       0   recursive calls  
119       0   db block gets  
120     10993 consistent gets  
121     10340 physical reads  
122       0   redo size  
123    471945 bytes sent via SQL*Net to client  
124     7657  bytes received via SQL*Net from client  
125     660   SQL*Net roundtrips to/from client  
126       0   sorts (memory)  
127       0   sorts (disk)  
128     9880  rows processed       ----注意，object_id上是有索引的，但是这里并没有使用索引，而是使用了全表扫描。  
129   
130 SQL> alter system flush buffer_cache;  
131   
132 System altered.  
133   
 ----强制走索引，查看执行计划----  
134 SQL> select /*+ index(jack jack_ind) */ * from jack where object_id>1000 and object_id<2000;  
135   
136 9880 rows selected.  
137   
138   
139 Execution Plan  
140 ----------------------------------------------------------  
141 Plan hash value: 2860868395  
142   
143 ----------------------------------------------------------------------------------------  
144 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
145 ----------------------------------------------------------------------------------------  
146 |   0 | SELECT STATEMENT        |           |  9657 |   914K|  9683     (1)| 00:01:57 |  
147 |   1 |  TABLE ACCESS BY INDEX ROWID| JACK     |  9657 |   914K|  9683     (1)| 00:01:57 |  
148 |*  2 |   INDEX RANGE SCAN        | JACK_IND |  9657 |       |    24     (0)| 00:00:01 |  
149 ----------------------------------------------------------------------------------------  
150   
151 Predicate Information (identified by operation id):  
152 ---------------------------------------------------  
153   
154    2 - access("OBJECT_ID">1000 AND "OBJECT_ID"<2000)  
155   
156   
157 Statistics  
158 ----------------------------------------------------------  
159       0    recursive calls  
160       0    db block gets  
161     10561  consistent gets  
162     164    physical reads  
163       0    redo size  
164    988947  bytes sent via SQL*Net to client  
165     7657   bytes received via SQL*Net from client  
166     660    SQL*Net roundtrips to/from client  
167       0    sorts (memory)  
168       0    sorts (disk)  
169     9880   rows processed      
----强制走索引之后，使用了index range scan,但是cost变成了9683，而全表扫描时是1824.  
----还有比较一下两次查询中物理读的情况：全表扫描的物理读明显比索引的要高很多，但是Oracle却没有使用索引。  
----因此Oracle认为走索引的Cost比走全表扫描大，而是大N倍，CBO是基于Cost来决定执行计划的。  
----由此得出，对于索引的Cost，Oracle是根据clustering factor参数来计算的，而该实验中的clustering factor参数是很高的，数据存储无序。这就造成了Oracle认为走索引的cost比全表扫描的大。  
复制代码  
   4.2、解决问题：  


复制代码  
  ----通过上面的分析，可以看出，要降低clustering factor才能解决问题，而要解决clustering factor，就需要重新对表的存储位置进行排序。----  
  ----重建jakc表----  
  1 SQL> create table echo as select * from jack where 1=0;  
  2   
  3 Table created.  
  4   
  5 SQL> insert /*+ append */ into echo select * from jack order by object_id;  
  6   
  7 725460 rows created.  
  8   
  9 SQL> commit;  
 10   
 11 Commit complete.  
 12   
 13 SQL> truncate table jack;  
 14   
 15 Table truncated.  
 16   
 17 SQL> insert /*+ append */ into jack select * from echo;  
 18   
 19 725460 rows created.  
 20   
 21 SQL> commit;  
 22   
 23 Commit complete.  
 24   
  ----查看表和索引的信息----  
 25 SQL> select segment_name,blocks,extents,bytes/1024/1024||'M' "size" from user_segments where segment_name='JACK';  
 26   
 27 SEGMENT_NAME    BLOCKS    EXTENTS     size  
 28 ------------- ---------- ---------- -----------  
 29 JACK             11264       82        88M  
 30   
 31 SQL> select segment_name,segment_type,blocks,extents,bytes/1024/1024||'M' "size" from user_segments where segment_name='JACK_IND';  
 32   
 33 SEGMENT_NAME    SEGMENT_TYPE      BLOCKS     EXTENTS    size  
 34 ------------ ------------------ ---------- ---------- -------------  
 35 JACK_IND            INDEX          1536          27     12M  
 36   
 37 SQL> select index_name,clustering_factor,num_rows from user_indexes where index_name='JACK_IND';  
 38   
 39 INDEX_NAME     CLUSTERING_FACTOR NUM_ROWS  
 40 ------------- ----------------- ----------  
 41 JACK_IND             725460      725460  
 42   
  ----对索引进行rebuild----  
 43 SQL> alter index jack_ind rebuild;  
 44   
 45 Index altered.  
 46   
  ----查看cluster factor----  
 47 SQL> select index_name,clustering_factor,num_rows from user_indexes where index_name='JACK_IND';  
 48   
 49 INDEX_NAME      CLUSTERING_FACTOR  NUM_ROWS  
 50 --------------- ----------------- ----------  
 51 JACK_IND               10327       725460    ------注意这里的Factor，已经变成10327，我们收集一下表的统计信息，然后与表的block进行一次比较。  
 52   
 53 SQL> exec dbms_stats.gather_table_stats(user,'jack',cascade=>true);  
 54   
 55 PL/SQL procedure successfully completed.  
 56   
 57 SQL> select blocks from dba_tables where table_name='JACK';  
 58   
 59     BLOCKS  
 60 ----------  
 61      10474   ----表jack实际使用的block是10474，clustering factor是10327基本还是比较接近了，这也说明相邻的row是存储在相同的block里。  
 62   
 63 SQL> select index_name,clustering_factor,num_rows from user_indexes where index_name='JACK_IND';  
 64   
 65 INDEX_NAME               CLUSTERING_FACTOR   NUM_ROWS  
 66 ------------------------------ ----------------- ----------  
 67 JACK_IND                   10327     725460  
 68   
 69 SQL> alter system flush buffer_cache;  
 70   
 71 System altered.  
 72   
 73 SQL> set autotrace traceonly;  
  ----再次查看之前sql的执行计划----  
 74 SQL> select * from jack where object_id>1000 and object_id<2000;  
 75   
 76 9880 rows selected.  
 77   
 78   
 79 Execution Plan  
 80 ----------------------------------------------------------  
 81 Plan hash value: 2860868395  
 82   
 83 ----------------------------------------------------------------------------------------  
 84 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
 85 ----------------------------------------------------------------------------------------  
 86 |   0 | SELECT STATEMENT        |           |  9657 |   914K|   162     (0)| 00:00:02 |  
 87 |   1 |  TABLE ACCESS BY INDEX ROWID| JACK     |  9657 |   914K|   162     (0)| 00:00:02 |  
 88 |*  2 |   INDEX RANGE SCAN        | JACK_IND |  9657 |       |    24     (0)| 00:00:01 |  
 89 ----------------------------------------------------------------------------------------  
 90   
 91 Predicate Information (identified by operation id):  
 92 ---------------------------------------------------  
 93   
 94    2 - access("OBJECT_ID">1000 AND "OBJECT_ID"<2000)  
 95   
 96   
 97 Statistics  
 98 ----------------------------------------------------------  
 99       1   recursive calls  
100       0   db block gets  
101     1457  consistent gets  
102     151   physical reads  
103       0   redo size  
104   988947  bytes sent via SQL*Net to client  
105     7657  bytes received via SQL*Net from client  
106     660   SQL*Net roundtrips to/from client  
107       0   sorts (memory)  
108       0   sorts (disk)  
109     9880  rows processed  
  ----注意这里的cost已经降到了162，性能提升还是非常明显。  
复制代码  
 五、小结  


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

---

---

降低列值聚簇因子 提高查询效率
 

oracle 在 dba_indexes 视图中提供一个名为 clustering_factor 的列，通知优化器关于表的行与索引的同步情况。当集簇因子接近数据块的数量时，表的行与索引同步,即列值相同的数据行存放得比较集中，聚集度高。 列值的选择性、db_block_size、avg_row_len 以及集合基数全都协同工作，帮助优化器决定是使用索引还是使用全表扫描。如果数据列具有高度的选择性和低的 clustering_factor，则索引扫描通常是最快的执行方法。即使列具有高度的选择性，高 clustering_factor 和小 avg_row_len 也会表示列值在表中随机分布，而获取这些行需要额外的 I/O。在此情况下，索引范围扫描会导致大量不必要的 I/O;全表扫描则会高效得多。

【实验环境】

操作系统：RHEL 5.5

数据库：Oracle 10.2.0

【实验过程】

1、环境部署

1.1、创建表、添加索引

SCOTT@ prod>create table test as select * from emp;

SCOTT@ prod>create index ind_test_empno on test (empno);

1.2、分析表

 SCOTT@ prod>analyze table test compute statistics;

1.3、查看列值的集簇因子：

 SYS@ prod>select t.table_name,t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,i.index_name,

i.CLUSTERING_FACTOR from dba_tables t,dba_indexes i where t.table_name=i.table_name

and t.owner='SCOTT' and t.table_name='TEST';

 

TABLE_NAME   NUM_ROWS  BLOCKS AVG_ROW_LEN INDEX_NAME      CLUSTERING_FACTOR

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

TEST               14      4          40 IND_TEST_EMPNO                 1

 

NUM_ROWS 总行数14

AVG_ROW_LEN 平均每行长度40 bytes

14*40=560，一个块中14行占了560 bytes字节，一个块的大小是8192bytes（8K）。

560/8192=0.0683，560个字节约占了一个块的7%

1.4、使用索引列进行查询

SCOTT@ prod>set autotrace traceonly SCOTT@ prod>select * from test where empno=7788; Execution Plan ---------------------------------------------------------- Plan hash value: 4043037449 -------------------------------------------------------------------------------------------- | Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |                |     1 |    32 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| TEST           |     1 |    32 |     2   (0)| 00:00:01 | |*  2 |  INDEX RANGE SCAN           | IND_TEST_EMPNO |     1 |       |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("EMPNO"=7788) Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets           0  consistent gets           0  physical reads           0  redo size           0  bytes sent via SQL*Net to client           0  bytes received via SQL*Net from client           0  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed

 

1.5、修改pctfree值，改为93，插入更多值

SCOTT@ prod>alter table test pctfree 93; SCOTT@ prod>insert into test select * from emp; SCOTT@ prod>/ SCOTT@ prod>/ SCOTT@ prod>/ SCOTT@ prod>/ SCOTT@ prod>/ SCOTT@ prod>/ SCOTT@ prod>commit;

 

1.6、分析表、查看执行计划

SCOTT@ prod>analyze table test estimate statistics; SCOTT@ prod>select * from test where empno=7788; 8 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     8 |   256 |     5   (0)| 00:00:01 | |*  1 | TABLE ACCESS FULL | TEST |     8 |   256 |     5   (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("EMPNO"=7788) Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets           0  consistent gets           0  physical reads           0  redo size           0  bytes sent via SQL*Net to client           0  bytes received via SQL*Net from client           0  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           8  rows processed

 

1.7、查看集簇因子

SYS@ prod>select t.table_name,t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,i.index_name, i.CLUSTERING_FACTOR from dba_tables t,dba_indexes i  where t.table_name=i.table_name  and t.owner='SCOTT' and t.table_name='TEST';   TABLE_NAME   NUM_ROWS  BLOCKS AVG_ROW_LEN INDEX_NAME      CLUSTERING_FACTOR ---------- ---------- ------- ----------- --------------- ----------------- TEST             112      16          40 IND_TEST_EMPNO               112

 

CLUSTERING_FACTOR的值和NUM_ROWS的值接近，列值相同的行比较分散，走全表扫描。

可以对emp3重新排序，把列值相同的行变的集中，降低集簇因子的值。

2、改变集簇因子

2.1、创建中间表存放数据

SCOTT@ prod>create table test_tmp as select * from test;

2.2、truncate原表

SCOTT@ prod>truncate table test;

2.3、按顺序重新插入数据

SCOTT@ prod>insert into test select * from test_tmp order by empno; SCOTT@ prod>commit; SCOTT@ prod>select * from test where rownum < 10;  EMPNO ENAME JOB         MGR HIREDATE                SAL   COMM  DEPTNO ------ ----- --------- ----- ------------------- ------- ------ -------   7369 SMITH CLERK      7902 1980-12-17 00:00:00     800             20   7369 SMITH CLERK      7902 1980-12-17 00:00:00     800             20   7369 SMITH CLERK      7902 1980-12-17 00:00:00     800             20   7369 SMITH CLERK      7902 1980-12-17 00:00:00     800             20   7369 SMITH CLERK      7902 1980-12-17 00:00:00     800             20   7369 SMITH CLERK      7902 1980-12-17 00:00:00     800             20   7369 SMITH CLERK      7902 1980-12-17 00:00:00     800             20   7369 SMITH CLERK      7902 1980-12-17 00:00:00     800             20   7499 ALLEN SALESMAN   7698 1981-02-20 00:00:00    1600    300      30 9 rows selected.

 

2.4、重新分析表、查看执行计划

SCOTT@ prod>analyze table test estimate statistics; SCOTT@ prod>select * from test where empno=7788; 8 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4043037449 -------------------------------------------------------------------------------------------- | Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |                |     8 |   256 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| TEST           |     8 |   256 |     2   (0)| 00:00:01 | |*  2 |  INDEX RANGE SCAN         | IND_TEST_EMPNO |     8 |       |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("EMPNO"=7788) Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets           0  consistent gets           0  physical reads           0  redo size           0  bytes sent via SQL*Net to client           0  bytes received via SQL*Net from client           0  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           8  rows processed

这次执行计划选择了索引

2.5、查看集簇因子的改变

SYS@ prod>select t.table_name,t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,i.index_name,  i.CLUSTERING_FACTOR from dba_tables t,dba_indexes i  where t.table_name=i.table_name and t.owner='SCOTT' and t.table_name='TEST'; TABLE_NAME   NUM_ROWS  BLOCKS AVG_ROW_LEN INDEX_NAME      CLUSTERING_FACTOR ---------- ---------- ------- ----------- --------------- ----------------- TEST              112     13          40 IND_TEST_EMPNO                13