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

create materialized view  sales_customers_products asSELECT p.prod_category, c.country_id,sum(s.quantity_sold) AS quantity_sold,sum(s.amount_sold) AS amount_soldFROM sh.sales s, sh.customers c, sh.products pWHERE s.cust_id = c.cust_idAND s.prod_id = p.prod_idand c.country_id='Ruddy'GROUP BY p.prod_category, c.country_idORDER 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_productsENABLE QUERY REWRITEasSELECT p.prod_category, c.country_id,sum(s.quantity_sold) AS quantity_sold,sum(s.amount_sold) AS amount_soldFROM sh.sales s, sh.customers c, sh.products pWHERE s.cust_id = c.cust_idAND s.prod_id = p.prod_idGROUP BY p.prod_category, c.country_idORDER 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 completedSQL>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/，如需转载，请注明出处，否则将追究法律责任。