The Tune MView Advisor, and improvements to
Query Rewrite
Query Rewrite (the ability for Oracle to
transparently redirect queries from detail
level to summary tables) is one of the best
data warehousing features in Oracle 8i and
9i, but it's sometimes a bit temperamental
and you can often find that queries don't
actually get rewritten. Sometimes this is
because you've broken one of the Query
Rewrite restrictions, sometimes it's because
your materialized view doesn't contain the
correct columns and aggregates. Oracle 10g
has a number of improvements to Query
Rewrite and the materialized view tuning
process that should make this process a bit
more productive.
With Oracle Database 10g, query rewrite
is now possible when your SELECT statement
contains analytic functions, full outer
joins, and set operations such as UNION,
MINUS and INTERSECT. In addition, you can
now use a hint, /*+ REWRITE_OR_ERROR */,
which will stop the execution of a SQL
statement if query rewrite cannot occur.
SQL> SELECT /*+ REWRITE_OR_ERROR */
2 s.prod_id,
3 sum(s.quantity_sold)
4 FROM sales s
5 GROUP BY s.prod_id;
FROM sales s
*
ERROR at line 4:
ORA-30393: a query block in the statement did not rewrite
Oracle 9i came with two packages,
DBMS_MVIEW.EXPLAIN_MVIEW and
DBMS_MVIEW.EXPLAIN_REWRITE that could be
used to diagnose why a materialized view
wasn't being used for query rewrite.
However, although these packages told you
why rewrite hadn't happened, they left it
down to you to work out how to alter your
CREATE MATERIALIZED VIEW statement to ensure
that rewrite happened correctly. Oracle
Database 10g comes with a new advisor
package, DBMS_ADVISOR.TUNE_MVIEW, that takes
as its input a CREATE MATERIALIZED VIEW DML
statement, and outputs a corrected version
that supports query rewrite and features
such as fast refresh.
More details on Query Rewrite
improvements and the Tune MView Adviser can
be found in
this presentation by Lillian Hobbs
(password 'presentation'), the
TUNE_MVIEW online documentation, the
Query Rewrite online documents and
this Oracle Database 10g Oracle-by-Example
tutorial.
