Query rewrite in Oracle

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.