通过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;
/

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’or

   回复

3. Very nice post. I just stumbled upon your blog and wanted to mention that I’ve really loved surfing around your blog posts. After all I抣l be subscribing for your rss feed and I hope you write again soon! swtor Fleet Commendation

   回复

4. 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?

   回复

5. Hello there, You’ve done an excellent job. I抣l definitely digg it and personally recommend to my friends. I’m confident they’ll be benefited from this website. LOTRO Gold

   回复

6. Normally I do not read post on blogs, but I wish to say that this write-up very forced me to try and do it! Your writing style has been surprised me. Thanks, very nice post. AION Kinah

   回复

7. Hello.This article was extremely fascinating, especially since I was investigating for thoughts on this issue last Tuesday. TERA d’or

   回复

8. You made some good points there. I did a search on the subject and found most people will consent with your website. Diablo 3 d’or

   回复

9. I do not even know how I ended up here, but I thought this post was great. I do not know who you are but definitely you’re going to a famous blogger if you aren’t already 😉 Cheers! BNS d’Or/BNS Gold

   回复

10. 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. “Now I see the secret of the making of the best persons.” by Walt Whitman. swtor Smuggler power leveling

    回复

11. Youre so cool! I dont suppose Ive read something like this before. So nice to find anyone with some original ideas on this subject. realy thanks for beginning this up. this website is one thing that’s needed on the internet, somebody with a bit originality. useful job for bringing something new to the web! swtor valor power leveling

    回复

12. My spouse and I stumbled over here from a different web address and thought I might check things out. I like what I see so now i’m following you. Look forward to looking at your web page again. best swtor power leveling service

    回复

13. I gotta bookmark this site it seems handy very beneficial swtor Artifice power leveling

    回复