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

全部博文(159)

文章存档

2013年(48)

2012年(111)

分类: Oracle

2013-07-01 16:23:04

物化视图的一个重要的特性就是支持查询重写。


如果初始化参数query_rewrite_enabled设置为TRUE,且使用CBO时,当发出对基表的查询,Oracle会自动判断是否能利用这个基表的所有指定了ENABLE QUERY REWRITE语句的物化视图,如果可以且根据统计信息判断通过查询物化视图代价更小,则Oracle自动重写查询语句,通过查询物化视图得到正确的结果。


不过不是所有的物化视图都支持查询重写的,查询重写有一些限制条件,本文首先讨论查询重写对物化视图的限制。


物化视图的查询重写功能的限制来自两个方面,对物化视图的限制和对查询重写的限制。


物化视图的限制:


1.物化视图中不能包括ROWNUM、SYSDATE等不可重复的表达式;


2.物化视图不能包括LONG或LONG RAW或对象参考列;


3.如果物化视图的查询定义中包括集合操作(如minus、union等),则物化视图只支持全文匹配的查询重写;


4.如果以PREBUILD方式建立物化视图,除非指定了WITH REDUCED PRECISION语句,否则列的精度必须满足SELECT表达式的精度;


5.如果物化视图包含一个表的次数多于一次,那么,这个物化视图支持一般类型的查询重写,要求查询中的表的别名必须和物化视图中定义的一致。


 


首先建立测试环境:


SQL> create table dim_a (id number primary key, name varchar2(30));


表已创建。


SQL> create table dim_b (id number primary key, name varchar2(30));


表已创建。


SQL> create table fact (id number, aid number, bid number, num number);


表已创建。


SQL> alter table fact add constraint fk_fact_aid foreign key (aid) references dim_a(id);


表已更改。


SQL> alter table fact add constraint fk_fact_bid foreign key (bid) references dim_b(id);


表已更改。


SQL> insert into dim_a select rownum, 'a'||rownum from user_objects;


已创建56行。


SQL> insert into dim_b select rownum, 'b'||rownum from user_objects;


已创建56行。


SQL> insert into fact select rownum, mod(rownum, 6) + 1, mod(rownum, 5 ) + 1, rownum *2 
  2  from user_objects;


已创建56行。


SQL> commit;


提交完成。


建立MV_CAPABILITIES_TABLE表:


SQL> @?/rdbms/admin/utlxmv.sql


表已创建。


环境:


SQL> show parameter query_rewrite


NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced


物化视图的限制:


1.物化视图中不能包括ROWNUM、SYSDATE等不可重复的表达式;


SQL> create materialized view mv_fact enable query rewrite as 
  2  select rownum id, a.name, b.num from dim_a a, fact b
  3  where a.id = b.aid;
where a.id = b.aid
             *
ERROR 位于第 3 行:
ORA-30353: 表达式对查询重写不支持


SQL> begin
  2  dbms_mview.explain_mview('select rownum id, a.name, b.num from dim_a a, fact b
  3  where a.id = b.aid');
  4  end;
  5  /


PL/SQL 过程已成功完成。


SQL> col msgtxt format a40
SQL> col related_text format a30
SQL> select capability_name, possible, msgtxt, related_text 
  2  from mv_capabilities_table 
  3  where capability_name like '%REWRITE%'
  4  and capability_name not like '%PCT%';


CAPABILITY_NAME                P MSGTXT                                   RELATED_TEXT
------------------------------ - ---------------------------------------- --------------
REWRITE                        N
REWRITE_FULL_TEXT_MATCH        N Oracle 错误: 有关详细信息, 请参阅 RELATE 表达式对查询重写不支持
                                 D_NUM 和 RELATED_TEXT


REWRITE_PARTIAL_TEXT_MATCH     N 实体化视图无法支持任何类型的查询重写
REWRITE_GENERAL                N 实体化视图无法支持任何类型的查询重写


 


2.物化视图不能包括LONG或LONG RAW或对象参考列;


SQL> alter table dim_b add (col_long long);


表已更改。


SQL> create materialized view mv_fact as 
  2  select b.col_long, a.num from dim_b b, fact a
  3  where b.id = a.bid;
select b.col_long, a.num from dim_b b, fact a
                              *
ERROR 位于第 2 行:
ORA-00997: 非法使用 LONG 数据类型


SQL> alter table dim_b drop (col_long);


表已更改。


 


3.如果物化视图的查询定义中包括集合操作(如minus、union等),则物化视图只支持全文匹配的查询重写;


SQL> create materialized view mv_fact enable query rewrite as 
  2  select a.name, b.num from dim_a a, fact b
  3  where a.id = b.aid
  4  union
  5  select a.name, b.num from dim_b a, fact b
  6  where a.id = b.bid
  7  ;


实体化视图已创建。


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 设置在实体化视图中遇到的运算符
REWRITE_GENERAL                N 设置在实体化视图中遇到的运算符


根据MV_CAPABILITIES_TABLE中的信息可以看出,物化视图MV_FACT只支持全文匹配的查询。


 


 


4.如果以PREBUILD方式建立物化视图,除非指定了WITH REDUCED PRECISION语句,否则列的精度必须满足SELECT表达式的精度;


SQL> drop materialized view mv_fact;


实体化视图已删除。


SQL> create table mv_fact (name varchar2(20), num number);


表已创建。


SQL> create table mv_fact1 (name varchar2(20), num number);


表已创建。


SQL> create materialized view mv_fact on prebuilt table enable query rewrite as 
  2  select a.name, b.num from dim_a a, fact b
  3  where a.id = b.aid;
select a.name, b.num from dim_a a, fact b
       *
ERROR 位于第 2 行:
ORA-12060: 预建表的形式与定义查询不匹配



SQL> create materialized view mv_fact1 on prebuilt table with reduced precision 
  2  enable query rewrite as 
  3  select a.name, b.num from dim_a a, fact b
  4  where a.id = b.aid;


实体化视图已创建。


 


5.如果物化视图包含一个表的次数多于一次,那么,这个物化视图支持一般类型的查询重写,要求查询中的表的别名必须和物化视图中定义的一致。


SQL> create materialized view mv_fact1 on prebuilt table with reduced precision 
  2  enable query rewrite as 
  3  select a.name, b.num from dim_a a, fact b
  4  where a.id = b.aid;


实体化视图已创建。


SQL> 
SQL> 
SQL> create materialized view mv_dim_a enable query rewrite 
  2  as select a.id, b.name from dim_a a, dim_a b
  3  where a.id = b.id;


实体化视图已创建。


SQL> truncate table mv_capabilities_table;


表已截掉。


SQL> begin
  2  dbms_mview.explain_mview('MV_DIM_A');
  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 相同的表或视图的多个例程
REWRITE_GENERAL                Y


SQL> set autot on exp
SQL> select a.id, b.name from dim_a a, dim_a b
  2  where a.id = b.id;


        ID NAME
---------- ------------------------------
         1 a1
         2 a2
         3 a3
         . 
         . 
         . 
        56 a56


已选择56行。



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'DIM_A'
   3    1     INDEX (UNIQUE SCAN) OF 'SYS_C001917' (UNIQUE)


 


由于没有统计信息采用了RBO,下面对表进行分析。


SQL> exec dbms_stats.gather_table_stats(user, 'DIM_A');


PL/SQL 过程已成功完成。


SQL> select a.id, b.name from dim_a a, dim_a b
  2  where a.id = b.id;


        ID NAME
---------- ------------------------------
         1 a1
         2 a2
         3 a3
         . 
         . 
         . 
        56 a56


已选择56行。



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=9810)
   1    0   TABLE ACCESS (FULL) OF 'MV_DIM_A' (Cost=2 Card=327 Bytes=9810)


 



SQL> select a.id, c.name from dim_a a, dim_a c
  2  where a.id = c.id;


        ID NAME
---------- ------------------------------
         1 a1
         2 a2
         3 a3
         . 
         . 
         . 
        56 a56


已选择56行。



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=56 Bytes=504)
   1    0   NESTED LOOPS (Cost=2 Card=56 Bytes=504)
   2    1     TABLE ACCESS (FULL) OF 'DIM_A' (Cost=2 Card=56 Bytes=336) 
   3    1     INDEX (UNIQUE SCAN) OF 'SYS_C001917' (UNIQUE)


可以看出,当物化视图中包含一个表多次时,查询重写的要求是查询语句中的别名必须和物化视图定义中的一致。

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