ORACLE 聚簇因子優化

聚簇因子：資料表中的資料都是無序的存在庫中，當我們對資料進行檢索的時候，查詢起來很是耗費資源，於是我們就需要為表建立索引，索引的作用就是把表中的資料按照一定的順序排列儲存起來，於是就出現了一個問題，有的表中的資料和索引排列的順序很是相近，而另一些表中的資料和索引排列的順序相距甚遠，聚簇因子的作用就是用來標記這個的，聚簇因子越小，相似度越高，聚簇因子越大，相似度越低。

資料和索引相似度：ORACLE 在儲存資料時，並不是按照資料塊的順序挨個進行存入資料，因為前面存入的資料經常會有DML或者DDL操作，刪除資料後，原先存有資料的資料塊就變成了空塊，ORACLE為了節省儲存空間，當資料庫再次有新資料進行插入的話，就會優先使用那些空塊，只有當空塊不夠使用的時候，才會去高水位上開闢新塊，這種情況也就會導致，一張表中的資料，並不是儲存在相鄰的資料塊中，於是聚簇因子變的很大，當這種情況進行邏輯讀取的時候，就會增加IO的次數， 影響讀取的速度。

測試：

建表：

CREATE TABLE t_1 AS SELECT ROWNUM rn,a.* FROM all_objects a ORDER BY object_name DESC;

--建立t_1表關於rownum索引

CREATE INDEX ind_t_1 ON t_1(rn);

--建立表表t_2

CREATE TABLE t_2 AS SELECT * FROM ( SELECT ROWNUM rn,a.* FROM all_objects a ) ORDER BY rn ASC;

 

--建立t_2表關於rownum索引

CREATE INDEX ind_t_2 ON t_2(rn);

 

--分析兩張表及其索引

EXEC DBMS_STATS.gather_table_stats(USER, 'T_1');

EXEC DBMS_STATS.gather_table_stats(USER, 'T_2');

EXEC DBMS_STATS.gather_index_stats(USER, 'IND_T_1');

EXEC DBMS_STATS.gather_index_stats(USER, 'IND_T_2');

執行查詢操作

SQL> set autotrace traceonly;

SQL> SELECT * FROM t_1 WHERE rn BETWEEN 100 AND 120;

21 rows selected.

Execution Plan

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

Plan hash value: 923245147

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

-------

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

e     |

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

-------

|   0 | SELECT STATEMENT            |         |    22 |  2244 |    14   (0)| 00:

00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_1     |    22 |  2244 |    14   (0)| 00:

00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_T_1 |    22 |       |     2   (0)| 00:

00:01 |

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

-------

Predicate Information (identified by operation id):

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

   2 - access("RN">=100 AND "RN"<=120)

Statistics

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

        445  recursive calls

          0  db block gets

         85  consistent gets

          0  physical reads

          0  redo size

       3426  bytes sent via SQL*Net to client

        427  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          4  sorts (memory)

          0  sorts (disk)

         21  rows processed

表二查詢：

SELECT * FROM t_2 WHERE rn BETWEEN 100 AND 120;

21 rows selected.

Execution Plan

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

Plan hash value: 2193767187

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

-------

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

e     |

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

-------

|   0 | SELECT STATEMENT            |         |    22 |  2244 |     3   (0)| 00:

00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_2     |    22 |  2244 |     3   (0)| 00:

00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_T_2 |    22 |       |     2   (0)| 00:

00:01 |

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

-------

Predicate Information (identified by operation id):

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

   2 - access("RN">=100 AND "RN"<=120)

Statistics

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

        445  recursive calls

          0  db block gets

         69  consistent gets

          0  physical reads

          0  redo size

       3426  bytes sent via SQL*Net to client

        427  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          4  sorts (memory)

          0  sorts (disk)

         21  rows processed

觀察結果：

 通過執行統計資訊觀察，T_1表的查詢一致讀是85，而T_2表的一致讀只有69，周樣的表結構，同樣的資料，為何有這麼大的差距呢。

分析：

SQL> select b.table_name, a.index_name, b.num_rows, b.blocks, a.clustering_factor from user_indexes a, user_tables b where b.table_name in('T_1','T_2') and a.table_name = b.table_name;

TABLE_NAME                     INDEX_NAME             NUM_ROWS     BLOCKS CLUSTERING_FACTOR

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

T_1                            IND_T_1                   71761       1069             38034

T_2                            IND_T_2                   71763       1069              1069

通過查詢聚簇因子發現，兩個表的聚簇因子差別很大，基於RN的索引在RN是順序排列的表中，CLUSTERING_FACTOR的值相差很大。

在表中資料有時候屬於無序狀態，這個時候的CLUSTERING_FACTOR比較接近NUM_ROWS，說明如果掃描整個表，每次都要根據INDEX來讀取相應行的ROWID，這個時候的IO操作很多，自然檢索時間會比較長。如果資料有序的話，CLUSTERING_FACTOR比較接近BLOCKS，說明相鄰的資料在一個塊中，減少了IO運算元量，自然檢索時間會大大降低。

解決辦法：

第一種辦法：對錶進行重構：alter table t_1 move;

SQL> alter table t_1 move;

（

alter table move;

可以用來降低HWM，也可以用來調整已使用的block的儲存引數，

比如PCTFREE。

等於在當前tablespace中重組table。

注意這個操作後，需要rebuild index

）

Table altered.

再次查詢聚簇因子：

SQL>  select b.table_name, a.index_name, b.num_rows, b.blocks, a.clustering_factor from user_indexes a, user_tables b where b.table_name in('T_1','T_2') and a.table_name = b.table_name;

TABLE_NAME                     INDEX_NAME             NUM_ROWS     BLOCKS CLUSTERING_FACTOR

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

T_1                            IND_T_1                   71761       1069             38034

T_2                            IND_T_2                   71763       1069              1069

發現還是一樣。

第二種方法： 按照索引順序重建表：

SQL> create table t_1_bk as select * from t_1 order by rn;

Table created.

建立索引：

 create index ind_t1bk on t_1_bk(rn);

Index created.

統計索引

EXEC DBMS_STATS.gather_index_stats(USER, 'IND_T1BK');

再次檢視聚簇因子：

select b.table_name, a.index_name, b.num_rows, b.blocks, a.clustering_factor from user_indexes a, user_tables b where b.table_name in('T_1_BK','T_2') and a.table_name = b.table_name;

TABLE_NAME                     INDEX_NAME             NUM_ROWS     BLOCKS CLUSTERING_FACTOR

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

T_1_BK                         IND_T1BK                                                1069

T_2                            IND_T_2                   71763       1069              1069

可以看到聚簇因子有所降低，但沒了NUM_ROWS與BLOCKS。

在這裡沒有這兩個欄位的值是因為user_indexes 和user_tables的資訊來自於對錶的統計資訊。

SQL> exec dbms_stats.gather_table_stats(USER,'T_1_BK');

PL/SQL procedure successfully completed.

SQL> select b.table_name, a.index_name, b.num_rows, b.blocks, a.clustering_factor from user_indexes a, user_tables b where b.table_name in('T_1_BK','T_2') and a.table_name = b.table_name;

TABLE_NAME                     INDEX_NAME             NUM_ROWS     BLOCKS CLUSTERING_FACTOR

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

T_1_BK                         IND_T1BK                  71761       1069              1069

T_2                            IND_T_2                   71763       1069              1069

以上這種辦法只針對於小表， 但如果這個表的資料量非常大就不能夠使用這種辦法了。

第三種方法： 針對大表的：分割槽

線上將表改為分割槽表：

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T_1',dbms_redefinition.cons_index);

 EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T_1',dbms_redefinition.cons_index);

PL/SQL procedure successfully completed.

獲得建立表的語句：select dbms_metadata.get_ddl('TABLE','T_1','SYS') from dual;

獲得建立INDEX的語句：select dbms_metadata.get_ddl('INDEX','IND_T_1','SYS') from dual;

建立表：

create table t_1new(RN NUMBER,

        OWNER VARCHAR2(30) NOT NULL ENABLE,

        OBJECT_NAME VARCHAR2(30) NOT NULL ENABLE,

        SUBOBJECT_NAME VARCHAR2(30),

        OBJECT_ID NUMBER NOT NULL ENABLE,

        DATA_OBJECT_ID NUMBER,

        OBJECT_TYPE VARCHAR2(19),

        CREATED DATE NOT NULL ENABLE,

        LAST_DDL_TIME DATE NOT NULL ENABLE,

        TIMESTAMP VARCHAR2(19),

        STATUS VARCHAR2(7),

        TEMPORARY VARCHAR2(1),

        GENERATED VARCHAR2(1),

        SECONDARY VARCHAR2(1),

        NAMESPACE NUMBER NOT NULL ENABLE,

        EDITION_NAME VARCHAR2(30)) partition by range(rn)

(partition p1 values less than(20000),

partition p2 values less than(40000),

partition p3 values less than(60000),

partition p4 values less than(maxvalue));

建立索引：create index ind_t_1new on t_1new(rn);

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T_1', 'T_1NEW', 'RN RN, OWNER OWNER, OBJECT_NAME OBJECT_NAME, SUBOBJECT_NAME SUBOBJECT_NAME, OBJECT_ID OBJECT_ID, DATA_OBJECT_ID DATA_OBJECT_ID, OBJECT_TYPE OBJECT_TYPE, CREATED CREATED, LAST_DDL_TIME LAST_DDL_TIME, TIMESTAMP TIMESTAMP, STATUS STATUS, TEMPORARY TEMPORARY, GENERATED GENERATED, SECONDARY SECONDARY, NAMESPACE NAMESPACE, EDITION_NAME EDITION_NAME', DBMS_REDEFINITION.CONS_INDEX);

 exec dbms_redefinition.finish_redef_table('HR','T_1','T_1NEW');

PL/SQL procedure successfully completed.

驗證查詢：

SQL> select count(*) from t_1 partition(p1);

  COUNT(*)

----------

     19999

SQL> select count(*) from t_1 partition(p2);

  COUNT(*)

----------

     20000

分割槽成功！  我們這時再來看看聚簇因子：

select b.table_name, a.index_name, b.num_rows, b.blocks, a.clustering_factor from user_indexes a, user_tables b where b.table_name in('T_1') and a.table_name = b.table_name;

SQL> select b.table_name, a.index_name, b.num_rows, b.blocks, a.clustering_factor from user_indexes a, user_tables b where b.table_name in('T_1') and a.table_name = b.table_name;

TABLE_NAME                     INDEX_NAME             NUM_ROWS     BLOCKS CLUSTERING_FACTOR

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

T_1                            IND_T_1NEW                                             38104

! 無解！   咋個不行。  應該是理解問題了， 這個只能解決聚簇因子所帶來的效能問題。