通過dbms_mviewdbms_mview.explain_rewrite檢測為什麼不使用查詢重寫的問題

eric0435發表於2013-07-03
ViewAI

先建立一個物化檢視使用最簡單的語法來建立

create materialized view  sales_customers_products as
SELECT p.prod_category, c.country_id,
sum(s.quantity_sold) AS quantity_sold,
sum(s.amount_sold) AS amount_sold
FROM sh.sales s, sh.customers c, sh.products p
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
and c.country_id='Ruddy'
GROUP BY p.prod_category, c.country_id
ORDER BY p.prod_category, c.country_id;
/

建立rewrite_table表
SQL> @d:/oracle/product/10.2.0/db_1/rdbms/admin/utlxrw.sql

Table created

檢查查詢重寫的引數設定
SQL> show parameter query

NAME TYPE VALUE
------------------------------------ ----------- -----------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced

SQL> DECLARE
  2   l_query CLOB := 'SELECT p.prod_category, c.country_id,
  3  sum(s.quantity_sold) AS quantity_sold,
  4  sum(s.amount_sold) AS amount_sold
  5  FROM sh.sales s, sh.customers c, sh.products p
  6  WHERE s.cust_id = c.cust_id
  7  AND s.prod_id = p.prod_id
  8  GROUP BY p.prod_category, c.country_id
  9  ORDER BY p.prod_category, c.country_id';
 10   BEGIN
 11   dbms_mview.explain_rewrite(
 12   query => l_query,
 13   mv => 'sales_customers_products',
 14   statement_id => '42'
 15   );
 16  END;
 17  /

PL/SQL procedure successfully completed;

SQL> select message from rewrite_table;

MESSAGE
-------------------------------------------------------------------
QSM-01150: 未重寫查詢
QSM-01052: 表的引用完整性約束條件 PRODUCTS 在 ENFORCED 完整性模式中無效
QSM-01026: 對 SALES_CUSTOMERS_PRODUCTS 禁用查詢重寫


/
SQL>drop materialized view  sales_customers_products ;

下面在建立物化檢視時啟用查詢重寫

create materialized view  sales_customers_products
ENABLE QUERY REWRITE
as
SELECT p.prod_category, c.country_id,
sum(s.quantity_sold) AS quantity_sold,
sum(s.amount_sold) AS amount_sold
FROM sh.sales s, sh.customers c, sh.products p
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
GROUP BY p.prod_category, c.country_id
ORDER BY p.prod_category, c.country_id;


SQL> DECLARE
  2   l_query CLOB := 'SELECT p.prod_category, c.country_id,
  3  sum(s.quantity_sold) AS quantity_sold,
  4  sum(s.amount_sold) AS amount_sold
  5  FROM sh.sales s, sh.customers c, sh.products p
  6  WHERE s.cust_id = c.cust_id
  7  AND s.prod_id = p.prod_id
  8  GROUP BY p.prod_category, c.country_id
  9  ORDER BY p.prod_category, c.country_id';
 10   BEGIN
 11   dbms_mview.explain_rewrite(
 12   query => l_query,
 13   mv => 'sales_customers_products',
 14   statement_id => '43'
 15   );
 16  END;
 17  /

PL/SQL procedure successfully completed

SQL>select message from rewrite_table where statement_id='43';

MESSAGE
-------------------------------------------------------------------
QSM-01151: 已重寫查詢
QSM-01209: 已通過實體化檢視 SALES_CUSTOMERS_PRODUCTS, 採用文字匹配演算法進行了查詢重寫
/
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-765390/，如需轉載，請註明出處，否則將追究法律責任。

相關文章