Query rewrite an one of Query Transformations which makes possible to use materialized view to get results instead of querying underlying tables.
Basic prerequisites for query rewrite described in corresponding chapter of a Database Data Warehousing Guide. Let’s start with a simple example to show how it works.
Script creates underlying tables and materialized view which is enabled for query rewrite.
create table fact (product_id, value) as select (mod(level - 1, 5) + 1) r, rownum v from dual connect by level <= 1e6; create table product (id, product, sub_product) as select level, 'product' || (mod(level - 1, 2) + 1) r, 'sub_product' || level sp from dual connect by level <= 5; create materialized view mv enable query rewrite as select product, sub_product, sum(value) value, count(*) cnt from fact f, product p where f.product_id = p.id group by product, sub_product;
Hint REWRITE_OR_ERROR
is used to understand whether query rewrite has happened so we do not need to check query plans.
select --+ rewrite_or_error product, sub_product, sum(value) value, count(*) cnt from fact f join product p on f.product_id = p.id where p.sub_product = 'sub_product2' group by product, sub_product; PRODUCT SUB_PRODUCT VALUE CNT ---------- --------------- ---------- ---------- product2 sub_product2 1.0000E+11 200000
So far so good, Oracle scanned just 5 rows in materialized view instead of million rows in a fact table because query rewrite took place. Let’s try to do same test for materialized view with ANSI join syntax.
create materialized view mv enable query rewrite as select product, sub_product, sum(value) value, count(*) cnt from fact f join product p on f.product_id = p.id group by product, sub_product; Materialized view created. select --+ rewrite_or_error product, sub_product, sum(value) value, count(*) cnt from fact f join product p on f.product_id = p.id where p.sub_product = 'sub_product2' group by product, sub_product; join product p on f.product_id = p.id * ERROR at line 4: ORA-30393: a query block in the statement did not rewrite
Oracle cannot take advantage of materialized view because it contains ANSI join. To resolve this there is a hint mv_merge
which forces Oracle to do more complex query rewrite evaluation. See details on MOS in Doc ID 2042031.1.
create materialized view mv enable query rewrite as select --+ mv_merge product, sub_product, sum(value) value, count(*) cnt from fact f join product p on f.product_id = p.id group by product, sub_product; Materialized view created. select --+ rewrite_or_error product, sub_product, sum(value) value, count(*) cnt from fact f join product p on f.product_id = p.id where p.sub_product = 'sub_product2' group by product, sub_product; PRODUCT SUB_PRODUCT VALUE CNT ---------- --------------- ---------- ---------- product2 sub_product2 1.0000E+11 200000
Query rewrite and dimensions
Above examples demonstrated query rewrite technique when logic in a query is pretty much the same as in the materialized view. However query rewrite can be enabled in more complex scenarios, for example when aggregation granularity differs in a query and in the materialized view.
To enable query rewrite for such cases we need to adjust default value for query_rewrite_integrity
and create a dimension with a relation between attributes.
create materialized view mv enable query rewrite as select --+ mv_merge sub_product, sum(value) value, count(*) cnt from fact f, product p where f.product_id = p.id group by sub_product; Materialized view created. alter session set query_rewrite_integrity = trusted; Session altered. select --+ rewrite_or_error product, sum(value) value, count(*) cnt from fact f join product p on f.product_id = p.id where p.product = 'product2' group by product; from fact f * ERROR at line 3: ORA-30393: a query block in the statement did not rewrite create dimension product_dim level product is (product.product) level sub_product is (product.sub_product) hierarchy product_hierarchy (sub_product child of product); Dimension created. select --+ rewrite_or_error product, sum(value) value, count(*) cnt from fact f join product p on f.product_id = p.id where p.product = 'product2' group by product; PRODUCT VALUE CNT ---------- ---------- ---------- product2 2.0000E+11 400000 select * from dbms_xplan.display_cursor(null, null, format => 'basic predicate'); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ EXPLAINED SQL STATEMENT: ------------------------ select --+ rewrite_or_error product, sum(value) value, count(*) cnt from fact f join product p on f.product_id = p.id where p.product = 'product2' group by product Plan hash value: 1319296212 -------------------------------------------------- | Id | Operation | Name | -------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT GROUP BY NOSORT | | |* 2 | HASH JOIN | | | 3 | VIEW | | | 4 | HASH UNIQUE | | |* 5 | TABLE ACCESS FULL | PRODUCT | | 6 | MAT_VIEW REWRITE ACCESS FULL| MV | -------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("from$_subquery$_006"."SUB_PRODUCT"="MV"."SUB_PRODUCT") 5 - filter("PRODUCT"='product2') 26 rows selected.
Once dimension is created Oracle can derive the set of sub-products which should be used to access materialized view based on a product filter specified in a query.