使用 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