DBMS_ADVANCED_REWRITE in Oracle 10g

The DBMS_ADVANCED_REWRITE package allows you to intercept specific SQL statements and replace them with alternative statements. This is done by defining functional equivalence definitions, which are used by the rewrite engine in addition to regular query rewrites. This can be useful when you need to make minor alterations to the way applications work when you don't have access to the code. This article presents a simple example of how this can be achieved.

First we must make sure our user (TEST) has the necessary privileges to run the example code.

The functional equivalence definition states the output of the destination statement against the view produces the same result as the original source statement. The view definition contains contains the required order and description case.

At this point, repeating the original query still results in the old values.

The default settings of the QUERY_REWRITE_INTEGRITY parameter is "enforced", which means that a query rewrite will only happen if the rewritten statement produces exactly the same output at the original statement. Since our functional equivalence definition will result in differing output, the SQL statement is not rewritten. If we reset the QUERY_REWRITE_INTEGRITY parameter to "trusted" the rewrite will work.