oracle - 如何将 Oracle DBMS_ADVANCED_REWRITE 与绑定(bind)变量一起使用？

Question

我们需要使用绑定(bind)变量实现查询重写，因为我们没有修改 Web 应用程序源代码的选项。例子:

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;

上面的命令会报错，因为有一个绑定(bind)变量:

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.

我正在阅读 here有一个解决方法，但我只是无法在网上的任何地方找到该文档。

你能告诉我周围的工作是什么吗？

Answer 1

您不能指定绑定(bind)参数，但它应该已经按照您的意愿工作了。关键是recursive您作为 mode 传递的参数.recursive和 general模式将拦截所有涉及表(或 View )的语句，忽略过滤器，并将它们转换为以第二个表(或 View )为目标，调整原始语句中的过滤条件 .
(如果您将其定义为 TEXT_MATCH ，它将检查原始语句和目标语句中是否存在相同的过滤器以触发转换。)

在下面的示例中可以看到，即使我们没有定义任何绑定(bind)条件，过滤器 id = 2尽管如此，还是被应用了；换句话说，它实际上正在改变 SELECT * FROM A1 where id = 2进入 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 

如你看到的

引擎透明地应用转换并返回过滤结果

最重要的是，应用了过滤器的转换。过滤器被正确“推送”到源表中，以从 A1 中提取值.它不是盲目地从 A2 中提取所有值然后应用过滤器，因此保留了性能。