Oracle12c中效能最佳化&功能增強新特性之全域性索引DROP和TRUNCATE 分割槽的非同步維護

1.   設定

下面的例子演示帶全域性索引的表建立和載入資料的過程。

-- 建表

CREATE TABLE t1

(id            NUMBER,

 comment   VARCHAR2(50),

 crt_time  DATE)

PARTITION BY RANGE (crt_time)

(PARTITION part_14 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY'))TABLESPACE users,

 PARTITION part_15 VALUES LESS THAN(TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users);

 

ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id);

CREATE INDEX t1_idx ON t1 (crt_time);

 

--載入資料

INSERT /*+ APPEND */ INTO t1

SELECT level,

       'commit for ' || level,

       CASE

         WHEN MOD(level,2) = 0 THENTO_DATE('01/07/2014', 'DD/MM/YYYY')

         ELSE TO_DATE('01/07/2015','DD/MM/YYYY')

       END

FROM   dual

CONNECT BY level <= 10000;

COMMIT;

 

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

 

-- 檢查索引

COLUMN table_name FORMAT A20

COLUMN index_name FORMAT A20

 

SElECT table_name,

       index_name,

       status

FROM   user_indexes

ORDER BY 1,2;

 

TABLE_NAME           INDEX_NAME           STATUS

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

T1                   T1_IDX               VALID

T1                   T1_PK                VALID

SQL>

2.    全域性索引非同步維護

現在，DROP和TRUNCATE PARTITION命令和UPDATE_INDEXES一起使用時，只會導致相關後設資料的變化。該功能目前只能用於堆表，不支援物件表，域索引或SYS使用者的物件。

實際的索引維護稍後被執行，當滿足如下之一的條件時。

• SYS.PMO_DEFERRED_GIDX_MAINT_JOB作業每天2點被排程時。
• 透過 DBMS_SCHEDULER.RUN_JOB手工執行SYS.PMO_DEFERRED_GIDX_MAINT_JOB時。
• 執行DBMS_PART.CLEANUP_GIDX過程時。
• 執行ALTER INDEX REBUILD [PARTITION]命令時。
• 執行ALTER INDEX [PARTITION] COALESCE CLEANUP命令時。

Oracle12c前，如果我們DROP或TRUNCATE了一個分割槽，將會導致全域性索引失效，UPDATE_INDEXES子句會導致操作期間索引重建，使得整個操作變慢。下例中，我們TRUNCATE一個分割槽，然後檢視索引狀態。

-- Truncate一個分割槽

ALTER TABLE t1 TRUNCATE PARTITIONpart_2014 DROP STORAGE UPDATE INDEXES;

或

ALTER TABLE t1 DROP PARTITION part_2014UPDATE INDEXES;

 

-- 檢視索引狀態

SElECT table_name,

      index_name,

      status

FROM  user_indexes

ORDER BY 1,2;

 

TABLE_NAME           INDEX_NAME           STATUS

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

T1                   T1_IDX               VALID

T1                   T1_PK                VALID

 

SQL>

檢視USER_INDEXE中ORPHANED_ENTRIES新列顯示索引還沒被維護。

-- 檢查是否需要索引維護

SELECT index_name,

      orphaned_entries

FROM  user_indexes

ORDER BY 1;

 

INDEX_NAME           ORP

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

T1_IDX               YES

T1_PK                YES

 

SQL>如果我們手工觸發索引維護，我們將會看到ORPHANED_ENTRIES列的變化

-- 手工觸發索引維護

EXECDBMS_PART.cleanup_gidx(USER, 't1');

 

-- 檢視是否需要索引維護

SELECT index_name,

       orphaned_entries

FROM   user_indexes

ORDER BY 1;

 

INDEX_NAME           ORP

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

T1_IDX               NO

T1_PK                NO

 

SQL>