materialized view (物化檢視)

一些materialized view的實現指令碼，先放上來，下次測試時可以省去檢視各文件的步驟

[@more@]

Available mechanisms:

1.      Set the refresh mode as  “ON COMMIT”, and set the refresh method as “FORCE”

SQL> GRANT CREATE MATERIALIZED VIEW TO SCOTT;

Grant succeeded.

SQL> GRANT ON COMMIT REFRESH TO SCOTT;

Grant succeeded.

SQL> GRANT QUERY REWRITE TO SCOTT;

Grant succeeded.

SQL> CONNECT SCOTT/TIGER

Connected.

--create MV with ‘ON COMMIT’ option

SQL> CREATE MATERIALIZED VIEW EMP_MV

  2  BUILD IMMEDIATE

  3  REFRESH FORCE

  4  ON COMMIT --ON COMMIT REFRESH privilege is necessary

  5  ENABLE QUERY REWRITE --QUERY REWRITE privilege is necessary

  6  AS

  7  select d.deptno deptno,

          d.dname dept_name,

          e.empno empno,

          e.ename ename

  8  from dept d,emp e

  9  where d.deptno=e.deptno

 10  /

2.      If the refresh mode of the MV is “ON DEMAND”, refresh with jobs, invoking procedure   DBMS_MVIEW.REFRESH .

--create MV with ‘ON DEMAND’ option

SQL>  CREATE MATERIALIZED VIEW EMP2_MV

  2     BUILD IMMEDIATE

  3     REFRESH FORCE

  4     ON DEMAND

  5     ENABLE QUERY REWRITE

  6     AS

  7     select d.deptno deptno,

  8            d.dname dept_name,

  9            e.empno empno,

 10            e.ename ename

 11     from dept d,emp e

 12     where d.deptno=e.deptno

 13  /

--check the result

--insert a row to table emp

SQL> insert into emp(empno,ename,job,sal,deptno)

  2  values(8001,'james1','CLERK',800,20);

已建立 1 行。

SQL> commit;

提交完成。

-- BEFORE REFRESH

SQL> select * from emp2_mv;

    DEPTNO DEPT_NAME           EMPNO ENAME

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

        20 RESEARCH             7369 SMITH

        30 SALES                7499 ALLEN

        30 SALES                7521 WARD

        20 RESEARCH             7566 JONES

        30 SALES                7654 MARTIN

        30 SALES                7698 BLAKE

        10 ACCOUNTING           7782 CLARK

        10 ACCOUNTING           7839 KING

        30 SALES                7844 TURNER

        30 SALES                7900 JAMES

        20 RESEARCH             7902 FORD

        10 ACCOUNTING           7934 MILLER

已選擇12行。

--refresh MV by procedure DBMS_MVIEW.REFRESH

SQL> begin

  2   DBMS_MVIEW.REFRESH( 'EMP2_MV',null,null,true,false,1,0,0,true);

  3  end;

  4  /

PL/SQL procedure successfully completed

-- AFTER REFRESH

SQL> select * from emp2_mv;

    DEPTNO DEPT_NAME           EMPNO ENAME

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

        20 RESEARCH             7369 SMITH

        30 SALES                7499 ALLEN

        30 SALES                7521 WARD

        20 RESEARCH             7566 JONES

        30 SALES                7654 MARTIN

        30 SALES                7698 BLAKE

        10 ACCOUNTING           7782 CLARK

        10 ACCOUNTING           7839 KING

        30 SALES                7844 TURNER

        30 SALES                7900 JAMES

        20 RESEARCH             7902 FORD

        10 ACCOUNTING           7934 MILLER

        20 RESEARCH             8001 james1

已選擇13行。

--submit a job

variable jobno number;
begin
  DBMS_JOB.submit(:jobno,
     'DBMS_MVIEW.REFRESH( ''EMP2_MV'',null,null,true,false,1,0,0,true);',
     sysdate );
end;
jobno

---------

4

--change the job to run at 6 o’clock everyday
begin
  DBMS_JOB.next_date(4, to_date(to_char(sysdate+1,'mm-dd-yyyy')||'06:00:00','mm-dd-yyyy hh24:mi:ss'));
end;

--check the job

SQL> select SCHEMA_USER,NEXT_DATE,NEXT_SEC,WHAT from user_jobs;

 JOB NEXT_DATE   NEXT_SEC  WHAT

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

 4     2004-12-1    06:00:00 DBMS_MVIEW.REFRESH( 'EMP2_MV',null,null,true,false,1,0,0,true);

 

3.      If the refresh mode of the MV is “ON DEMAND”, refresh with crontab, invoking procedure   DBMS_MVIEW.REFRESH .

 

Comments:

Build Method	Description
BUILD IMMEDIATE	Create the materialized view and then populate it with data
BUILD DEFERRED	Create the materialized view definition but do not populate it with data

 

verify what types of query rewrite are possible by calling the procedure DBMS_MVIEW.EXPLAIN_MVIEW

use DBMS_MVIEW.EXPLAIN_REWRITE to find out if (or why not) it will rewrite a specific query

 

Refresh Mode	Description
ON COMMIT	Refresh occurs automatically when a transaction that modified one of the materialized view's detail tables commits. This can be specified as long as the materialized view is fast refreshable (in other words, not complex). The ON COMMIT privilege is necessary to use this mode
ON DEMAND	Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT)

 

Refresh Option	Description
COMPLETE	Refreshes by recalculating the materialized view's defining query
FAST	Applies incremental changes to refresh the materialized view using the information logged in the materialized view logs, or from a SQL*Loader direct-path or a partition maintenance operation
FORCE	Applies FAST refresh if possible; otherwise, it applies COMPLETE refresh
NEVER	Indicates that the materialized view will not be refreshed with the Oracle refresh mechanisms