How to use Oracle DBMS_ADVANCED_REWRITE with variable binding?

Question

We need to implement request rewriting using a variable binding, because we do not have the ability to modify the source code of the web application. 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 fail 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 read here that there is work, but I just cannot find the document anywhere on the Internet.

Could you tell me what work is?

Answer 1

You cannot specify binding parameters, but it should work as you wish. The key is the recursive parameter that you passed as mode . The recursive and general mode intercepts all statements that include the table (or view), ignoring the filter, and converts them to target the second table (or view), the adaptation of the filter condition to the original expression . (If you defined it as TEXT_MATCH , he checked for the same filter in the source and destination statements to trigger the conversion.)

The example below shows that even if we do not define any binding condition, the id = 2 filter is still applied; in other words, it actually converts SELECT * FROM A1 where id = 2 to 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

• the engine transparently applies the transformation and returns the filtered result
• In addition, filter conversion is applied. The filter is correctly pushed to the source table to extract values ​​from A1 . It does not blindly extract all values ​​from A2 and then applies a filter, so performance is maintained.