物化视图

以system用户登录，解锁HR用户,再以HR用户登录；

sqlplus system/password

SQL>alter user hr account unlock;
SQL>alter user hr identified by hr;
SQL>conn hr/hr

1、创建物化视图日志

SQL> create materialized view log on employees with rowid;

Materialized view log created.

查看物化视图日志结构
SQL> desc mlog$_employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 M_ROW$$                                            VARCHAR2(255)
 SNAPTIME$$                                         DATE
 DMLTYPE$$                                          VARCHAR2(1)
 OLD_NEW$$                                          VARCHAR2(1)
 CHANGE_VECTOR$$                                    RAW(255)

更新基表内容，查看物化视图日志的内容

SQL>update employees set salary=12000 where employee_id=101;
1 row updated.

SQL>commit;
Commit complete.

SQL> select count(*) from mlog$_employees;

  COUNT(*)
----------
         1
SQL> update employees set salary=12000 where employee_id=102;

1 row updated.

SQL> commit;
Commit complete.

SQL> select count(*) from mlog$_employees;

  COUNT(*)
----------
         2

SQL> create materialized view log on departments with rowid;

2、创建物化视图
CREATE MATERIALIZED VIEW  mv_test
REFRESH FAST ON COMMIT WITH ROWID
AS SELECT e.employee_id, e.last_name, e.salary
FROM employees e;

SQL> select count(*) from mlog$_employees;

  COUNT(*)
----------
         0

3、查询重写

注意各个步骤的执行计划和代价

SQL>create table my_all_objects
as select * from all_objects
union all
select * from all_objects
union all
select * from all_objects;

SQL>insert into my_all_objects select * from my_all_objects;

SQL>commit;
SQL>exec dbms_stats.gather_table_stats('HR','MY_ALL_OBJECTS');

SQL>set autotrace on;
SQL>select owner, count(*) from my_all_objects group by owner;

SQL>CREATE MATERIALIZED VIEW MV_MY_ALL_OBJECTS USING INDEX REFRESH FORCE ON COMMIT WITH PRIMARY KEY
enable query rewrite as
select owner,count(*) from my_all_objects group by owner;

SQL>select owner, count(*) from my_all_objects group by owner;

SQL>insert into my_all_objects(owner,object_name,object_type,object_id)
values('New Owner','New Name','New Type',1000000);

SQL>commit;

SQL>select owner, count(*) from my_all_objects where owner='New Owner' group by owner;

SQL>drop materialized view MV_MY_ALL_OBJECTS;

SQL>select owner, count(*) from my_all_objects group by owner;