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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/207/viewspace-782976/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/207/viewspace-782976/