select ename from emp;

And instead of seeing the ename column, get an unexpected result:

select 'Haha, no ename for you' from dual;

Sounds like a barrel of laughs to me, and a great practical joke.  However, the dbms_advanced_rewrite package gives us some huge technical benefit in the area of improving query speed. 

The dbms_advanced_rewrite package is a native SQL rewrite that is identical to the query rewrite used in materialized views to direct a query to a pre-aggregated result table, saving repeated scans of a base table.

For details on query rewrite with materialized views, see:

For instance, we can rewrite any SQL query to use dimension tables (where appropriate) to give us a batter join plan.  This powerful tool allows the DBAs to change application SQL at a database-wide level, and removed the tedious "SQL hunt" to track-down the original developer code.  Using the dbms_advanced_rewrite package, we can customize queries on the database side, instead of the application side.

An example of dbms_query_rewrite

Some other uses for dbms_advanced_rewrite may include adding hints, re-arranging join orders, all to improve the speed of SQL.  For example, consider a sales application with sub-optimal SQL execution plans. Assume that we are storing the sale prices in the database, and the application has added some compiled executable code that dynamically adds the sales tax to the price.

But what happens when a State changes their sales tax from 3.5% to 5%? Let's assume that the developers no longer have their original source code, (just a compiled module), and it will be time-consuming and risky to implement new code. The original query looks like this from the library cache:

select 
   price + (price * 0.035) as tax
from 
   sales_table
where 
   state = 'NC';

We, the DBA, can change this query at the database-level by re-writing the syntax. Our goal is to change the query to reflect the changed sales tax:

select 
   price + (price * 0.05) as tax
from 
   sales_table
where 
   state = 'NC';

Okay, let's try a working example of dbms_advanced_rewrite, using our tax example.

create table sales_table (
item_id number primary key,
price number (10,2)
);

insert into sales_table values (1, 14.95);
insert into sales_table values (2, 17.50);
insert into sales_table values (3, 21.35);
commit;

SQL> select price + (price * 0.035) as tax from sales_table;

TAX
----------
15.47325
18.1125
22.09725

begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
'new_tax',
'select price + (price * 0.035) as tax from sales_table',
'select price + (price * 0.05) as tax from sales_table',
false);
end;
/

SQL> select price + (price * 0.035) as tax from sales_table;

TAX
----------
15.6975
18.375
22.4175

As we see, we invoke dbms_advanced_rewrite with two arguments, the old query and our improved query and we get the desired results quickly, with little fuss!

Once you’ve created your re-written queries you can query the data dictionary and see the details. We simply query the view dba_rewrite_equivalences.  Also note than you can use the procedure  dbms_advanced_rewrite.drop_rewrite_equivalence to remove query rewrite for specific SQL statements.

Obviously, dynamic query rewriting is potentially harmful, but just like nuclear weaponry, hot coffee, and the Internet, dynamic query rewrite can be a very useful and powerful tool.

Here is another example.