Database Reference
In-Depth Information
•
General query rewrite
: To find a matching materialized view, general query rewrite does a
semantic analysis. For that purpose, it makes extensive use of constraints and dimensions
to infer the semantic relations between data in the base tables. The purpose is to apply
query rewrite even if the query passed to the query optimizer is quite different from the one
associated with the matching materialized view. In fact, it's quite common for a well-designed
materialized view to be used to rewrite many (and possibly quite different) SQL statements.
DIMeNSIONS
The query optimizer uses constraints stored in the data dictionary to infer data relations that enable general
query rewrite to be used to the fullest extent possible. Sometimes, other very useful relationships, not covered
by constraints, exist between columns in the same table or even in different tables. This is especially true for
denormalized tables (such as the
times
table in the
sh
schema). To provide such information to the query
optimizer, it's possible to use a
dimension
. Because of it, it's possible to specify 1:n relations with
hierarchies
and
1:1 relations with
attributes
. Both hierarchies and attributes are based on
levels
, which are, simply put, columns
in a table. The following SQL statement illustrates this:
CREATE DIMENSION times_dim
LEVEL day IS times.time_id
LEVEL month IS times.calendar_month_desc
LEVEL quarter IS times.calendar_quarter_desc
LEVEL year IS times.calendar_year
HIERARCHY cal_rollup (day CHILD OF month CHILD OF quarter CHILD OF year)
ATTRIBUTE day DETERMINES (day_name, day_number_in_month)
ATTRIBUTE month DETERMINES (calendar_month_number, calendar_month_name)
ATTRIBUTE quarter DETERMINES (calendar_quarter_number)
ATTRIBUTE year DETERMINES (days_in_cal_year)
detailed information about dimensions is available in the
Oracle Database Data Warehousing Guide
manual.
Full-text-match and partial-text-match query rewrites can be applied very quickly. But because their decisions
are based on a simple text match, they aren't very flexible. As a result, they can only rewrite a limited number of
queries. In contrast, general query rewrite is much more powerful. The downside is that the overhead of applying it
is much higher. For this reason, the query optimizer applies the methods in an increasing order of complexity (and
thereby parse overhead) until a matching materialized view is found. This process is illustrated in Figure
15-4
.
