Trick #2 – Rewrite a Query at Runtime

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);
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

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.