物化檢視的CONSIDER FRESH語句（一）

物化檢視的修改語句ALTER MATERIALIZED VIEW包含了CONSIDER FRESH語句。透過這個語句可以使得資料庫認為物化檢視已經是重新整理後的狀態。同時可以使得這個物化檢視對於查詢重新生效。

這篇簡單討論CONSIDER FRESH語句的用法。

 

 

透過一個簡單的例子說明如何使用CONSIDER FRESH語句：

SQL> CREATE TABLE T
  2  (ID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(30));

表已建立。

SQL> CREATE MATERIALIZED VIEW LOG ON T;

實體化檢視日誌已建立。

SQL> CREATE MATERIALIZED VIEW MV_T1
  2  REFRESH FAST
  3  AS SELECT *     
  4  FROM T;

實體化檢視已建立。

SQL> INSERT INTO T 
  2  SELECT ROWNUM, TNAME 
  3  FROM TAB;

已建立8行。

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T1')

PL/SQL 過程已成功完成。

SQL> SELECT COUNT(*)
  2  FROM MV_T1;

  COUNT(*)
----------
         8

SQL> INSERT INTO T
  2  VALUES (9, 'TEST');

已建立 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT COUNT(*)
  2  FROM T;  

  COUNT(*)
----------
         9

SQL> ALTER MATERIALIZED VIEW MV_T1
  2  CONSIDER FRESH;

實體化檢視已更改。

SQL> SELECT COUNT(*)
  2  FROM MV_T1;

  COUNT(*)
----------
         8

SQL> INSERT INTO T
  2  VALUES (10, 'NEW');

已建立 1 行。

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T1')

PL/SQL 過程已成功完成。

SQL> SELECT *
  2  FROM MV_T1;

        ID NAME
---------- ------------------------------
         1 MLOG$_T
         2 MV_T1
         3 RUPD$_T
         4 T
         5 T_PART_HASH
         6 T_PART_INTER
         7 T_PART_LIST
         8 T_PART_RANGE
        10 NEW

已選擇9行。

SQL> INSERT INTO MV_T1
  2  VALUES (9, 'TEST');
INSERT INTO MV_T1
            *
第 1 行出現錯誤:
ORA-01732: 此檢視的資料操縱操作非法

可以看到，正常的物化檢視可以利用CONSIDER FRESH，之後也不影響物化檢視的快速重新整理，但是物化檢視和原表的資料可能會查詢差異。

而這種差異在物化檢視端是沒有辦法透過DML修改來修正的。

嘗試建立可寫的物化檢視：

SQL> CREATE MATERIALIZED VIEW MV_T2
  2  REFRESH FAST
  3  FOR UPDATE
  4  AS SELECT *
  5  FROM T;

實體化檢視已建立。

SQL> INSERT INTO T      
  2  VALUES (11, 'ABC');

已建立 1 行。

SQL> COMMIT;

提交完成。

SQL> INSERT INTO MV_T2
  2  VALUES (11, 'AB');

已建立 1 行。

SQL> ALTER MATERIALIZED VIEW MV_T2
  2  CONSIDER FRESH;
ALTER MATERIALIZED VIEW MV_T2
       *
第 1 行出現錯誤:
ORA-03001: 未實施的功能

可寫的物化檢視可以執行DML，但是不能執行CONSIDER FRESH語句，那麼使用CONSIDER FRESH只能通知Oracle物化檢視已經處於FRESH狀態，而不能進行任何的實際修改嗎，事實上，CONSIDER FRESH支援分割槽裝載操作。

SQL> CREATE TABLE T_PART
  2  (ID NUMBER,
  3  NAME VARCHAR2(30),
  4  CREATE_DATE DATE)
  5  PARTITION BY RANGE (CREATE_DATE)
  6  (PARTITION P1 VALUES LESS THAN (TO_DATE('2009-1', 'YYYY-MM')),
  7  PARTITION P2 VALUES LESS THAN (TO_DATE('2010-1', 'YYYY-MM')),
  8  PARTITION P3 VALUES LESS THAN (TO_DATE('2011-1', 'YYYY-MM')));

表已建立。

SQL> ALTER TABLE T_PART    
  2  ADD CONSTRAINT PK_T_PART
  3  PRIMARY KEY (ID);

表已更改。

SQL> INSERT INTO T_PART               
  2  SELECT ROWNUM, OBJECT_NAME, CREATED
  3  FROM ALL_OBJECTS
  4  WHERE CREATED < TO_DATE('2010-1', 'YYYY-MM');

已建立55629行。

SQL> COMMIT;

提交完成。

SQL> CREATE MATERIALIZED VIEW LOG ON T_PART;

實體化檢視日誌已建立。

SQL> CREATE MATERIALIZED VIEW MV_T_PART
  2  PARTITION BY RANGE (CREATE_DATE)
  3  (PARTITION P1 VALUES LESS THAN (TO_DATE('2009-1', 'YYYY-MM')),
  4  PARTITION P2 VALUES LESS THAN (TO_DATE('2010-1', 'YYYY-MM')),
  5  PARTITION P3 VALUES LESS THAN (TO_DATE('2011-1', 'YYYY-MM')))
  6  REFRESH FAST
  7  AS SELECT *
  8  FROM T_PART;

實體化檢視已建立。

SQL> CREATE TABLE T_LOAD
  2  (ID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(30),
  4  CREATE_DATE DATE);

表已建立。

SQL> INSERT INTO T_LOAD
  2  SELECT 55629 + ROWNUM, OBJECT_NAME, CREATED
  3  FROM ALL_OBJECTS
  4  WHERE CREATED >= TO_DATE('2010-1', 'YYYY-MM');

已建立61行。

SQL> COMMIT;

提交完成。

SQL> ALTER TABLE T_PART
  2  EXCHANGE PARTITION P3
  3  WITH TABLE T_LOAD;

表已更改。

SQL> INSERT INTO T_LOAD
  2  SELECT 55629 + ROWNUM, OBJECT_NAME, CREATED
  3  FROM ALL_OBJECTS
  4  WHERE CREATED >= TO_DATE('2010-1', 'YYYY-MM');
INSERT INTO T_LOAD
*
第 1 行出現錯誤:
ORA-01502: 索引 'TEST.SYS_C0011198' 或這類索引的分割槽處於不可用狀態

SQL> ALTER INDEX SYS_C0011198 REBUILD;

索引已更改。

SQL> INSERT INTO T_LOAD
  2  SELECT 55629 + ROWNUM, OBJECT_NAME, CREATED
  3  FROM ALL_OBJECTS
  4  WHERE CREATED >= TO_DATE('2010-1', 'YYYY-MM');

已建立61行。

SQL> COMMIT;

提交完成。

SQL> ALTER TABLE MV_T_PART
  2  EXCHANGE PARTITION P3
  3  WITH TABLE T_LOAD;

表已更改。

SQL> ALTER MATERIALIZED VIEW MV_T_PART
  2  CONSIDER FRESH;

實體化檢視已更改。

SQL> SELECT COUNT(*)
  2  FROM T_PART;

  COUNT(*)
----------
     55690

SQL> SELECT COUNT(*)
  2  FROM MV_T_PART;

  COUNT(*)
----------
     55690

這是一個簡單的利用CONSIDER FRESH語句的例子，透過EXCHANGE分割槽的方式，是的物化檢視和基表保持一致。