Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1031352
  • 博文数量: 159
  • 博客积分: 3063
  • 博客等级: 中校
  • 技术积分: 2703
  • 用 户 组: 普通用户
  • 注册时间: 2012-04-01 01:51
文章分类

全部博文(159)

文章存档

2013年(48)

2012年(111)

分类: 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 子句

阅读(1262) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~