How to drive your colleagues nuts – dbms_advanced_rewrite (Oracle 10g)

If you really want to have some fun – and have your co-developers start wondering whether they are losing it altogether – you could consider spending a few minutes getting your head round the dbms_advanced_rewrite package, first introduced in Oracle 10g. This package is primarily intended to inform the CBO (Cost Based Optimizer) that certain queries requested by applications and end-users can actually be executed in a different way and still yield the same result. This typically allows the use of summary tables (other than Materialized Views), OLAP results held in Analytical Workspaces (see for example Query Equivalence by Mark Rittman) or any other alternative data source.

Here is how to trick your friends:

begin
   DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
   'my_first_equivalence',
   'select  ename from emp'
   ,'select dname from dept', false);
end;
/

When they now execute the most innocent statement available in any Oracle Database:

select ename
from emp
/

Here is their result:

ENAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS

4 rows selected.

The database was instructed to treat select ename from emp as if it had been asked select dname from dept, so that is what it does!

To remove any trace of your foulplay:

begin
    DBMS_ADVANCED_REWRITE.drop_REWRITE_EQUIVALENCE('my_first_equivalence');
end;

From the documentation:

Usage Notes

Query rewrite using equivalence declarations occurs simultaneously and in concert with query rewrite using materialized views. The same query rewrite engine is used for both. The query rewrite engine uses the same rewrite rules to rewrite queries using both equivalence declarations and materialized views. Because the rewrite equivalence represents a specific rewrite crafted by a sophisticated user, the query rewrite engine gives priority to rewrite equivalences over materialized views when it is possible to perform a rewrite with either a materialized view or a rewrite equivalence. For this same reason, the cost-based optimizer (specifically, cost-based rewrite) will not choose an unrewritten query plan over a query plan that is rewritten to use a rewrite equivalence even if the cost of the un-rewritten plan appears more favorable. Query rewrite matches properties of the incoming request query against the equivalence declaration’s source_stmt or the materialized view’s defining statement, respectively, and derives an equivalent relational expression in terms of the equivalence declaration’s destination_stmt or the materialized view’s container table, respectively.

The following modes are supported, in increasing order of power:

disabled: Query rewrite does not use the equivalence declaration. Use this mode to temporarily disable use of the rewrite equivalence declaration.

text_match: Query rewrite uses the equivalence declaration only in its text match modes. This mode is useful for simple transformations.

general: Query rewrite uses the equivalence declaration in all of its transformation modes against the incoming request queries. However, query rewrite makes no attempt to rewrite the specified destination_query.

recursive: Query rewrite uses the equivalence declaration in all of its transformation modes against the incoming request queries. Moreover, query rewrite further attempts to rewrite the specified destination_query for further performance enhancements whenever it uses the equivalence declaration.

Oracle recommends you use the least powerful mode that is sufficient to solve your performance problem.

Resources

• Query Rewrite, Chapter 22 in 9iR2 Data Warehousing Guide
• dbms_advanced_rewrite, 10g supplied packages reference