SWAT Blog

Oracle 10g Advanced Query Rewrite – Query Equivalence

June 26th, 2009 by Shailendra Parate

Occasionally there are instances where we have very little or no control over the query generated by application layer, especially if it a 3rd party application. If you’ve identified a problematic query originating from such an application, what do do ?

Well if your enterprise is on Oracle 10g, you are in luck.

Oracle 10g introduced Query equivalence, which can be used to substitute any SQL DML statement for another (including use of the new SQL Model clause), and is particularly useful when SQL is generated by an application and cannot be changed, but the DBA or advanced developer knows of a better way to phrase the query, perhaps using new data structures that they might have created.

Lets see step by step

create table dept
as
select * from scott.dept;

Problem Query

select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Optimized Query

create or replace view vw_optimised_dept
as
select deptno, LOWER(dname) dname, loc
from dept order by loc;

Query Equivalence

begin


sys.dbms_advanced_rewrite.declare_rewrite_equivalence

( name           => 'test_equivalence',
source_stmt      => 'select * from dept',
destination_stmt => 'select * from vw_optimised_dept',
validate         => FALSE,
rewrite_mode     => 'TEXT_MATCH' );
end;

/

That is it!
Next time Oracle 10g optimiser comes across this query it would rewrite it.

select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
40 operations BOSTON
30 sales CHICAGO
20 research DALLAS
10 accounting NEW YORK

Note the DNAME column in the lowercase, proving that our problematic query was rewritten.
It is however important to note that the text match rewrite can distinguish contexts where the difference between uppercase and lowercase is significant and where it is not.

For example, the following statements are equivalent:

SELECT X, 'aBc' FROM Y;
Select x, 'aBc' From y;

But following statements are not:

SELECT X, 'aBc' FROM Y;
SELECT X colx, 'aBc' coly FROM Y Y_ALIAS;

If you wish to remove this rewrite at a later stage-

exec sys.dbms_advanced_rewrite.drop_rewrite_equivalence( 'test_equivalence' );

select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

In this post I have only picked up one sample usage, for a more detailed discussion please visit Oracle documentation on Advanced Query Rewrite.

Tags: ,

Leave a Reply

  • © 2004-2015 Special Work & Technology Limited