CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_ADVANCED_REWRITE
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/prvtxrmv.plb
First Available 10.1
Character Set The character set must be set to WE8ISO8859P1 for this feature to work.
Dependencies SUM$
all_rewrite_equivalences dbms_rwequiv_lib dbms_utility
dba_rewrite_equivalences dbms_sys_error user_rewrite_equivalences

Error Messages
Error Code Reason
ORA-30388 Name of the rewrite equivalence is not specified
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-30396 Rewrite equivalence procedures require the COMPATIBLE parameter to be set to 10.1 or greater

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.
Object Privileges execute on dbms_advanced_rewrite
create materialized view (or) create any materialized view
GRANT execute ON dbms_advanced_rewrite TO uwclass;
GRANT create materialized view TO uwclass;

CREATE SYNONYM dbms_advanced_rewrite FOR sys.dbms_advanced_rewrite;

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'
SCOPE=BOTH;

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%rewrite%';

-- the 10g and 11gR1 default is ENFORCED which is not compatible.
 
ALTER_REWRITE_EQUIVALENCE
Changes the mode of the rewrite equivalence declaration to the mode  specified dbms_advanced_rewrite.alter_rewrite_equivalence(
name IN VARCHAR2,
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

Overload 1
dbms_advanced_rewrite.declare_rewrite_equivalence (
name             VARCHAR2,
source_stmt      VARCHAR2,
destination_stmt VARCHAR2,
validate         BOOLEAN  := TRUE,
mode             VARCHAR2 := 'TEXT_MATCH');
conn / as sysdba

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

-- click here to download demo test data and save to c: emp
-- create the demo tables and data by running servers.sql

SQL> @c: emp\servers.sql

EXPLAIN PLAN FOR
SELECT srvr_id FROM servers INTERSECT SELECT srvr_id FROM serv_inst;

set linesize 121

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers s
WHERE EXISTS (
  SELECT srvr_id
  FROM 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 servers INTERSECT SELECT srvr_id FROM serv_inst', 'SELECT srvr_id FROM servers s WHERE EXISTS (SELECT srvr_id FROM 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 servers
INTERSECT
SELECT srvr_id FROM 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);

Overload 2
dbms_advanced_rewrite.declare_rewrite_equivalence (
name             VARCHAR2,
source_stmt      CLOB,
destination_stmt CLOB,
validate         BOOLEAN  := TRUE,
mode             VARCHAR2 := 'TEXT_MATCH');
DECLARE
 sclob CLOB;
 dclob CLOB;
BEGIN
  sclob := CAST('SELECT srvr_id FROM servers INTERSECT SELECT srvr_id FROM serv_inst' AS CLOB);

  dclob := CAST('SELECT srvr_id FROM servers s WHERE EXISTS (SELECT srvr_id FROM serv_inst i WHERE s.srvr_id = i.srvr_id)' AS CLOB);

  dbms_advanced_rewrite.declare_rewrite_equivalence('UW', sclob, dclob, TRUE, 'TEXT_MATCH');
END;
/

exec dbms_advanced_rewrite.drop_rewrite_equivalence('UW');
 
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');
 
Related Topics
Explain Plan
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [54 users online]    © 2010 psoug.org