oracle如何创建基表,创建本地基表的物化视图

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;

USERNAMEPRIVILEGEADM

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

GEORGE                   UNLIMITED TABLESPACENO

创建物化视图需要的权限 以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;

USERNAMEPRIVILEGEADM

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

GEORGE                   CREATE ANY MATERIALIZED VIEW        NO

GEORGE                   UNLIMITED TABLESPACENO

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