标题: 使用 DBMS_MVIEW.EXPLAIN_REWRITE过程 诊断QUERY REWRITE 的原因 [打印本页] 作者: 郑全 时间: 2017-1-5 12:49 标题: 使用 DBMS_MVIEW.EXPLAIN_REWRITE过程 诊断QUERY REWRITE 的原因 查找无法query rewrite的原因
1 执行utlxrw.sql，创建REWRITE_TABLE
@?/rdbms/admin/utlxrw.sql
2.创建一个物化视图
CREATE MATERIALIZED VIEW cust_sales_mv
--ENABLE QUERY REWRITE
AS
SELECT c.cust_id, SUM(amount_sold)
FROM sales s, customers c
WHERE s.cust_id = c.cust_id
GROUP BY c.cust_id;
3 执行存储过程，提供一条SQL，看看为什么不能query rewrite
begin
DBMS_MVIEW.EXPLAIN_REWRITE (
query => 'SELECT c.cust_id, SUM(amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_id',
mv => 'SH.CUST_SALES_MV',
statement_id => '');
end;
SQL> select MESSAGE from rewrite_table;
MESSAGE
------------------------------------------------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01052: referential integrity constraint on table, CUSTOMERS, not VALID in ENFORCED integrity mode
QSM-01026: query rewrite is disabled for, CUST_SALES_MV
这个是期望行为
4.修改物化视图为可以重写
alter MATERIALIZED VIEW cust_sales_mv ENABLE QUERY REWRITE ;
5.重新去执行
truncate table rewrite_table;
begin
DBMS_MVIEW.EXPLAIN_REWRITE (
query => 'SELECT c.cust_id, SUM(amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_id',
mv => 'SH.CUST_SALES_MV',
statement_id => '');
end;
SQL> select MESSAGE from rewrite_table;
MESSAGE
------------------------------------------------------------------------------------------------------------------------
QSM-01151: query was rewritten
QSM-01209: query rewritten with materialized view, CUST_SALES_MV, using text match algorithm