News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

DBMS_ADVANCED_REWRITE – tuning without touching the code

  • Posted by Gavin Soorma
  • On June 22, 2009
  • 0 Comments

Very often, with packaged vendor applications we do not have access to the source code, but would like to customise the application to some specific requirements or in many cases would like an optimised piece of SQL code to be executed which is actually sub-optimal when run directly via the vendor application. We can use DBMS_ADVANCED_REWRITE for this.

This feature is available in Oracle 10g Release 2 (maybe Release 1 as well)

Scenario:

Application calculates tax based on product price – earlier used to be 3.5% of the price – now has changed to 5% of the product price because of government regulations.

Instead of making code changes to application we can direct Oracle to execute a totally different query when a particular query is executed

 

SQL> grant query rewrite to scott;

Grant succeeded.

SQL> grant execute on DBMS_ADVANCED_REWRITE to scott;

Grant succeeded.

 

 

SQL> create table sales_table (

item_id number primary key,

price number (10,2)

);

2 3 4

Table created.

SQL> 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

SQL> begin

sys.dbms_advanced_rewrite.declare_rewrite_equivalence

‘new_tax’,

‘select price + (price * 0.035) as tax from sales_table’, >>>> Old query run internally by application

‘select price + (price * 0.05) as tax from sales_table’, >>>> New query automatically run by application

false);

end;

PL/SQL procedure successfully completed.

SQL> select price + (price * 0.035) as tax from sales_table; >>> now calculates using new value of 5% instead of 3.5%

TAX

———-

15.6975

18.375

22.4175

 

 

(

 

0 Comments

Leave Reply

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