---

---

  聚簇因子是 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、實戰聚簇因子隨索引結構變化的情形

8、實戰聚簇因子隨DML變化的情形

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、創建表、添加索引

1.2、分析表

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

 

NUM_ROWS 總行數14

AVG_ROW_LEN 平均每行長度40 bytes

14*40=560，一個塊中14行佔了560 bytes字節，一個塊的大小是8192bytes（8K）。

560/8192=0.0683，560個字節約佔了一個塊的7%

1.4、使用索引列進行查詢

[email protected] prod>set autotrace traceonly [email protected] 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，插入更多值

[email protected] prod>alter table test pctfree 93; [email protected] prod>insert into test select * from emp; [email protected] prod>/ [email protected] prod>/ [email protected] prod>/ [email protected] prod>/ [email protected] prod>/ [email protected] prod>/ [email protected] prod>commit;

 

1.6、分析表、查看執行計劃

[email protected] prod>analyze table test estimate statistics; [email protected] 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、查看集簇因子

[email protected] 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、創建中間表存放數據

[email protected] prod>create table test_tmp as select * from test;

2.2、truncate原表

[email protected] prod>truncate table test;

2.3、按順序重新插入數據

[email protected] prod>insert into test select * from test_tmp order by empno; [email protected] prod>commit; [email protected] 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、重新分析表、查看執行計劃

[email protected] prod>analyze table test estimate statistics; [email protected] 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、查看集簇因子的改變

[email protected] 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

 

【實驗總結】

1、如果CLUSTERING_FACTOR的值和BLOCKS的值比較接近，則列值相同的行比較集中，則執行計劃應該走索引。 2、如果CLUSTERING_FACTOR的值和NUM_ROWS的值比較接近，則列值相同的行比較分散，這樣優化器會選擇走全表掃描。如果想要走索引，需要對原表進行重排序，降低集簇因子的值。