Oracle | DBMS_ADVANCED

Oracle DBMS_ADVANCED_REWRITE Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Contains interfaces for advanced query rewrite users. Using this package, you can create, drop, and maintain functional equivalence declarations for query rewrite.

AUTHID CURRENT_USER

Character Set The character set must be set to WE8ISO8859P1 for this feature to work.

Dependencies

ALL_REWRITE_EQUIVALENCES	DBMS_STANDARD	DBMS_UTILITY
DBA_REWRITE_EQUIVALENCES	DBMS_SYS_ERROR	SUM$
DBMS_RWEQUIV_LIB

Documented Yes

Exceptions

Error Code	Reason
ORA-30354	Query rewrite not allowed on SYS relations
ORA-30388	Name of the rewrite equivalence is not specified
ORA-30389	The source statement is not compatible with the destination statement
ORA-30391	The specified rewrite equivalence does not exist
ORA-30392	The checksum analysis for the rewrite equivalence failed
ORA-30393	A query block in the statement did not rewrite
ORA-30394	Source statement identical to the destination statement
ORA-30396	Rewrite equivalence procedures require the COMPATIBLE parameter to be set to 10.1 or greater

First Available 10.1

Modes

Value	Description
disabled	Query rewrite does not use the equivalence declaration. Use this mode to temporarily disable use of the rewrite equivalence declaration.
general	Query rewrite uses the equivalence declaration in all of its transformation modes against the incoming request queries. However, query rewrite makes no attempt to rewrite the specified destination_query.
recursive	Query rewrite uses the equivalence declaration in all of its transformation modes against the incoming request queries. Moreover, query rewrite further attempts to rewrite the specified destination_query for further performance enhancements whenever it uses the equivalence declaration.
text_match	Query rewrite uses the equivalence declaration only in its text match modes. This mode is useful for simple transformations.

Security Model Owned by SYS with no privileges granted

Recommend the following
GRANT execute ON dbms_advanced_rewrite TO uwclass; GRANT create materialized view TO uwclass; CREATE SYNONYM dbms_advanced_rewrite FOR sys.dbms_advanced_rewrite;

Source {ORACLE_HOME}/rdbms/admin/prvtxrmv.plb

Startup Parameters ALTER SYSTEM SET query_rewrite_integrity = <'TRUSTED' | 'STALE_TOLERATED'> SCOPE=<BOTH | MEMORY | SPFILE>;

set linesize 121

      col name format a30

      col value format a30

      

      SELECT name, value

      FROM gv$parameter

      WHERE name LIKE '%rewrite%';

      

      ALTER SYSTEM SET query_rewrite_integrity = 'TRUSTED'

      COMMENT='Permanent Change To System Configuration'

      SID='*'

      SCOPE=BOTH;

      

      SELECT name, value

      FROM gv$parameter

      WHERE name LIKE '%rewrite%';

      

      -- the 10g through 12.1 default is ENFORCED which is not compatible.

Subprograms

ALTER_REWRITE_EQUIVALENCE	DECLARE_REWRITE_EQUIVALENCE	VALIDATE_REWRITE_EQUIVALENCE
BUILD_SAFE_REWRITE_EQUIVALENCE	DROP_REWRITE_EQUIVALENCE

ALTER_REWRITE_EQUIVALENCE

Changes the mode of the rewrite equivalence declaration to the mode specified dbms_advanced_rewrite.alter_rewrite_equivalence( name IN VARCHAR2, rewrite_mode IN VARCHAR2);

exec dbms_advanced_rewrite.alter_rewrite_equivalence('UW', 'DISABLED');

BUILD_SAFE_REWRITE_EQUIVALENCE

Enables the rewrite of top-level materialized views using sub-materialized views dbms_advanced_rewrite.build_safe_rewrite_equivalence( name IN VARCHAR2, source_stmt IN CLOB, destination_stmt IN CLOB, check_sum IN BINARY_INTEGER);

TBD

This procedure enables the rewrite and refresh of top-level materialized views using submaterialized views. It is provided for the exclusive use by scripts generated by the DBMS_ADVISOR.TUNE_MVIEW procedure. It is required to enable query rewrite and fast refresh when DBMS_ADVISOR.TUNE_MVIEW decomposes a materialized view into a top-level materialized view and one or more submaterialized views.

Oracle does not recommend you directly use the BUILD_SAFE_REWRITE_EQUIVALENCE procedure. You should use either the DBMS_ADVISOR.TUNE_MVIEW or the DBMS_ADVANCED_REWRITE.CREATE_REWRITE_EQUIVALENCE procedure as appropriate.

DECLARE_REWRITE_EQUIVALENCE

Creates a declaration indicating that source_stmt is functionally equivalent to destination_stmt for as long as the equivalence declaration remains enabled dbms_advanced_rewrite.declare_rewrite_equivalence ( name VARCHAR2, source_stmt CLOB, destination_stmt CLOB, validate BOOLEAN := TRUE, rewrite_mode VARCHAR2 := 'TEXT_MATCH');

conn sys as sysdba@pdbdev

      

      GRANT execute on dbms_advanced_rewrite TO uwclass;

      GRANT create materialized view TO uwclass;

      

      CREATE OR REPLACE PUBLIC SYNONYM dbms_advanced_rewrite

      FOR dbms_advanced_rewrite;

      

      conn uwclass/uwclass@pdbdev

      

      -- click here
       to download demo test data and save to c:\temp

      -- create the demo tables and data by running servers.sql

      

      SQL> @c:\temp\servers.sql

      

      EXPLAIN PLAN FOR

      SELECT srvr_id FROM uwclass.servers INTERSECT SELECT srvr_id FROM uwclass.serv_inst;

      

      set linesize 121

      

      SELECT * FROM TABLE(dbms_xplan.display);

      

      EXPLAIN PLAN FOR

      SELECT srvr_id

      FROM uwclass.servers s

      WHERE EXISTS (

        SELECT srvr_id

        FROM uwclass.serv_inst i

        WHERE s.srvr_id = i.srvr_id);

      

      SELECT * FROM TABLE(dbms_xplan.display);

      

      BEGIN

        dbms_advanced_rewrite.declare_rewrite_equivalence('UW',

      'SELECT srvr_id FROM uwclass.servers INTERSECT SELECT srvr_id FROM uwclass.serv_inst',

      'SELECT srvr_id FROM uwclass.servers s WHERE EXISTS (SELECT srvr_id FROM uwclass.serv_inst i WHERE s.srvr_id = i.srvr_id)',

       TRUE,

      'TEXT_MATCH');

      END;

      /

      

      SELECT *

      FROM user_rewrite_equivalences;

      

      EXPLAIN PLAN

      SET STATEMENT_ID = 'AFTER'

      FOR

      SELECT srvr_id FROM uwclass.servers

      INTERSECT

      SELECT srvr_id FROM uwclass.serv_inst;

      

      SELECT * FROM TABLE(dbms_xplan.display);

      

      exec  dbms_advanced_rewrite.drop_rewrite_equivalence ('UW');

      

      EXPLAIN PLAN

      SET STATEMENT_ID = 'AFTER'

      FOR

      SELECT srvr_id FROM servers

      INTERSECT

      SELECT srvr_id FROM serv_inst;

      

      SELECT * FROM TABLE(dbms_xplan.display);

DROP_REWRITE_EQUIVALENCE

Drops the specified rewrite equivalence declaration dbms_advanced_rewrite.drop_rewrite_equivalence(name IN VARCHAR2);

exec dbms_advanced_rewrite.drop_rewrite_equivalence('UW');

VALIDATE_REWRITE_EQUIVALENCE

Validates the rewrite equivalence declaration using the specified mode dbms_advanced_rewrite.validate_rewrite_equivalence(name IN VARCHAR2);

exec dbms_advanced_rewrite.validate_rewrite_equivalence('UW');