创建本地基表的物化视图

Oracle的物化视图(Materialized View)是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者是用来生成基于数据表求和的汇总表。
物化视图可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，提高查询性能。
物化视图需要占用存储空间，物化视图由于是物理真实存在的，故可以创建索引。当基表发生变化时，物化视图也应当刷新。
 
在以下创建物化视图的例子中，基表是Oracle数据库所带的HR Schema中的employees表。拥有基表的用户是hr，创建物化视图的用户是george。
==================================================================
创建本地基表的on commit refresh物化视图
==================================================================
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0    Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> conn george/george
Connected.
SQL> select * from user_sys_privs;
USERNAME                 PRIVILEGE                           ADM
------------------------ ----------------------------------- ---
GEORGE                   UNLIMITED TABLESPACE                NO

创建物化视图需要的权限 以sysdba授权
SQL> conn sys/welcome1 as sysdba
Connected.
SQL> grant create any materialized view to george;
Grant succeeded.

创建物化视图日志
视图的刷新将采用增量刷新的方式，为配合增量刷新，ORACLE要求要在基表上建立物化视图日志。
SQL> create materialized view log on employees with primary key including new values;

创建on commit refresh物化视图
SQL> conn george/george
Connected.
SQL> select * from user_sys_privs;
USERNAME                 PRIVILEGE                           ADM
------------------------- ---------------------------------- ---
GEORGE                   CREATE ANY MATERIALIZED VIEW        NO
GEORGE                   UNLIMITED TABLESPACE                NO
SQL> create materialized view mv_employees build immediate refresh fast with primary key on commit as select * from hr.employees;
create materialized view mv_employees build immediate refresh fast with primary key on commit as select * from hr.employees
                                                                                                                  *
ERROR at line 1:
ORA-01031: insufficient privileges

授予用户george基表的ON COMMIT REFRESH权限
SQL> conn hr/hr
Connected.
SQL> grant ON COMMIT REFRESH on employees to george;
SQL> grant select on mlog$_employees to george;

SQL> conn george/george
Connected.
SQL> create materialized view mv_employees build immediate refresh fast with primary key on commit as select * from hr.employees;
Materialized view created.

在基表中插入测试数据
SQL> conn hr/hr
Connected.
SQL> select count(*) from employees;
  COUNT(*)
----------
       107
SQL> INSERT INTO employees VALUES
        ( 208
        , 'Heather'
        , 'Wang'
        , 'hw@bds.com'
        , '515.123.8181'
        , TO_DATE('07-JUN-1994', 'dd-MON-yyyy')
        , 'AC_ACCOUNT'
        , 8300
        , NULL
        , 205
        , 110
        );  2    3    4    5    6    7    8    9   10   11   12   13  
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist

SQL> grant select on employees to george;
SQL> INSERT INTO employees VALUES
        ( 208
        , 'Heather'
        , 'Wang'
        , 'hw@bds.com'
        , '515.123.8181'
        , TO_DATE('07-JUN-1994', 'dd-MON-yyyy')
        , 'AC_ACCOUNT'
        , 8300
        , NULL
        , 205
        , 110
        );  2    3    4    5    6    7    8    9   10   11   12   13  
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from employees;
  COUNT(*)
----------
       108
SQL>

在物化视图中验证数据已刷新
SQL> conn george/george
Connected.
SQL> select count(*) from mv_employees;
  COUNT(*)
----------
       108

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

转载于:http://blog.itpub.net/29960937/viewspace-1406115/