有时候我们在使用MV进行查询重写时，会发现某条SQL应该使用MV重写，但是却没有重写。没有进行查询重写的原因很多，要找出其中的原因，还是要花一些功夫的。为此，oracle提供了dbms_mview.explain_rewrite过程来帮助我们确定为何一条SQL语句没有进行查询重写。
先创建一个MV。 SQL> CREATE MATERIALIZED VIEW cust_mth_sales_mv BUILD IMMEDIATE 2 REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS 3 SELECT s.time_id, s.prod_id, SUM(s.quantity_sold), SUM(s.amount_sold), 4 p.prod_name, t.month, COUNT(*), 5 COUNT(s.quantity_sold), COUNT(s.amount_sold) 6 FROM sales_mine s, products p, time t 7 WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id 8 GROUP BY t.month, s.prod_id, p.prod_name, s.time_id; 实体化视图已创建。 SQL> exec dbms_stats.gather_table_stats(user,'cust_mth_sales_mv'); PL/SQL 过程已成功完成。 执行下面的SQL来确定其执行计划 SQL> set autotrace traceonly exp; SQL> SELECT t.month, SUM(s.quantity_sold), SUM(s.amount_sold),COUNT(*),COUNT(s.quantity_sold), COUNT(s.amount_sold) FROM sales_mine s, time t WHERE s.time_id = t.time_id GROUP BY t.month; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=656 Card=60 Bytes=1860) 1 0 HASH (GROUP BY) (Cost=656 Card=60 Bytes=1860) 2 1 HASH JOIN (Cost=597 Card=478278 Bytes=14826618) 3 2 TABLE ACCESS (FULL) OF 'TIME' (TABLE) (Cost=4 Card=1826 Bytes=27390) 4 2 PARTITION RANGE (ALL) (Cost=586 Card=478278 Bytes=7652448) 5 4 TABLE ACCESS (FULL) OF 'SALES_MINE' (TABLE) (Cost=586 Card=478278 Bytes=7652448) 显然，没有进行查询重写。来看一下为何没有进行查询重写。 SQL> set autotrace off; SQL> DECLARE 2 qrytext VARCHAR2(2000) := 'SELECT t.month, SUM(s.quantity_sold), SUM(s.amount_sold),COUNT(*),COUNT(s.quantity_sold ), COUNT(s.amount_sold) FROM sales_mine s, time t WHERE s.time_id = t.time_id GROUP BY t.month'; 3 BEGIN 4 dbms_mview.explain_rewrite(qrytext,'CUST_MTH_SALES_MV','124'); 5 END; 6 / PL/SQL 过程已成功完成。 SQL> SELECT message FROM rewrite_table WHERE statement_id = '124' ORDER BY sequence; MESSAGE -------------------------------------------------------------------------------- QSM-01150: 未重写查询 QSM-01110: 由于实体化视图 CUST_MTH_SALES_MV 包含查询中不存在的表 (SALES_MINE and PRODUCTS) 之间的联接, 这可能会清除查询所需的行, 因此无法对该视图执行查询重写 这里说明sales_mine和products之间没有外键约束，所以添加外键，同时检查products的主键是否为novalidate，如果是的话，还要改成validate。 SQL> alter table sales_mine add CONSTRAINT SALES_MINE_PRODUCTS_FK1 FOREIGN KEY (PROD_ID) REFERENCES PRODUCTS (PROD_ID); 表已更改。 SQL> alter table products enable validate constraint products_pk; 表已更改。 SQL> truncate table rewrite_table; 表已截掉。 SQL> DECLARE 2 qrytext VARCHAR2(2000) := 'SELECT t.month, SUM(s.quantity_sold), SUM(s.amount_sold),COUNT(*),COUNT(s.quantity_sold ), COUNT(s.amount_sold) FROM sales_mine s, time t WHERE s.time_id = t.time_id GROUP BY t.month'; 3 BEGIN 4 dbms_mview.explain_rewrite(qrytext,'CUST_MTH_SALES_MV','124'); 5 END; 6 / PL/SQL 过程已成功完成。 SQL> SELECT message FROM rewrite_table WHERE statement_id = '124' ORDER BY sequence; MESSAGE -------------------------------------------------------------------------------- QSM-01151: 已重写查询 QSM-01033: 已用实体化视图 CUST_MTH_SALES_MV 进行重写查询 说明进行了查询重写。我们也可以执行一下，查看执行计划是否走了MV。 SQL> set autotrace traceonly exp; SQL> SELECT t.month, SUM(s.quantity_sold), SUM(s.amount_sold),COUNT(*),COUNT(s.quantity_sold), COUNT(s.amount_sold) FROM sales_mine s, time t WHERE s.time_id = t.time_id GROUP BY t.month; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=42 Card=24 Bytes=672) 1 0 HASH (GROUP BY) (Cost=42 Card=24 Bytes=672) 2 1 MAT_VIEW REWRITE ACCESS (FULL) OF 'CUST_MTH_SALES_MV' (MAT_VIEW REWRITE) (Cost=40 Card=17134 Bytes=479752)

标题

在此添加评论
表情符号:

称呼

邮箱地址（可选）

个人主页（可选）

记住我？