ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 通过dbms_mviewdbms_mview.explain_rewrite检测为什么不使用查询重写的问题

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

原创 Linux操作系统 作者:eric0435 时间:2013-07-03 14:36:28 0 删除 编辑

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

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 系统架构师

注册时间:2011-10-12

  • 博文量
    560
  • 访问量
    6231346