Trick #2 – Rewrite a Query at Runtime

Share Button

There are many Oracle tools that can wreak havoc on an unsuspecting DBA or developer, but all in all they are for the common good. Though Oracle never puts procedures in place that are made for practical jokes, the DBMS_ADVANCED_REWRITE package comes close.

DBMS_ADVANCED_REWRITE allows you to transform queries on the fly. An unsuspecting developer may try

select ename from emp;

And instead end up with

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

Sounds like a barrel of laughs to me, and a great practical joke item. However, the package gives us some technical benefit as well.

For instance, we can rewrite a query to use dimension tables where appropriate to give us the best possible joins. We, the DBAs, can change application code if the developers are having trouble tracking it down. Using this package, we can customize queries on OUR side, instead of theirs.

Some other uses may include adding hints, re-arranging join orders, or computing as necessary. Take a sales application, for instance. You store sale prices in your database, and the application has a bit of compiled code that adds sales tax on the fly.

Uh oh! The state changed the sales tax from 3.5% to 5% (pesky state). The developers have no original code, just a compiled module, and it will take time to implement new code. All we know is that the query always comes in as:

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

We, the DBA, can change it where the developer cannot. Simply change the query to:

select price + (price * 0.05) as tax
from sales_table;

Whenever it comes in.

Okay, enough theory. Let’s go ahead and see how it works. This tax example is a great one.

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);
SQL> select price + (price * 0.035) as tax from sales_table;

sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
'select price + (price * 0.035) as tax from sales_table',
'select price + (price * 0.05) as tax from sales_table',
SQL> select price + (price * 0.035) as tax from sales_table;


The results we wanted with little fuss!

If you haven’t gathered, it’s pretty easy to use. Simple give a name, an original query, and a target query, and you’re off.

Once you’ve made all of these, you can always look for them and delete as necessary. Simply query DBA_REWRITE_EQUIVALENCES. You can use the DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE as necessary.

Yes, this package can be potentially harmful. But like nuclear weaponry, hot coffee, and the Internet, it can also be very useful for your needs.

Join me tomorrow when we go over another trick! We’ll actually see how to store any data in a column, no matter what type of data it is.

Share Button


  1. You should mention all the limitations to using this package beginning with the use of bind variables.

    “Query Rewrite and Bind Variables

    Query rewrite is supported when the query contains user bind variables as long as the actual bind values are not required during query rewrite. If the actual values of the bind variables are required during query rewrite, then we say that query rewrite is dependent on the bind values. Because the user bind variables are not available during query rewrite time, if query rewrite is dependent on the bind values, it is not possible to rewrite the query.”

    It’s a shame really…

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.