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.

CONN sys/password AS SYSDBA

GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO test;
GRANT CREATE MATERIALIZED VIEW TO test;

Next we create and populate a test table.

CONN test/test

DROP TABLE rewrite_test_tab;

CREATE TABLE rewrite_test_tab (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT rewrite_test_tab_pk PRIMARY KEY (id)
);

INSERT INTO rewrite_test_tab (id, description) VALUES (1, 'GLASGOW');
INSERT INTO rewrite_test_tab (id, description) VALUES (2, 'BIRMINGHAM');
INSERT INTO rewrite_test_tab (id, description) VALUES (3, 'LONDON');
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'rewrite_test_tab');

Let's assume our packaged applications issues the following statement.

SELECT * FROM rewrite_test_tab;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 GLASGOW
         2 BIRMINGHAM
         3 LONDON

3 rows selected.

SQL>

Instead, we would prefer the data to be in proper-case and ordered by the description. This can be achieved by creating the following view and functional equivalence definition.

CREATE OR REPLACE VIEW rewrite_test_tab_v AS
SELECT id,
       INITCAP(description) AS description
FROM   rewrite_test_tab
ORDER BY description;

BEGIN
  SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
     name             => 'test_rewrite',
     source_stmt      => 'SELECT * FROM rewrite_test_tab',
     destination_stmt => 'SELECT * FROM rewrite_test_tab_v',
     validate         => FALSE,
     rewrite_mode     => 'TEXT_MATCH');
END;
/

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.

SELECT * FROM rewrite_test_tab;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 GLASGOW
         2 BIRMINGHAM
         3 LONDON

3 rows selected.

SQL>

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.

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

Session altered.

SELECT * FROM rewrite_test_tab;

        ID DESCRIPTION
---------- --------------------------------------------------
         2 Birmingham
         1 Glasgow
         3 London

3 rows selected.

SQL>

The [USER|ALL|DBA]_REWRITE_EQUIVALENCES views can be used to display information about defined functional equivalence definitions.

SELECT * FROM user_rewrite_equivalences;

OWNER                          NAME
------------------------------ ------------------------------
SOURCE_STMT
--------------------------------------------------------------------------------
DESTINATION_STMT                                                                 REWRITE_MO
-------------------------------------------------------------------------------- ----------
TEST                           TEST_REWRITE
SELECT * FROM rewrite_test_tab
SELECT * FROM rewrite_test_tab_v                                                 TEXT_MATCH


1 row selected.

SQL>

The functional equivalence definition is deleted using the DROP_REWRITE_EQUIVALENCE procedure.

EXEC SYS.DBMS_ADVANCED_REWRITE.drop_rewrite_equivalence (name => 'test_rewrite');

For more information see:

DBMS_ADVANCED_REWRITE

Hope this helps. Regards Tim...

Back to the Top.