oracle之查询重写

淡淡的烟草味﹌ 2022-10-01 01:53 36阅读 0赞

查询重写是优化器的一个机制,优化器在解析的时候会对你的sql进行改写,改写成oracle认为效率更高的语句,改写可能是基于成本的或者是基于规则的,查询重写一般包括是否重写物化视图,view merge,子查询转成表连接等等。

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

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

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

物化视图查询重写的限制（二）：[http://blog.itpub.net/post/468/18765][http_blog.itpub.net_post_468_18765]

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

物化视图的限制：

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)

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

[http_blog.itpub.net_post_468_18765]: http://blog.itpub.net/post/468/18765