How to use Oracle DBMS_ADVANCED_REWRITE with bind variable?

Question

We need to implement a query rewrite with a bind variable because we don't have the option of modifying the web application source code. Example:

BEGIN
     SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
     name             => 'test_rewrite2',
     source_stmt      => 'select COUNT(*) from ViewX where columnA = :1',
     destination_stmt => 'select COUNT(*) from ViewY where columnA = :1',
     validate         => FALSE,
     rewrite_mode     => 'recursive');    
END;

The above command will result in error because there is a bind variable:

30353. 00000 -  "expression not supported for query rewrite"
*Cause:    The SELECT clause referenced UID, USER, ROWNUM, SYSDATE,
           CURRENT_TIMESTAMP, MAXVALUE, a sequence number, a bind variable,
           correlation variable, a set result, a  trigger return variable, a
           parallel table queue column, collection iterator, a non-deterministic
           date format token RR, etc.
*Action:   Remove the offending expression or disable the REWRITE option on
           the materialized view.

I am reading here that there is a work around but I just cannot find the document anywhere online.

Could you please tell me what the work around is?

Answer 1

You can't specify the bind parameters, but it should already work as you wish. The key is the recursive parameter you passed as mode. The recursive and general mode will intercept all statements that involve the table (or view), disregarding the filter, and transform them to target the second table (or view), adapting the filter condition from your original statement. (If you had defined it as TEXT_MATCH, it would have checked the presence of the same filter in the original and target statement in order to trigger the transformation.)

In the example below one can see that even if we don't define any bind condition, the filter id = 2 is applied nervetheless; in other words it is actually transforming the SELECT * FROM A1 where id = 2 into SELECT * FROM A2 where id = 2

set LINESIZE 300

drop table A1;
drop view A2;
drop index A1_IDX;
EXEC SYS.DBMS_ADVANCED_REWRITE.drop_rewrite_equivalence (name => 'test_rewrite');

create table A1 (id number, name varchar2(20));

insert into A1 values(1, 'hello world');
insert into A1 values(2, 'hola mundo');

create index A1_IDX  on A1(id);

select * from A1;

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

CREATE OR REPLACE VIEW A2 AS
SELECT id,
       INITCAP(name) AS name
FROM   A1
ORDER BY id desc;


BEGIN
  SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
     name             => 'test_rewrite',
     source_stmt      => 'SELECT * FROM A1',
     destination_stmt => 'SELECT * FROM A2',
     validate         => FALSE,
     rewrite_mode     => 'recursive');
END;
/


select * from A1;

        ID NAME               
---------- --------------------
         2 Hola Mundo          
         1 Hello World         




select * from A1 where id = 2;


        ID NAME               
---------- --------------------
         2 Hola Mundo       


explain plan for
select * from A1 where id = 2;

select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                          
----------------------------------------------------------------------------------------
Plan hash value: 1034670462                                                                                                                                                                                                                                                                                 

----------------------------------------------------------------------------------------                                                                                                                                                                                                                    
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------                                                                                                                                                                                                                    
|   0 | SELECT STATEMENT              |        |     1 |    25 |     2   (0)| 00:00:01 |                                                                                                                                                                                                                    
|   1 |  VIEW                         | A2     |     1 |    25 |     2   (0)| 00:00:01 |                                                                                                                                                                                                                    
|   2 |   TABLE ACCESS BY INDEX ROWID | A1     |     1 |    25 |     2   (0)| 00:00:01 |                                                                                                                                                                                                                    
|*  3 |    INDEX RANGE SCAN DESCENDING| A1_IDX |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------                                                                                                                                                                                                                    


PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                          
---------------------------------------------------
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                                                                                                         

   3 - access("ID"=2)                                                                                                                                                                                                                                                                                       

Note                                                                                                                                                                                                                                                                                                        
-----                                                                                                                                                                                                                                                                                                       
   - dynamic sampling used for this statement (level=2)                                                                                                                                                                                                                                                     
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold                                                                                                                                                                                                                       

 20 rows selected 

As you can see

1. the engine is transparently applying the transformation and returning the filtered result
2. on top of that, the transformation on the filter is applied. The filter is correctly "pushed" into the source table, to extract the values from A1. It is not blindly extracting all values from A2 and then applying the filter, so the performance is preserved.