分类： Oracle
2013-07-01 16:23:54
本文继续讨论物化视图关于查询重写的限制。
本文介绍的是一般意义上的限制，对于具体某个SQL能否使用查询重写，可以使用DBMS_MVIEW.EXPLAIN_REWRITE过程。
物化视图的查询首先功能的限制来自两个方面，对物化视图的限制和对查询重写的限制。
查询重写的限制包括：
1.如果物化视图包括本地表和远端表，则只有本地表支持查询重写；
2.基表和物化视图都不能处于SYS用户下；
3.物化视图的查询中如果包含SELECT和GROUP BY列表，则二者必须保持一致；
4.聚集函数必须在表达式的最外层；
5.不允许CONNECT BY语句。
1.如果物化视图包括本地表和远端表，则只有本地表支持查询重写；
SQL> conn yangtk/yangtk@yangtk
已连接。
SQL> create table dim_b (id number primary key, name varchar2(30));
表已创建。
SQL> insert into dim_b select rownum, 'b'||rownum from user_objects;
已创建60行。
SQL> commit;
提交完成。
SQL> conn yangtk/yangtk@test4
已连接。
SQL> create database link yangtk using 'yangtk';
数据库链接已创建。
SQL> select count(*) from dim_b@yangtk;
COUNT(*)
----------
60
SQL> create materialized view mv_fact enable query rewrite as
2 select a.name aname, b.name bname, f.num
3 from dim_a a, dim_b@yangtk b, fact f
4 where a.id = f.aid
5 and b.id = f.bid;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_FACT');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - --------------------------
REWRITE N
REWRITE_FULL_TEXT_MATCH N 实体化视图引用了 FROM 列表中的远程表或视图
REWRITE_PARTIAL_TEXT_MATCH N 实体化视图无法支持任何类型的查询重写
REWRITE_GENERAL N 实体化视图无法支持任何类型的查询重写
优化器无法使用远端数据库中的物化视图，如果在本地建立远端表的物化视图，则Oracle无法确定物化视图中的数据是否和基表中的数据同步，因此包含远端表的物化视图也不支持查询重写。
因此如果一个查询中包含了远端表和本地表，则只有对本地表的访问会被优化器考虑是否使用查询重写，远端表部分不会利用查询重写的功能。
2.基表和物化视图都不能处于SYS用户下；
SQL> conn /@test4 as sysdba
已连接。
SQL> create materialized view mv_fact enable query rewrite as
2 select a.name, b.num from yangtk.dim_a a, yangtk.fact b
3 where a.id = b.aid;
select a.name, b.num from yangtk.dim_a a, yangtk.fact b
*
ERROR 位于第 2 行:
ORA-30359: SYS 具体化视图不支持查询重写
SQL> create table dim_a (id number primary key, name varchar2(30));
表已创建。
SQL> grant select on dim_a to yangtk;
授权成功。
SQL> conn yangtk/yangtk@test4
已连接。
SQL> create materialized view mv_fact enable query rewrite as
2 select a.name, b.num from sys.dim_a a, yangtk.fact b
3 where a.id = b.aid;
where a.id = b.aid
*
ERROR 位于第 3 行:
ORA-01031: 权限不足
SQL> conn /@test4 as sysdba
已连接。
SQL> create materialized view yangtk.mv_fact enable query rewrite as
2 select a.name, b.num from sys.dim_a a, yangtk.fact b
3 where a.id = b.aid;
where a.id = b.aid
*
ERROR 位于第 3 行:
ORA-30354: SYS 关系中不允许查询重写
根据简单的测试可以发现，基表和物化视图都不能包括在SYS用户中。
3.物化视图的查询中如果包含SELECT和GROUP BY列表，则二者必须保持一致；
SQL> create materialized view mv_dim_b enable query rewrite as
2 select count(*) from dim_b
3 group by name;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_DIM_B');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - --------------------------------
REWRITE Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH N 在 SELECT 列表中忽略了分组列
REWRITE_GENERAL N 在 SELECT 列表中忽略了分组列
如果GROUP BY列表和SELECT列表不一致，则只支持全文匹配的查询重写。
4.聚集函数必须在表达式的最外层；
SQL> create materialized view mv_fact enable query rewrite as
2 select avg(avg(num)) from dim_a a, fact b
3 where a.id = b.aid
4 group by name;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_FACT');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - --------------------------------
REWRITE Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH N 在 SELECT 列表中忽略了分组列
REWRITE_GENERAL N 表达式中聚集函数嵌套
REWRITE_GENERAL N 在 SELECT 列表中忽略了分组列
5.不允许CONNECT BY语句。
SQL> create table test_connect (id number, father_id number, name varchar2(30));
表已创建。
SQL> create materialized view mv_test_connect enable query rewrite as
2 select id from test_connect
3 start with id = 1
4 connect by prior id = father_id;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_TEST_CONNECT');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - -----------------------------
REWRITE Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL N 存在一个 CONNECT BY 子句
