This package completely passed me by until it was mentioned in Dan Hotka’s presentation (Tuning SQL When You Cannot Change the Code) at OpenWorld. I ad a play with it and this was the result.
DBMS_ADVANCED_REWRITE in Oracle 10g
Cheers
Tim…
The ORACLE-BASE Blog
Oracle related rants (and lots of off-topic stuff)…
This is a great feature with a lot of possibilities. I wrote something a while ago about how you can use it to rewrite queries so as to take account of primary key data that the optimizer sometimes will not. Somewhere near the end of this: http://oraclesponge.wordpress.com/2006/08/14/more-on-redundant-materialized-views/
I saw this feature being mentioned at one of the OOW sessions and I thought “that’s cool”, but the significance of it never really sunk in until I read about it in more detail, what a superb feature this is!
Great for tricking and annoying people!
Cheers
Tim…
Yep, that’s what I said…practical jokes galore! I’ll throw in my “I did this too” here.
http://www.oraclealchemist.com/news/trick-2-rewrite-a-query-at-runtime/