|
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
Oracle9i OLAP 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 Oracle9i would 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:
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
| 
