10G物化檢視PCT快速重新整理不再需要物化檢視日誌（一）

Oracle10g增加了物化檢視PCT快速重新整理的支援，使用PCT快速重新整理不再需要物化檢視日誌。

 

 

在9i中，物化檢視的快速重新整理必須建立物化檢視日誌，否則物化檢視無法快速重新整理：

SQL> CREATE TABLE T
  2  (
  3   ID NUMBER,
  4   NAME VARCHAR2(30),
  5   CONSTRAINT PK_T PRIMARY KEY (ID)
  6  )
  7  PARTITION BY RANGE (ID)
  8  (
  9  PARTITION P1 VALUES LESS THAN (100),
 10  PARTITION P2 VALUES LESS THAN (200),
 11  PARTITION P3 VALUES LESS THAN (MAXVALUE)
 12  );

表已建立。

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS
  2  SELECT * FROM T;
SELECT * FROM T
              *
第 2 行出現錯誤:
ORA-23413: 表 "TEST"."T" 不帶實體化檢視日誌

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

在10g中，分割槽表不在需要建立物化檢視日誌就可以實現物化檢視的快速重新整理：

SQL> CONN YANGTK/YANGTK@YTK
已連線。
SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> CREATE TABLE T
  2  (
  3   ID NUMBER,
  4   NAME VARCHAR2(30),
  5   CONSTRAINT PK_T PRIMARY KEY (ID)
  6  )
  7  PARTITION BY RANGE (ID)
  8  (
  9  PARTITION P1 VALUES LESS THAN (100),
 10  PARTITION P2 VALUES LESS THAN (200),
 11  PARTITION P3 VALUES LESS THAN (MAXVALUE)
 12  );

表已建立。

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS
  2  SELECT * FROM T;

實體化檢視已建立。

雖然Oracle支援不建立物化檢視日誌，但是要注意，不建立物化檢視日誌的快速重新整理採用的是PCT的快速重新整理，而不是普通的增量重新整理。

SQL> @?/RDBMS/ADMIN/UTLXMV

表已建立。

SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW('SELECT * FROM T')

PL/SQL 過程已成功完成。

SQL> SELECT CAPABILITY_NAME, POSSIBLE, MSGTXT
  2  FROM MV_CAPABILITIES_TABLE;

CAPABILITY_NAME                P MSGTXT
------------------------------ - ---------------------------------------------
PCT                            Y
REFRESH_COMPLETE               Y
REFRESH_FAST                   Y
REWRITE                        Y
PCT_TABLE                      Y
REFRESH_FAST_AFTER_INSERT      N 詳細資訊表沒有實體化檢視日誌
REFRESH_FAST_AFTER_ONETAB_DML  N 檢視禁用 REFRESH_FAST_AFTER_INSERT 的原因
REFRESH_FAST_AFTER_ANY_DML     N 檢視禁用 REFRESH_FAST_AFTER_ONETAB_DML 的原因
REFRESH_FAST_PCT               Y
REWRITE_FULL_TEXT_MATCH        Y
REWRITE_PARTIAL_TEXT_MATCH     Y
REWRITE_GENERAL                Y
REWRITE_PCT                    Y
PCT_TABLE_REWRITE              Y

已選擇14行。

從上面的資訊不難看出，物化檢視支援的是PCT快速重新整理，而不是增量重新整理，這就意味著進行普通增量修改的重新整理將花費大量的時間：

SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS;

已建立50691行。

SQL> COMMIT;

提交完成。

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')

PL/SQL 過程已成功完成。

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
     50691

SQL> SELECT COUNT(*) FROM MV_T;

  COUNT(*)
----------
     50691

下面對比一下刪除一條記錄對應的重新整理時間：

SQL> DELETE T WHERE ID = 1;

已刪除 1 行。

SQL> SET TIMING ON
SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')

PL/SQL 過程已成功完成。

已用時間:  00: 00: 00.09
SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
     50690

已用時間:  00: 00: 00.06
SQL> SELECT COUNT(*) FROM MV_T;

  COUNT(*)
----------
     50690

已用時間:  00: 00: 00.06
SQL> DELETE T WHERE ID = 10000;

已刪除 1 行。

已用時間:  00: 00: 00.03
SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')

PL/SQL 過程已成功完成。

已用時間:  00: 00: 06.01
SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
     50689

已用時間:  00: 00: 00.04
SQL> SELECT COUNT(*) FROM MV_T;

  COUNT(*)
----------
     50689

已用時間:  00: 00: 00.06

從執行時間不難分析出，PCT重新整理是針對某個分割槽進行的重新整理，而每次重新整理的應該是整個分割槽。由於ID為1的記錄所在分割槽資料量小，重新整理速度就很快，而ID為10000的記錄對應的分割槽資料量大，因此重新整理所需時間就長。

在這種情況下建立物化檢視，如果建立了物化檢視日誌，Oracle在確認可以正常使用物化檢視日誌後，就會選擇利用物化檢視日誌儘量增量重新整理，下面可以對比一下兩種重新整理對於普通增量重新整理的效能：

SQL> SELECT MVIEW_NAME, REFRESH_METHOD, FAST_REFRESHABLE, LAST_REFRESH_TYPE
  2  FROM USER_MVIEWS
  3  WHERE MVIEW_NAME = 'MV_T';

MVIEW_NAME                     REFRESH_ FAST_REFRESHABLE   LAST_REF
------------------------------ -------- ------------------ --------
MV_T                           FAST     DML                FAST_PCT

已用時間:  00: 00: 00.06
SQL> CREATE MATERIALIZED VIEW LOG ON T;

實體化檢視日誌已建立。

已用時間:  00: 00: 00.20
SQL> DELETE T WHERE ID = 10001;

已刪除 1 行。

已用時間:  00: 00: 00.04
SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')

PL/SQL 過程已成功完成。

已用時間:  00: 00: 03.64
SQL> SELECT MVIEW_NAME, REFRESH_METHOD, FAST_REFRESHABLE, LAST_REFRESH_TYPE
  2  FROM USER_MVIEWS
  3  WHERE MVIEW_NAME = 'MV_T';

MVIEW_NAME                     REFRESH_ FAST_REFRESHABLE   LAST_REF
------------------------------ -------- ------------------ --------
MV_T                           FAST     DML                FAST_PCT

已用時間:  00: 00: 00.04
SQL> DELETE T WHERE ID = 10002;

已刪除 1 行。

已用時間:  00: 00: 00.04
SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')

PL/SQL 過程已成功完成。

已用時間:  00: 00: 00.25
SQL> SELECT MVIEW_NAME, REFRESH_METHOD, FAST_REFRESHABLE, LAST_REFRESH_TYPE
  2  FROM USER_MVIEWS
  3  WHERE MVIEW_NAME = 'MV_T';

MVIEW_NAME                     REFRESH_ FAST_REFRESHABLE   LAST_REF
------------------------------ -------- ------------------ --------
MV_T                           FAST     DML                FAST

已用時間:  00: 00: 00.04

可以看到，雖然PCT重新整理使得物化檢視日誌不在是物化檢視快速重新整理的必要條件，但是仍然推薦對於分割槽表建立物化檢視日誌。因為PCT重新整理是針對整個分割槽而言，而用來進行日常增量代價要比物化檢視方式大得多，最好的方法仍然是物化檢視日誌配合PCT來進行物化檢視的快速重新整理。