如何将Oracle DBMS_ADVANCED_REWRITE与绑定变量一起使用？
发布时间：2020-12-12 16:29:30 所属栏目：百科 来源：网络整理
导读：我们需要使用绑定变量实现查询重写,因为我们没有修改Web应用程序源代码的选项.例： BEGIN SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence ( name = 'test_rewrite2',source_stmt = 'select COUNT(*) from ViewX where columnA = :1',destination_st
我们需要使用绑定变量实现查询重写,因为我们没有修改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;
上面的命令会导致错误,因为有一个绑定变量： 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有一个解决方法,但我无法在网上找到任何文件. 你能告诉我这项工作是什么吗？ 您不能指定绑定参数,但它应该已经按照您的意愿工作.关键是您作为模式传递的递归参数.递归和通用模式将拦截涉及表(或视图)的所有语句,忽略过滤器,并将它们转换为目标第二个表(或视图),从原始语句调整过滤条件. (如果您已将其定义为TEXT_MATCH,则它将检查原始目标语句和目标语句中是否存在相同的过滤器,以便触发转换.) 在下面的例子中,我们可以看到,即使我们没有定义任何绑定条件,过滤器id = 2也是神经应用的;换句话说,它实际上将SELECT * FROM A1(其中id = 2)转换为SELECT * FROM A2,其中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',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中提取所有值然后应用过滤器,因此保留了性能. （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |