通过dbms_mviewdbms_mview.explain_rewrite检测为什么不使用查询重写的问题

先创建一个物化视图使用最简单的语法来创建

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
***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212; ***212;***212;***212;***211; ***212;***212;***212;***212;***212;***211;
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, 采用文本匹配算法进行了查询重写
/

		

13 thoughts on “通过dbms_mviewdbms_mview.explain_rewrite检测为什么不使用查询重写的问题

  1. Thanks for finally writing about >通过dbms_mviewdbms_mview.explain_rewrite检测为什么不使用查询重写的问题 | 但行好事
    莫问前程 <Liked it!

  2. I feel this is among the such a lot vital info for me. And i am glad reading your article. But want to commentary on few common things, The site taste is ideal, the articles is in reality excellent : D. Good job, cheers WOW d***217;or

  3. I was wondering if you ever considered changing the layout of your blog? Its very well written; I love what youve got to say. But maybe you could a little more in the way of content so people could connect with it better. Youve got an awful lot of text for only having one or two images. Maybe you could space it out better?

  4. I have been browsing on-line more than 3 hours these days, yet I by no means found any interesting article like yours. It is beautiful value enough for me. In my opinion, if all webmasters and bloggers made just right content material as you did, the internet might be a lot more useful than ever before. ***220;Now I see the secret of the making of the best persons.***221; by Walt Whitman. swtor Smuggler power leveling

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.