Why is Query Rewrite not happening on my Materialized Views?

You have designed the model and implemented the dimensions and fact tables. Data is clean. You have implemented all the required PK-FK relationships, created required indexes, gathered statistics and built the required materialized views.

The analytic design team have completed report and issues SQL query! Oops…
You expected the response time of < 10 seconds, instead the front end is clocking for over 4 minutes, now.

You quickly investigate the active sessions in Oracle and see the BI query is performing FTS on your largest FACT table. Why is it not using the Materialized View you so painstakingly built? Why is Query Rewrite not happening?

DBMS_MVIEW.EXPLAIN_REWRITE comes to rescue!

Conditions that must be satisfied for MV Query Rewrite to occur

The conditions under which Oracle will Rewrite queries using MV are well documented here.
In summary following techniques are used for MV query rewrite.
In addition if your MV is not fresh (not expoected to be due to batch or on-demand rebuild) then it is required to set QUERY_REWRITE_INTEGRITY at session (or system level) to TRUSTED or STALE_TOLERATED.
When the MV is not used for Query Rewrite, how do we find out WHY?
We’ll use DBMS_MVIEW.EXPLAIN_REWRITE.
Script for building dimension and fact tables are at the end of document. Borrowed from ASKTOM site.
Based on the setup and MV creation following query does NOT rewrite. Let us check WHY?
We’ll create REWRITE_TABLE.
CREATE TABLE REWRITE_TABLE(
  statement_id          VARCHAR2(30),   -- id for the query
  mv_owner              VARCHAR2(30),   -- owner of the MV
  mv_name               VARCHAR2(30),   -- name of the MV
  sequence              INTEGER,        -- sequence no of the msg
  query                 VARCHAR2(2000), -- user query
  query_block_no        INTEGER,        -- block no of the current subquery
  rewritten_txt         VARCHAR2(2000), -- rewritten query
  message               VARCHAR2(512),  -- EXPLAIN_REWRITE msg
  pass                  VARCHAR2(3),    -- rewrite pass no
  mv_in_msg             VARCHAR2(30),   -- MV in current message 
  measure_in_msg        VARCHAR2(30),   -- Measure in current message 
  join_back_tbl         VARCHAR2(30),   -- Join back table in message 
  join_back_col         VARCHAR2(30),   -- Join back column in message
  original_cost         INTEGER,        -- Cost of original query
  rewritten_cost        INTEGER,        -- Cost of rewritten query
  flags                 INTEGER,        -- associated flags
  reserved1             INTEGER,        -- currently not used 
  reerved2              VARCHAR2(10));   -- currently not used;
  
truncate table REWRITE_TABLE;  
 
DECLARE
  qrytext VARCHAR2(500)  :='  select /*+ REWRITE_OR_ERROR */  time_hierarchy.qtr_yyyy, sum(sales_amount)
       from sales, time_hierarchy
      where sales.trans_date = time_hierarchy.day
      group by time_hierarchy.qtr_yyyy';
    idno    VARCHAR2(30) :='ID1';
BEGIN
  DBMS_MVIEW.EXPLAIN_REWRITE(qrytext, '', idno);
END;
/
SELECT message FROM rewrite_table ORDER BY sequence;
 
QSM-01150: query did not rewrite    
QSM-01082: Joining materialized view, SALES_MV, with table, TIME_HIERARCHY, not possible  
QSM-01102: materialized view, SALES_MV, requires join back to table, TIME_HIERARCHY, on column, QTR_YYYY    

APPENDIX A – Scripts

Set up for data :
set autotrace traceonly statistics;
set timing on;
 
drop table sales purge;
 
create table sales
    (trans_date date, cust_id int, sales_amount number );
 
 
insert /*+ APPEND */ into sales
    select trunc(sysdate,'year')+mod(rownum,366) TRANS_DATE,
            mod(rownum,100) CUST_ID,
            abs(dbms_random.random)/100 SALES_AMOUNT
      from all_objects;
commit;
 
begin
   for i in 1 .. 4
   loop
       insert /*+ APPEND */ into sales
       select trans_date, cust_id, abs(dbms_random.random)/100 
         from sales;
       commit;
   end loop;
end;
/
 
select count(*) from sales;
 
drop table time_hierarchy;
create table time_hierarchy
(day, mmyyyy, mon_yyyy, qtr_yyyy, yyyy)
as
select distinct
  trans_date    DAY,
  cast (to_char(trans_date,'yyyymm') as number) MMYYYY,
  to_char(trans_date,'mon-yyyy') MON_YYYY,
  'Q' || ceil( to_char(trans_date,'mm')/3) || ' FY'
      || to_char(trans_date,'yyyy') QTR_YYYY,
  cast( to_char( trans_date, 'yyyy' ) as number ) YYYY
 from sales;
 
exec dbms_stats.gather_table_stats( user, 'SALES', cascade=>true );
exec dbms_stats.gather_table_stats( user, 'TIME_HIERARCHY', cascade=>true );
exec dbms_stats.gather_table_stats( user, 'SALES_MV', cascade=>true );
 
drop materialized view sales_mv;
create materialized view sales_mv
   build immediate
   refresh on demand
   enable query rewrite
   as
   select sales.cust_id, sum(sales.sales_amount) sales_amount,
          time_hierarchy.mmyyyy
     from sales, time_hierarchy
    where sales.trans_date = time_hierarchy.day
    group by sales.cust_id, time_hierarchy.mmyyyy;
Check Query Rewrite:
select name, value from v$parameter
where name in ('query_rewrite_enabled','query_rewrite_integrity');
 
alter session set query_rewrite_enabled = TRUE ;
alter session set query_rewrite_integrity = TRUSTED;
 
alter session set query_rewrite_integrity = STALE_TOLERATED;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s