Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle10g OLAP Query Equivalence and dbms_advanced_rewrite

Oracle Tips by Burleson Consulting


 


By Mark Rittman

Query Equivalence

One of the options available for the Enterprise Edition of Oracle Database 10g is the OLAP Option, a multidimensional calculation engine that allows the DBA to perform OLAP analysis on multidimensional datatypes. By using the OLAP Option, DBAs working on data warehousing projects can choose to store their detail level data in normal Oracle relational tables, and then store aggregated data in OLAP Option ?analytic workspaces? for further multidimensional analysis.

With the OracleOLAP Option, you could provide access to these analytic workspaces using SQL statements, by using the new OLAP_TABLE function.

select product, city, sales, 
  from table(OLAP_TABLE('my_first_aw DURATION session',
   'SALES_TABLE',
   '',
   'DIMENSION category from product 
    DIMENSION country from geography
    MEASURE sales FROM sales
    ));

However, it was not possible to use these analytic workspaces as replacements for materialized views if you wanted to take advantage of query rewrite, as the query rewrite mechanism in Oraclewould never recognise the OLAP_TABLE function as being one that could provide the aggregated answers to the users? original query. Oracle Database 10g addresses this shortcoming by providing a new feature called ?query equivalence.?

Query equivalence is declared using the DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE procedure, and uses the syntax:

DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE(
	Declaration_name,
	Source_statement,
	Target_statement
	)

Query equivalence allows the DBA to declare that two SQL statements are functionally equivalent, and that the target statement should be used in preference to the source statement. By using the query equivalence feature, a DBA can produce a custom SQL query, in this instance by using the OLAP_TABLE feature to retrieve summary data from an analytic workspace, and have the query used to satisfy a regular SQL statement that summarises via the usual sum() and group by clauses.

Taking the example previously cited, we might want our analytic workspace to be used to provide a specific summary for an SQL query. In this case, our SQL query might be:

select        category, country, sum(sales)
from          product p, geography g, sales s
where         s.product_id = p.product_id
and           s.geography_id = p.geography_id
group         by p.category, g.country

To declare that our analytic workspace query is functionally equivalent to the previous query, issue the command

DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
   'my_first_equivalence',
   'select     category, country, sum(sales)
from 	   product p, geography g, sales s
where    s.product_id = p.product_id
and 	   s.geography_id = p.geography_id
group by p.category, g.country',
    ' select product, city, sales, 
      from table(OLAP_TABLE('my_first_aw DURATION session',
      'SALES_TABLE',
      '',
      'DIMENSION category from product 
      DIMENSION country from geography
      MEASURE sales FROM sales))');

Query equivalence can be used to substitute any SQL DML statement for another (including use of the new SQL Model clause), and is particularly useful when SQL is generated by an application and cannot be changed, but the DBA knows of a different way to phrase the query, perhaps using new data structures (such as an OLAP Option analytic workspace) that he has created.

Also see:

 


 

 

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.