How to use package DBMS_ADVANCED_REWRITE to implement new query rewriting function in Oracle10g

Editor to share with you how to use the package DBMS_ADVANCED_REWRITE in Oracle10g to achieve the new query rewriting function. I hope you will get something after reading this article. Let's discuss it together.

Starting with Oracle10g version 1, a new package, DBMS _ ADVANCED_REWRITE, is provided to implement query rewriting. It allows you to intercept specific SQL statements and set the

It is redefined as another SQL statement. Here is a simple example:

1. Confirm that the test user has the necessary permissions to run

CONN sys/password AS SYSDBA

GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO test

GRANT CREATE MATERIALIZED VIEW TO test

[@ more@]

2. Create 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')

3. Query the test table

SELECT * FROM rewrite_test_tab

ID DESCRIPTION

1 GLASGOW

2 BIRMINGHAM

3 LONDON

3 rows selected.

SQL >

4. Create the view to be replaced by the SQL, and then redefine the above statement to query the view

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

/

Rewrite replaces only when they are consistent. And we want to realize that the output after replacement is different from the original output, so we need to modify the value of this parameter to

Only "TRUSTED" will do.

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 >

6. The view [USER | ALL | DBA] _ REWRITE_EQUIVALENCES contains information about these redefined queries.

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 >

After reading this article, I believe you have a certain understanding of "how to use the package DBMS_ADVANCED_REWRITE to achieve the new query rewriting function in Oracle10g". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!