HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Advanced query rewrite, as cool as dangerous
ORACLE STUFFS
05-Sep-2008
There are three basic levels how to use materialized views functionality :
  • Complex functionality (fast refresh materialized views) - it is easiest to use, anyway it is too expensive because of performance requirements (update with MV log could be even 10 times longer than without it). This way is good for non-critical parts of systems, referential and metadata tables etc.
  • Materialized view on prebuilt tables - eliminates refresh functionality (it should be replaced by customized ETL processing). Anyway consistency check functionality (STALE) remains.
  • Advanced query rewrite - just a torso of materialized view functionality - query rewrite remains. It could be used alse for correction of used inefficient queries . This variant we will describe in detail.

  • Using DBMS_ADVANCED_REWRITE you avoid both refresh and check consistency functionality. The first one could be solved by ETL, the second one could be eiter omited if the source is constant between ETL windows or solved by database triggers.

    By default the package is not granted to users (clever solution), so you have to grant execution to particular user as SYSDBA:

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


    Now you can force Oracle to rewrite queries like Materialized view does.
    There are two basic reasons:
  • Bypass part of functionality of materialized views by manual processes.
  • Rewrite some query stereotype to their equivalents get better execution performance.



  • Following "cheating" example illustrates functionality of rewrite:

    execute sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
     'EQ1'                          --name of equivalence
    ,'select * from tt1 where p=10' --source statement
    ,'select * from tt1 where p=9'  --rewritten statement
    ,FALSE                          --validate
    ,'GENERAL');

    SQL> select * from tt1 where p=10; A P X1 ---------- ---------- ---------- 90 9

    execute sys.DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE ('EQ1');

    SQL> select * from tt1 where p=10; A P X1 ---------- ---------- ---------- 100 10

    Note: The example above works just when the QUERY_REWRITE_INTEGRITY parameter is set either to 'stale_tolerated' or 'trusted'. When QUERY_REWRITE_INTEGRITY='enforced', the query works properly.

    Information about active equivalences you can get in data dictionary:

    SQL> select * from USER_REWRITE_EQUIVALENCES; OWNER NAME SOURCE_STMT DESTINATION_STMT REWRITE_MO ----- ------- ----------------------------- ---------------------------- ------- A2 EQ1 select * from tt1 where p=10 select * from tt1 where p=9 GENERAL


    Reasonable usage of modifying query on the same table is an alternative condition in queries.

    CASE STUDY:
    Table with included band history also contains following columns:
  • VALID_FROM
  • VALID_THRU .. is set to 31-DEC-2999 for current records
  • CURRENT_FLAG .. 'Y' for current records


  • Query ... WHERE VALID_THRU=to_date('29991231','yyyymmdd')

    is equal to ... WHERE CURRENT_FLAG='Y'

    Anyway when we implement partitioning based on VALID_THRU, the first version of query becomes much more efficient cause of partition pruning.
    If a lot of modules use the second version, Advanced query rewrite is an efficient solution.

    Ludek Bob Jankovsky
    All Right Reserved © 2007, Designed by Bob Jankovsky