2010年11月16日 11:25:37
物化视图
create materialized view m_test_view
refresh forceBUILD IMMEDIATE --创建物化视图的时候就生成数据（默认就是BUILD IMMEDIATE）
--(BUILD DEFERRED) 则在创建时不生成数据，以后根据需要在生成数据
on commit --视图数据基于基表提交时更新
--(on demand) 根据自己需要刷新数据（可以根据刷新包手动刷新）
as
select * from gs_gcomm_table;
--删除物化视图
drop materialized view M_TEST_VIEW;
analyze table m_test_view compute statistics;
--查看物化视图的表
select tl.table_name, tl.num_rows from user_tables tl where tl.table_name in ( 'GS_GCOMM_TABLE', 'M_TEST_VIEW' );
--查看物化视图的段
select sg.segment_name, sg.bytes, sg.blocks from user_segments sg where sg.segment_name = 'M_TEST_VIEW';
--查看物化视图
select mv.* from user_mviews mv where mv.MVIEW_NAME = 'M_TEST_VIEW';
--手动刷新
exec dbms_mview.refresh('M_TEST_VIEW');
select * from M_TEST_VIEW
物化视图创建的几个注意关键词
1、物化视图的种类
1）主键物化视图
create materialized view m_test_view
refresh fast start with sysdate
next sysdate+2
with primary key
as
select * from gs_gcomm_table;
--当用fast创建物化视图时，必须创建物化视图日志
create materialized view log on gs_gcomm_table;
2）rowid物化视图
create materialized view m_test_view
refresh with rowid
as
select * from gs_gcomm_table;
--子查询物化视图
create materialized view m_test_view
as
select * from gwm_tabattribute a where exists (select * from gwm_attribute b where a.gwm_ano=b.gwm_ano);
--refresh方法：
1）fast 增量刷新物化日志来发送主表增量发生的变化
2）complete 完全刷新
3）force 不写的话就默认为force 即oracle增量刷新可用就用增量刷新，否则就用全量刷新
--主键和rowid
默认情况下创建的是主键视图（主表必须有主键，否则应该用rowid视图）
--刷新时间
指定刷新的方法与时间
create materialized view m_test_view
refresh fast start with sysdate --在创建时刷新
next sysdate+2 --每两天刷新一次
with primary key
as
select * from gs_gcomm_table;
2011-12-4
最近由于碰到了数据库中表的数据量比较大，而又需要对这些大表进行关联查询，不管语句如何调整，查询的速度总是不尽人意，后来想到了这个物化视图，速度果然提高了n倍。因此对于物化视图的一些感觉记录下来。
现在网上很容易找到很多关于物化视图的文章，因此具体我也不再多说，具体可参考
http://blog.itpub.net/post/468/14245这个博客，这个博主对物化视图写的很详细了。
说明一点就是由于该博客中的博文写时是使用oracle9i，因此一些限制到了10g或11g就不对了。
比如：关于 只有关联的物化视图的快速刷新限制条件中的“如果在WHERE语句中包含外连接，那么唯一约束必须存在于连接中内表的连接列上” 这个限制条件，我在11g上实验已经没有这个限制了。（10g的未测试）
快速刷新(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;
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;
