一步步实现物化视图FAST刷新
fast刷新指的物化视图的增量刷新。
创建物化视图
需要使用SQL语句
SQL> select e.empno,e.ename,e.deptno
from emp e,dept d
where e.deptno=d.deptno
and d.loc='DALLAS';
EMPNO ENAME DEPTNO
---------- ---------- ----------
7566 JONES 20
7902 FORD 20
7876 ADAMS 20
7369 SMITH 20
7788 SCOTT 20
创建materialized view
SQL> create materialized view emp_loc_mv
2 as
3 select e.empno,e.ename,e.deptno
4 from emp e,dept d
5 where e.deptno=d.deptno
6 and d.loc='DALLAS';
Materialized view created.
查看物化视图结果
SQL> select * from emp_loc_mv;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7566 JONES 20
7902 FORD 20
7876 ADAMS 20
7369 SMITH 20
7788 SCOTT 20
查看该物化视图详细信息:
1.获得DDL定义
SQL> set long 20000000
SQL> set pagesize 0
SQL> select dbms_metadata.get_ddl('MATERIALIZED VIEW','EMP_LOC_MV','SCOTT') from dual;
ERROR:
ORA-31600: invalid input value MATERIALIZED VIEW for parameter OBJECT_TYPE in
function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1
get_ddl的时候提示没有“MATERIALIZED VIEW”这个类型,下面改为table
SQL> select dbms_metadata.get_ddl('TABLE','EMP_LOC_MV','SCOTT') from dual;
CREATE TABLE "SCOTT"."EMP_LOC_MV"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"DEPTNO" NUMBER(2,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
在该DDL信息中,并没有记录物化视图相关的刷新信息。
DDL的相关内容还可以通过user_mviews数据字典获得select mview_name ,query from user_mviews;
相关刷新信息可以通过dbms_mview.explain_mview查