ORACLE物化视图

时间:2016-06-10 00:17 来源:linux.it.net.cn 作者:IT

ORACLE物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。
         当数据库中表的数据量比较大，而又需要对这些大表进行关联查询，不管语句如何调整，查询的速度总是不尽人意，使用物化视图，速度可以提高n倍。

1.下面的语法在远程数据库表emp注意：当用FAST如下:
[sql] view plain copy
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
     Materialized view log created.

2.Rowid下面的语法在远程数据库表emp物化视图
[sql] view plain copy
SQL> CREATE MATERIALIZED VIEW mv_emp_rowid
            REFRESH WITH ROWID
            AS SELECT * FROM emp@remote_db;

3.下面的语法在远程数据库表emp和dept表的子查询物化视图
[sql] view plain copy
SQL> CREATE MATERIALIZED VIEW mv_empdept
            AS SELECT * FROM emp@remote_db e
            WHERE EXISTS
            (SELECT * FROM dept@remote_db d WHERE e.dept_no = d.dept_no)

REFRESH 子句
[sql] view plain copy
[refresh [fast|complete|force]
[on demand | commit]
[start with date] [next date]
[with {primary key|rowid}]]

[sql] view plain copy
      CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE   --创建时刷新
NEXT SYSDATE + 2                 --以后每2天刷新一次
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;

--refresh方法：
1）fast 增量刷新物化日志来发送主表增量发生的变化。增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。对主表创建物化视图日志
[sql] view plain copy
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
2）complete 完全刷新。如果请求完全刷新，oracle会完成自动完全刷新即使增量刷新可用。
3）force 默认为force，即oracle增量刷新可用就用增量刷新，否则就用全量刷新

--刷新方式
   on commit   视图数据基于基表提交时更新
   on demand 根据自己需要刷新数据（可以根据刷新包手动刷新）

--刷新时间
start with子句通知数据库完成从主表到本地表第一次复制的时间,应该及时估计下一次运行的时间点, NEXT 子句说明了刷新的间隔时间.

--主键和rowid
默认情况下创建的是主键视图（主表必须有主键，否则应该用rowid视图），主键物化视图允许识别物化视图主表而不影响物化视图增量刷新的可用性。
       Rowid物化视图只有一个单一的主表，不能包括下面任何一项:
        1. Distinct 或者聚合函数.
        2. Group by，子查询，连接和SET操作

总结
物化视图提供了可伸缩的基于主键或ROWID的视图,指定了刷新方法和自动刷新的时间。

--删除物化视图
[sql] view plain copy
drop materialized view M_TEST_VIEW;

analyze table m_test_view compute statistics;

--查看物化视图的表
[sql] view plain copy
select tl.table_name, tl.num_rows from user_tables tl where tl.table_name in ( 'GS_GCOMM_TABLE', 'M_TEST_VIEW' );

--查看物化视图的段
[sql] view plain copy
select sg.segment_name, sg.bytes, sg.blocks from user_segments sg where sg.segment_name = 'M_TEST_VIEW';

--查看物化视图
[sql] view plain copy
select mv.* from user_mviews mv where mv.MVIEW_NAME = 'M_TEST_VIEW';

--手动刷新
[sql] view plain copy
exec dbms_mview.refresh('M_TEST_VIEW');
select * from M_TEST_VIEW

快速刷新(REFRESH FAST ON COMMIT)的基本限制条件：
1）select不能包含rownum，sysdate等这些对于表不能重复的字段
如下如果包含rownum字段则提示提示ora-01747错误。
CREATE MATERIALIZED VIEW v_a REFRESH FAST ON COMMIT AS
SELECT a.rownum,a.id FROM a;
ora-01747: invalid user.table.column, table.column, or column SPECIFICATION
2）不能包含类型为long或long raw类型的字段
CREATE TABLE bb (z_id NUMBER,v_name LONG)
CREATE MATERIALIZED VIEW LOG ON bb WITH ROWID;
CREATE MATERIALIZED VIEW mv_bb REFRESH FAST ON COMMIT WITH ROWID AS
SELECT z_id,v_name FROM bb;
ora-00997：非法使用long数据类型

ALTER TABLE bb MODIFY v_name LONG RAW;
CREATE MATERIALIZED VIEW mv_bb REFRESH FAST ON COMMIT WITH ROWID AS
SELECT z_id,v_name FROM bb;
ora-00997：非法使用long数据类型

以下说明的物化视图都是 REFRESH FAST ON COMMIT方式的物化视图：
创建物化视图时，如果用于创建物化视图的基表只有一个且有主键：
1）创建的视图日志若是基于rowid，则创建的视图也必须是基于rowid，若是创建的物化视图基于主键，则创建时提示：
    ora-23415: "FYZH_ORA"."F_RACK_INFO" 的实体化视图日志不记录主键
2）创建的视图日志若是基于主键的（PRIMARY key），默认即为主键，则创建的物化视图也应该基于主键的，若创建的物化视图
    是基于rowid的，则提示：ora-12032: 不能使用 "FYZH_ORA"."F_RACK_INFO" 上实体化视图日志中的 rowid 列；
3）若创建的物化视图是基于主键的，则select中必须包含主键的列
    否则提示：ORA-12016:实体化视图并未包含所有主键列
创建物化视图时，如果用于创建物化视图的基表只有一个且无主键：
1）创建物化视图日志时，必须基于rowid形式，如果是基于主键形式，则提示：
    ora-12014：表‘f_rack_s’不包含主键约束条件
2）创建物化视图时，必须基于rowid形式，如果是基于主键形式，则提示：
    ora-12014：表‘f_rack_s’不包含主键约束条件
创建物化视图如果是基于多个表的：
1）不管这些表是否有主键，创建的视图日志必须基于rowid
    而且通过dbms_mview.explain_mview查看可以看到很详细的提示，指出哪些表的视图日志必须具有rowid
   （ TRUNCATE TABLE mv_capabilities_table;
   BEGIN
   dbms_mview.explain_mview('SELECT odf.rowid odf_rowid,room.rowid room_rowid, odf.rack_name,room.china_name FROM rm_frame_info odf,rm_room_info room WHERE odf.room_id=room.room_id');
   END;

   SELECT capability_name, related_text, msgtxt
     FROM mv_capabilities_table
    WHERE possible = 'N'
      AND capability_name NOT LIKE '%PCT%';）
    2    REFRESH_FAST_AFTER_INSERT    FYZH_ORA.RM_ROOM_INFO    实体化视图日志必须具有 ROWID
    3    REFRESH_FAST_AFTER_INSERT    FYZH_ORA.RM_FRAME_INFO    实体化视图日志必须具有 ROWID
2）from后的所有表rowid必须出现在select中，且rowid要用字段别名表示，否则无法支持快速刷新
SELECT a.rowid a_rowid, --a和b表的rowid都要出现在select中，且分别用别名a_rowid，b_rowid表示
        b.rowid b_rowid
FROM a, b WHERE a.id = b.id;

(责任编辑：IT)