Advanced Query Rewrite in 10g

Learnt something new about Oracle Database pretty much every day. Recently I learned something its about advanced query rewrite in 10g EE

scott@ORCL> create table customer
  2  nologging
  3  as
  4  select rownum as cust_id,
  5         object_name as cust_name,
  6         object_type as cust_type,
  7         created  as  created_dt
  8  from all_objects
  9  /

Table created.

Elapsed: 00:00:10.34
scott@ORCL> create table sales
  2  nologging
  3  as
  4  select decode(mod(level,366),0,1) as cust_id,
  5         abs(dbms_random.random)/100 as sales_amt,
  6         trunc(sysdate,'yyyy')+level as trans_date
  7  from dual
  8  connect by level <=100000
  9  /

Table created.

Elapsed: 00:00:01.46
scott@ORCL> alter table customer add constraint cust_pk primary key(cust_id);

Table altered.

Elapsed: 00:00:02.14
scott@ORCL> alter table sales add constraint sales_fk foreign key(cust_id) references customer;

Table altered.

Elapsed: 00:00:00.89
scott@ORCL> begin
  2    dbms_stats.gather_table_stats(ownname=>USER,tabname=>'CUSTOMER',estimate_percent=>100,cascade=>true,method_opt=>'for all indexed columns size 254');
  3    dbms_stats.gather_table_stats(ownname=>USER,tabname=>'SALES',estimate_percent=>100,cascade=>true,method_opt=>'for all indexed columns size 254');
  4  end;
  5  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.59
scott@ORCL> create table cust_sales_mv
  2  nologging
  3  as
  4  select c.cust_id,count(s.cust_id) cust_count,sum(s.sales_amt) as sales_amt
  5  from customer c,sales s
  6  where c.cust_id = s.cust_id (+)
  7  and   c.created_dt between to_date('01/01/2005','mm/dd/yyyy') and to_date('12/31/2005','mm/dd/yyyy')
  8  group by c.cust_id
  9  /

Table created.

Elapsed: 00:00:00.06


scott@ORCL> begin
  2     sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
  3             name =>'demo_rewrite',
  4             source_stmt => ' select c.cust_id,count(s.cust_id) cust_count,sum(s.sales_amt) as sales_amt '||
  5                                             ' from customer c,sales s '||
  6                                             ' where c.cust_id = s.cust_id (+) '||
  7                                             ' and   c.created_dt between to_date(''01/01/2005'',''mm/dd/yyyy'') and to_date(''12/31/2005'',''mm/dd/yyyy'') ' ||
  8                                             ' group by c.cust_id ' ,
  9             destination_stmt =>' select * from cust_sales_mv ',
 10             validate => false,
 11             rewrite_mode =>'TEXT_MATCH' );
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.76

Now for this Query rewrite to take place ensure that query_rewrite_integrity should be either trusted or stale_tolerated

scott@ORCL> alter session set query_rewrite_integrity=trusted;

Session altered.

Elapsed: 00:00:00.00
scott@ORCL> set autotrace traceonly explain;
scott@ORCL> select c.cust_id,count(s.cust_id) cust_count,sum(s.sales_amt) as sales_amt
  2  from customer c,sales s
  3  where c.cust_id = s.cust_id (+)
  4  and   c.created_dt between to_date('01/01/2005','mm/dd/yyyy') and to_date('12/31/2005','mm/dd/yyyy')
  5  group by c.cust_id;
Elapsed: 00:00:00.85

Execution Plan
----------------------------------------------------------
Plan hash value: 2300330077

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| CUST_SALES_MV |     1 |    39 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

scott@ORCL> set autotrace off;