含子查询的物化视图的QUERY REWRITE问题

显示全部楼层 · 2005-10-30 17:05:33

物化视图的测试例子如下：
SQL> Create Table test1 (a Number,b Number);
表已创建。
SQL> Insert Into test1 Values (1,1);
已创建 1 行。
SQL> Insert Into test1 Values (1,2);
已创建 1 行。
SQL> Insert Into test1 Values (1,3);
已创建 1 行。
SQL> Insert Into test1 Values (1,4);
已创建 1 行。
SQL> Insert Into test1 Values (2,1);
已创建 1 行。
SQL> Insert Into test1 Values (2,2);
已创建 1 行。
SQL> Insert Into test1 Values (2,3);
已创建 1 行。
SQL> Insert Into test1 Values (2,4);
已创建 1 行。
SQL> Commit;
提交完成。
SQL> Create Materialized View test1_mv
2Build Immediate
3Enable Query Rewrite
4As
5Select * From test1 x Where b=(Select Max(b) From test1 y Where x.a=y.a);
实体化视图已创建。
SQL> Commit;
提交完成。
SQL> Alter Session Set query_rewrite_enabled=True;
会话已更改。
SQL> analyze table test1 compute statistics
2for table
3 for all indexes
4 for all indexed columns;
表已分析。
SQL> set autotrace traceonly;
SQL>Select * From test1 x Where b=(Select Max(b) From test1 y Where x.a=y.a);

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=2132

)
10 TABLE ACCESS (FULL) OF 'TEST1_MV' (Cost=1 Card=82 Bytes=21

32)

SQL>Select * From test1 x Where b=(Select Max(b) From test1 y Where x.a=y.a)
2 and a=1;

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
10 FILTER
21 TABLE ACCESS (FULL) OF 'TEST1' (Cost=1 Card=1 Bytes=9)
31 SORT (AGGREGATE)
43 TABLE ACCESS (FULL) OF 'TEST1' (Cost=1 Card=1 Bytes=9)
发现在包含子查询时，我加入其它WHERE条件就无法自动重写QUERY了？
觉得我的例子中的逻辑很简单，为什么ORACLE无法识别重写呢？
单层查询的到是每问题的。
最近看物化视图，还请大侠们指教。

千问 · 2005-10-30 17:05:33

最初由 adamyang 发布
[B]物化视图的测试例子如下：
SQL> Create Table test1 (a Number,b Number);
表已创建。
SQL> Insert Into test1 Values (1,1);
已创建 1 行。
SQL> Insert Into test1 Values (1,2);
已创建 1 行。
SQL> Insert Into test1 Values (1,3);
已创建 1 行。
SQL> Insert Into test1 Values (1,4);
已创建 1 行。
SQL> Insert Into test1 Values (2,1);
已创建 1 行。
SQL> Insert Into test1 Values (2,2);
已创建 1 行。
SQL> Insert Into test1 Values (2,3);
已创建 1 行。
SQL> Insert Into test1 Values (2,4);
已创建 1 行。
SQL> Commit;
提交完成。
SQL> Create Materialized View test1_mv
2Build Immediate
3Enable Query Rewrite
4As
5Select * From test1 x Where b=(Select Max(b) From test1 y Where x.a=y.a);
实体化视图已创建。
SQL> Commit;
提交完成。
SQL> Alter Session Set query_rewrite_enabled=True;
会话已更改。
SQL> analyze table test1 compute statistics
2for table
3 for all indexes
4 for all indexed columns;
表已分析。
SQL> set autotrace traceonly;
SQL>Select * From test1 x Where b=(Select Max(b) From test1 y Where x.a=y.a);

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=2132

)
10 TABLE ACCESS (FULL) OF 'TEST1_MV' (Cost=1 Card=82 Bytes=21

32)

SQL>Select * From test1 x Where b=(Select Max(b) From test1 y Where x.a=y.a)
2 and a=1;

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
10 FILTER
21 TABLE ACCESS (FULL) OF 'TEST1' (Cost=1 Card=1 Bytes=9)
31 SORT (AGGREGATE)
43 TABLE ACCESS (FULL) OF 'TEST1' (Cost=1 Card=1 Bytes=9)
发现在包含子查询时，我加入其它WHERE条件就无法自动重写QUERY了？
觉得我的例子中的逻辑很简单，为什么ORACLE无法识别重写呢？
单层查询的到是每问题的。
最近看物化视图，还请大侠们指教。 [/B]
因为物化视图认为它从MV中拿到的数据与不使用query rewrite拿到
的数据是不同的，所以它不可以使用query rewrite.而且你可以好好
看看expert one on one第13章，人家是如何使用mveiw的。

千问 · 2005-10-30 17:05:33

谢谢大侠提醒。
expert one on one第13章已经仔细看过了。
但是，其实我的两个查询，使用MV或使用query rewrite拿到
的数据是完全相同的。
而且逻辑并不复杂，就是不明白ORACLE为什么分辨不出来??

千问 · 2005-10-30 17:05:33

try this
select * from (select * from test1 x where b = (select max(b) from test1 y where x.a = y.a))
where a = 1;

千问 · 2005-10-30 17:05:33

这样写就可以了
select * from (Select * From test1 x Where b=(Select Max(b) From test1 y Where x.a=y.a))
where a=1

千问 · 2005-10-30 17:05:33

这跟oracle的parse顺序有关系

千问 · 2005-10-30 17:05:33

啊
各个
我可没复制2楼的哦
呵呵
还真是巧诶

千问 · 2005-10-30 17:05:33

谢谢您的回复。
最初由 yangtingkun 发布
[B]try this
select * from (select * from test1 x where b = (select max(b) from test1 y where x.a = y.a))
where a = 1; [/B]
SQL> select * from (select * from test1 x where b = (select max(b) from test1 y
where x.a = y.a))
2where a = 1;

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
10 FILTER
21 TABLE ACCESS (FULL) OF 'TEST1' (Cost=1 Card=1 Bytes=9)
31 SORT (AGGREGATE)
43 TABLE ACCESS (FULL) OF 'TEST1' (Cost=1 Card=1 Bytes=9)

：（
这个我也有试过，还是不行的

千问 · 2005-10-30 17:05:33

你们的意思是让我
这样建MV么？
Create Materialized View test1_mv2
Build Immediate
Enable Query Rewrite
As
select * from (Select * From test1 x Where b=(Select Max(b) From test1 y Where x.a=y.a))

千问 · 2005-10-30 17:05:33

SQL> select * from (select * from test1 x where b = (select max(b) from test1 y where x.a = y.a))
2where a = 1;
A
B
---------- ----------
1
4
已用时间:00: 00: 00.06
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=52)
10 TABLE ACCESS (FULL) OF 'TEST1_MV' (Cost=2 Card=2 Bytes=52)