Affecting Query Rewrite Performance 331
Chapter 11
hash partitions [1]. Partition query rewrite is known as PCT
query rewrite. A materialized view can include a partition marker
as a replacement for a partition key. The partition marker allows
specific identification of specific partitions, allowing query rewrite
on those marked partitions as pruned partitions. A partition
marker can identify one or more individual partitions. Once
again, all rows in a marked partition must be accessed for parti-
tioned query rewrite to occur. Also, only wholly fresh partitions
can be used for query rewrite. Only fresh partitions have an avail-
able partition marker. Stale partitions will not be identifiable by a
partition marker.
Nested Materialized Views
. The optimizer will iterate through the
query rewrite process using nested layers of materialized views until
the best option is found for the query, assuming all required criteria
are met.
GROUP BY Clause Extensions
. GROUP BY clause extensions such
as ROLLUP, CUBE, and GROUPING SETS clauses do allow query
The EXPAND_GSET_TO_UNION hint can be used for GROUP
BY clause extension queries in order to expand a grouping set to multiple
queries merged with the UNION set operator.
Bind Variables
. Bind variables are allowed for query rewrite as long
as their respective values are not required to perform query rewrite.
. Any expression is allowed as long as an underlying
materialized view caters completely for the evaluation of that expres-
sion, such as SUM(x) / COUNT(x) allowing AVG(x).
Other Possibilities
. Inline views or FROM clause embedded sub-
queries allow query rewrite, as well as self joining queries, views with
constraints (view constraints always match underlying tables); and
UNION set operator queries allow query rewrite assuming that an
underlying materialized view caters to all the required row sets.
11.3 Affecting Query Rewrite Performance
There are a number of factors possibly affecting query rewrite performance.
However, as reiterated in numerous chapters throughout this book, query
Affecting Query Rewrite Performance
rewrite and materialized views are one of those factors in which their use is
a tuning method in and of itself. In other words, performance tuning is
accomplished simply by creating appropriate materialized views. The fol-
lowing is a list of potentially performance-affecting factors:
. Always generate statistics and keep them up-to-date, for
both materialized views and their underlying tables.
. In an ideal world, it is beneficial for query rewrite, in
situations such as dimensional rollup, that all constraints be fully
enabled. Most significant are primary and foreign key constraints.
NULL-valued foreign keys can cause possible problems. In other
words, creating a properly structured data model, where primary and
foreign keys represent properly implemented relations, is the best
option to take. For example, the RELY constraint state allows use of
nonvalidated constraint values in materialized view query rewrites. A
situation like this is common, but from a purists perspective, and for
the hope of always having properly structured data, this option will
not always guarantee correct results.
Oracle Dimension Objects. These objects can be very useful for
query rewrite performance by allowing for explicit, multiple-level,
hierarchical dimensional structures.
Match Query Strings to Materialized Views. This is a sensible
approach, as long as the number and refresh rates of materialized
views are tightly controlled. Too many materialized views can cause
the same problems as too much indexing, except they will be much
worse because materialized views are that much bigger than indexes.
Aggregates. There are three points to make:
Preferably, only create materialized views with aggregates, for
there is little point in duplicating tables unless creating join mate-
rialized views.
If not using anything other than simple aggregation functions,
always include SUM and COUNT. If using more complex func-
tions, try to include their subfunctions as well whenever possible.
Frequently executed big nasty calculations, which revert to tables
and avoid query rewrite altogether, completely miss the point
entirely of creating potentially refresh processor-hogging material-
ized views.
By all means create aggregations with the most granularity possi-
ble, but if less detail is consistently required, consider using nested

Get Oracle Data Warehouse Tuning for 10g now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.