12 Advanced Query Rewrite for Materialized Views
This chapter discusses advanced query rewrite topics in Oracle, and contains:
12.1 How Oracle Rewrites Queries
The optimizer uses a number of different methods to rewrite a query. The first step in determining whether query rewrite is possible is to see if the query satisfies the following prerequisites:
-
Joins present in the materialized view are present in the SQL.
-
There is sufficient data in the materialized view(s) to answer the query.
After that, it must determine how it will rewrite the query. The simplest case occurs when the result stored in a materialized view exactly matches what is requested by a query. The optimizer makes this type of determination by comparing the text of the query with the text of the materialized view definition. This text match method is most straightforward but the number of queries eligible for this type of query rewrite is minimal.
When the text comparison test fails, the optimizer performs a series of generalized checks based on the joins, selections, grouping, aggregates, and column data fetched. This is accomplished by individually comparing various clauses (SELECT
, FROM
, WHERE
, HAVING
, or GROUP
BY
) of a query with those of a materialized view.
You can use the following types of query rewrite: Query Rewrite Method 1: Text Match Rewrite or General Query Rewrite Methods.
This following topics discuss the optimizer in more detail:
12.1.1 About Cost-Based Optimization and Query Rewrite
When a query is rewritten, Oracle's cost-based optimizer compares the cost of the rewritten query and original query and chooses the cheaper execution plan.
Query rewrite is available with cost-based optimization. Oracle Database optimizes the input query with and without rewrite and selects the least costly alternative. The optimizer rewrites a query by rewriting one or more query blocks, one at a time.
If query rewrite has a choice between several materialized views to rewrite a query block, it selects the ones which can result in reading in the least amount of data. After a materialized view has been selected for a rewrite, the optimizer then tests whether the rewritten query can be rewritten further with other materialized views. This process continues until no further rewrites are possible. Then the rewritten query is optimized and the original query is optimized. The optimizer compares these two optimizations and selects the least costly alternative.
Because optimization is based on cost, it is important to collect statistics both on tables involved in the query and on the tables representing materialized views. Statistics are fundamental measures, such as the number of rows in a table, that are used to calculate the cost of a rewritten query. They are created by using the DBMS_STATS
package.
Queries that contain inline or named views are also candidates for query rewrite. When a query contains a named view, the view name is used to do the matching between a materialized view and the query. When a query contains an inline view, the inline view can be merged into the query before matching between a materialized view and the query occurs.
Figure 12-1 presents a graphical view of the cost-based approach used during the rewrite process.
12.1.2 General Query Rewrite Methods
The optimizer has a number of different types of query rewrite methods that it can choose from to answer a query. When text match rewrite is not possible, this group of rewrite methods is known as general query rewrite. The advantage of using these more advanced techniques is that one or more materialized views can be used to answer a number of different queries and the query does not always have to match the materialized view exactly for query rewrite to occur.
When using general query rewrite methods, the optimizer uses data relationships on which it can depend, such as primary and foreign key constraints and dimension objects. For example, primary key and foreign key relationships tell the optimizer that each row in the foreign key table joins with at most one row in the primary key table. Furthermore, if there is a NOT
NULL
constraint on the foreign key, it indicates that each row in the foreign key table must join to exactly one row in the primary key table. A dimension object describes the relationship between, say, day, months, and year, which can be used to roll up data from the day to the month level.
Data relationships such as these are very important for query rewrite because they tell what type of result is produced by joins, grouping, or aggregation of data. Therefore, to maximize the rewritability of a large set of queries when such data relationships exist in a database, you should declare constraints and dimensions.
12.1.2.1 When are Constraints and Dimensions Needed for Query Rewrite?
Table 12-1 illustrates when dimensions and constraints are required for different types of query rewrite. These types of query rewrite are described throughout this chapter.
Table 12-1 Dimension and Constraint Requirements for Query Rewrite
Query Rewrite Types | Dimensions | Primary Key/Foreign Key/Not Null Constraints |
---|---|---|
Matching SQL Text |
Not Required |
Not Required |
Join Back |
Required OR |
Required |
Aggregate Computability |
Not Required |
Not Required |
Aggregate Rollup |
Not Required |
Not Required |
Rollup Using a Dimension |
Required |
Not Required |
Filtering the Data |
Not Required |
Not Required |
PCT Rewrite |
Not Required |
Not Required |
Multiple Materialized Views |
Not Required |
Not Required |
12.1.3 About Checks Made by Query Rewrite
12.1.3.1 Join Compatibility Check for Query Rewrite
In this check, the joins in a query are compared against the joins in a materialized view. In general, this comparison results in the classification of joins into three categories:
-
Common joins that occur in both the query and the materialized view. These joins form the common subgraph.
See Common Joins.
-
Delta joins that occur in the query but not in the materialized view. These joins form the query delta subgraph.
See Query Delta Joins.
-
Delta joins that occur in the materialized view but not in the query. These joins form the materialized view delta subgraph.
These can be visualized as shown in Figure 12-2.
See Also:
12.1.3.1.1 Common Joins
The common join pairs between the two must be of the same type, or the join in the query must be derivable from the join in the materialized view. For example, if a materialized view contains an outer join of table A
with table B
, and a query contains an inner join of table A
with table B
, the result of the inner join can be derived by filtering the antijoin rows from the result of the outer join. For example, consider the following query:
SELECT p.prod_name, t.week_ending_day, SUM(s.amount_sold) FROM sales s, products p, times t WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id AND mv.week_ending_day BETWEEN TO_DATE('01-AUG-1999', 'DD-MON-YYYY') AND TO_DATE('10-AUG-1999', 'DD-MON-YYYY') GROUP BY p.prod_name, mv.week_ending_day;
The common joins between this query and the materialized view join_sales_time_product_mv
are:
s.time_id = t.time_id AND s.prod_id = p.prod_id
They match exactly and the query can be rewritten as follows:
SELECT p.prod_name, mv.week_ending_day, SUM(s.amount_sold) FROM join_sales_time_product_mv WHERE mv.week_ending_day BETWEEN TO_DATE('01-AUG-1999','DD-MON-YYYY') AND TO_DATE('10-AUG-1999','DD-MON-YYYY') GROUP BY mv.prod_name, mv.week_ending_day;
The query could also be answered using the join_sales_time_product_oj_mv
materialized view where inner joins in the query can be derived from outer joins in the materialized view. The rewritten version (transparently to the user) filters out the antijoin rows. The rewritten query has the following structure:
SELECT mv.prod_name, mv.week_ending_day, SUM(mv.amount_sold) FROM join_sales_time_product_oj_mv mv WHERE mv.week_ending_day BETWEEN TO_DATE('01-AUG-1999','DD-MON-YYYY') AND TO_DATE('10-AUG-1999','DD-MON-YYYY') AND mv.prod_id IS NOT NULL GROUP BY mv.prod_name, mv.week_ending_day;
In general, if you use an outer join in a materialized view containing only joins, you should put in the materialized view either the primary key or the rowid on the right side of the outer join. For example, in the previous example, join_sales_time_product_oj_mv
, there is a primary key on both sales
and products
.
Another example of when a materialized view containing only joins is used is the case of a semijoin rewrites. That is, a query contains either an EXISTS
or an IN
subquery with a single table. Consider the following query, which reports the products that had sales greater than $1,000:
SELECT DISTINCT p.prod_name FROM products p WHERE EXISTS (SELECT p.prod_id, SUM(s.amount_sold) FROM sales s WHERE p.prod_id=s.prod_id HAVING SUM(s.amount_sold) > 1000) GROUP BY p.prod_id);
This query could also be represented as:
SELECT DISTINCT p.prod_name FROM products p WHERE p.prod_id IN (SELECT s.prod_id FROM sales s WHERE s.amount_sold > 1000);
This query contains a semijoin (s.prod_id = p.prod_id
) between the products
and the sales
table.
This query can be rewritten to use either the join_sales_time_product_mv
materialized view, if foreign key constraints are active or join_sales_time_product_oj_mv
materialized view, if primary keys are active. Observe that both materialized views contain s.prod_id=p.prod_id
, which can be used to derive the semijoin in the query. The query is rewritten with join_sales_time_product_mv
as follows:
SELECT mv.prod_name FROM (SELECT DISTINCT mv.prod_name FROM join_sales_time_product_mv mv WHERE mv.amount_sold > 1000);
If the materialized view join_sales_time_product_mv
is partitioned by time_id
, then this query is likely to be more efficient than the original query because the original join between sales
and products
has been avoided. The query could be rewritten using join_sales_time_product_oj_mv
as follows.
SELECT mv.prod_name FROM (SELECT DISTINCT mv.prod_name FROM join_sales_time_product_oj_mv mv WHERE mv.amount_sold > 1000 AND mv.prod_id IS NOT NULL);
Rewrites with semi-joins are restricted to materialized views with joins only and are not possible for materialized views with joins and aggregates.
See Also:
12.1.3.1.2 Query Delta Joins
A query
delta
join is a join that appears in the query but not in the materialized view. Any
number and type of delta joins in a query are allowed and they are simply retained when
the query is rewritten with a materialized view. In order for the retained join to work,
the materialized view must contain the joining key. Upon rewrite, the materialized view
is joined to the appropriate tables in the query delta. For example, consider the
following query against the sh
sample schema.
SELECT p.prod_name, t.week_ending_day, c.cust_city, SUM(s.amount_sold) FROM sh.sales s, sh.products p, sh.times t, sh.customers c WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY p.prod_name, t.week_ending_day, c.cust_city;
Using the materialized view join_sales_time_product_mv
, common joins are: s.time_id=t.time_id
and s.prod_id=p.prod_id
. The delta join in the query is s.cust_id=c.cust_id
. The rewritten form then joins the join_sales_time_product_mv
materialized view with the customers
table as follows:
SELECT mv.prod_name, mv.week_ending_day, c.cust_city, SUM(mv.amount_sold) FROM join_sales_time_product_mv mv, customers c WHERE mv.cust_id = c.cust_id GROUP BY mv.prod_name, mv.week_ending_day, c.cust_city;
See Also:
12.1.3.1.3 Materialized View Delta Joins
A materialized view delta join is a join that appears in the materialized view but not the query. All delta joins in a materialized view are required to be lossless with respect to the result of common joins. A lossless join guarantees that the result of common joins is not restricted. A lossless join is one where, if two tables called A
and B
are joined together, rows in table A
will always match with rows in table B
and no data will be lost, hence the term lossless join. For example, every row with the foreign key matches a row with a primary key provided no nulls are allowed in the foreign key. Therefore, to guarantee a lossless join, it is necessary to have FOREIGN
KEY
, PRIMARY
KEY
, and NOT
NULL
constraints on appropriate join keys. Alternatively, if the join between tables A
and B
is an outer join (A
being the outer table), it is lossless as it preserves all rows of table A
.
All delta joins in a materialized view are required to be non-duplicating with respect to the result of common joins. A non-duplicating join guarantees that the result of common joins is not duplicated. For example, a non-duplicating join is one where, if table A
and table B
are joined together, rows in table A
will match with at most one row in table B
and no duplication occurs. To guarantee a non-duplicating join, the key in table B
must be constrained to unique values by using a primary key or unique constraint.
Consider the following query that joins sales
and
times
in the sh
sample schema:
SELECT t.week_ending_day, SUM(s.amount_sold) FROM sh.sales s, sh.times t WHERE s.time_id = t.time_id AND t.week_ending_day BETWEEN TO_DATE ('01-MAR-2022', 'DD-MON-YYYY') AND TO_DATE('10-AUG-2022', 'DD-MON-YYYY') GROUP BY week_ending_day;
The materialized view join_sales_time_product_mv
has an additional join (s.prod_id=p.prod_id
) between sales
and products
. This is the delta join in join_sales_time_product_mv
. You can rewrite the query if this join is lossless and non-duplicating. This is the case if s.prod_id
is a foreign key to p.prod_id
and is not null. The query is therefore rewritten as:
SELECT week_ending_day, SUM(amount_sold) FROM join_sales_time_product_mv WHERE week_ending_day BETWEEN TO_DATE('01-MAR-2022', 'DD-MON-YYYY') AND TO_DATE('10-AUG-2022', 'DD-MON-YYYY') GROUP BY week_ending_day;
The query can also be rewritten with the materialized view join_sales_time_product_mv_oj
where foreign key constraints are not needed. This view contains an outer join (s.prod_id=p.prod_id
(+)) between sales
and products
. This makes the join lossless. If p.prod_id
is a primary key, then the non-duplicating condition is satisfied as well and optimizer rewrites the query as follows:
SELECT week_ending_day, SUM(amount_sold) FROM join_sales_time_product_oj_mv WHERE week_ending_day BETWEEN TO_DATE('01-AUG-1999', 'DD-MON-YYYY') AND TO_DATE('10-AUG-1999', 'DD-MON-YYYY') GROUP BY week_ending_day;
The query can also be rewritten with the materialized view join_sales_time_product_mv_oj
where foreign key constraints are not needed. This view contains an outer join (s.prod_id=p.prod_id
(+)) between sales
and products
. This makes the join lossless. If p.prod_id
is a primary key, then the non-duplicating condition is satisfied as well and optimizer rewrites the query as follows:
SELECT week_ending_day, SUM(amount_sold) FROM join_sales_time_product_oj_mv WHERE week_ending_day BETWEEN TO_DATE('01-MAR-2022', 'DD-MON-YYYY') AND TO_DATE('10-AUG-2022', 'DD-MON-YYYY') GROUP BY week_ending_day;
Note that the outer join in the definition of join_sales_time_product_mv_oj
is not necessary because the primary key - foreign key relationship between sales
and products
in the sh
schema is already lossless. It is used for demonstration purposes only, and would be necessary if sales.prod_id
were nullable, thus violating the losslessness of the join condition sales.prod_id = products.prod_id
.
Current limitations restrict most rewrites with outer joins to materialized views with joins only. There is limited support for rewrites with materialized aggregate views with outer joins, so those materialized views should rely on foreign key constraints to assure losslessness of materialized view delta joins.
See Also:
12.1.3.1.4 Join Equivalence Recognition
Query rewrite is able to make many transformations based upon the recognition of equivalent joins. Query rewrite recognizes the following construct as being equivalent to a join:
WHERE table1.column1 = F(args) /* sub-expression A */ AND table2.column2 = F(args) /* sub-expression B */
If F(args)
is a PL/SQL function that is declared to be deterministic and the arguments to both invocations of F
are the same, then the combination of subexpression A
with subexpression B
be can be recognized as a join between table1.column1
and table2.column2
. That is, the following expression is equivalent to the previous expression:
WHERE table1.column1 = F(args) /* sub-expression A */ AND table2.column2 = F(args) /* sub-expression B */ AND table1.column1 = table2.column2 /* join-expression J */
Because join-expression J
can be inferred from sub-expression A
and subexpression B
, the inferred join can be used to match a corresponding join of table1.column1 = table2.column2
in a materialized view.
12.1.3.2 Data Sufficiency Check for Query Rewrite
In this check, the optimizer determines if the
necessary column data requested by a query can be obtained from a materialized view. For
this, the equivalence of one column with another is used. For example, if an inner join
between table A
and table B
is based on a join
predicate A.X = B.X
, then the data in column A.X
equals the data in column B.X
in the result of the join. This data
property is used to match column A.X
in a query with column
B.X
in a materialized view or vice versa. For example, consider the
following query against the sh
sample schema:
SELECT p.prod_name, s.time_id, t.week_ending_day, SUM(s.amount_sold) FROM sh.sales s, sh.products p, sh.times t WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id GROUP BY p.prod_name, s.time_id, t.week_ending_day;
This query can be answered with join_sales_time_product_mv
even though the materialized view does not have s.time_id
. Instead, it has t.time_id
, which, through a join condition s.time_id=t.time_id
, is equivalent to s.time_id
. Thus, the optimizer might select the following rewrite:
SELECT prod_name, time_id, week_ending_day, SUM(amount_sold) FROM join_sales_time_product_mv GROUP BY prod_name, time_id, week_ending_day;
12.1.3.3 Grouping Compatibility Check for Query Rewrite
This check is required only if both the materialized view and the query contain a GROUP
BY
clause. The optimizer first determines if the grouping of data requested by a query is exactly the same as the grouping of data stored in a materialized view. In other words, the level of grouping is the same in both the query and the materialized view. If the materialized views groups on all the columns and expressions in the query and also groups on additional columns or expressions, query rewrite can reaggregate the materialized view over the grouping columns and expressions of the query to derive the same result requested by the query.
12.1.3.4 Aggregate Computability Check for Query Rewrite
This check is required only if both the query and the materialized view contain aggregates. Here the optimizer determines if the aggregates requested by a query can be derived or computed from one or more aggregates stored in a materialized view. For example, if a query requests AVG(X)
and a materialized view contains SUM(X)
and COUNT(X)
, then AVG(X)
can be computed as SUM(X)/COUNT(X)
.
If the grouping compatibility check determined that the rollup of aggregates stored in a materialized view is required, then the aggregate computability check determines if it is possible to roll up each aggregate requested by the query using aggregates in the materialized view.
12.1.4 About Query Rewrite Using Dimensions
This section discusses the following aspects of using dimensions in a rewrite environment:
12.1.4.1 Benefits of Using Dimensions in a Query Rewrite Environment
A dimension defines a hierarchical (parent/child) relationships between columns, where all the columns do not have to come from the same table.
Dimension definitions increase the possibility of query rewrite because they help to establish functional dependencies between the columns. In addition, dimensions can express intra-table relationships that cannot be expressed by constraints. A dimension definition does not occupy additional storage. Rather, a dimension definition establishes metadata that describes the intra- and inter-dimensional relationships within your schema. Before creating a materialized view, the first step is to review the schema and define the dimensions as this can significantly improve the chances of rewriting a query.
12.1.4.2 How to Define Dimensions for Query Rewrite
For any given schema, use the following steps to create dimensions:
- Identify all dimensions and dimension tables in the schema
- Identify the hierarchies within each dimension
- Identify the attribute dependencies within each level of the hierarchy
- Identify joins from the fact table in a data warehouse to each dimension
Remember to set the parameter QUERY_REWRITE_INTEGRITY
to TRUSTED
or STALE_TOLERATED
for query rewrite to take advantage of the relationships declared in dimensions.
Identify all dimensions and dimension tables in the schema
If the dimensions are normalized, that is, stored in multiple tables, then check that a join between the dimension tables guarantees that each child-side row joins with one and only one parent-side row. In the case of denormalized dimensions, check that the child-side columns uniquely determine the parent-side (or attribute) columns. Failure to abide by these rules may result in incorrect results being returned from queries.
Identify the hierarchies within each dimension
As an example, day is a child of month (you can aggregate day level data up to month), and quarter is a child of year.
Identify the attribute dependencies within each level of the hierarchy
As an example, identify that calendar_month_name
is an attribute of month.
Identify joins from the fact table in a data warehouse to each dimension
Then check that each join can guarantee that each fact row joins with one and only one dimension row. This condition must be declared, and optionally enforced, by adding FOREIGN
KEY
and NOT
NULL
constraints on the fact key columns and PRIMARY
KEY
constraints on the parent-side join keys. If these relationships can be guaranteed by other data handling procedures (for example, your load process), these constraints can be enabled using the NOVALIDATE
option to avoid the time required to validate that every row in the table conforms to the constraints. The RELY
clause is also required for all nonvalidated constraints to make them eligible for use in query rewrite.
12.1.4.2.1 Example SQL Statement to Create Time Dimensions
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 LEVEL fis_week IS TIMES.WEEK_ENDING_DAY LEVEL fis_month IS TIMES.FISCAL_MONTH_DESC LEVEL fis_quarter IS TIMES.FISCAL_QUARTER_DESC LEVEL fis_year IS TIMES.FISCAL_YEAR HIERARCHY cal_rollup (day CHILD OF month CHILD OF quarter CHILD OF year) HIERARCHY fis_rollup (day CHILD OF fis_week CHILD OF fis_month CHILD OF fis_quarter CHILD OF fis_year) ATTRIBUTE day DETERMINES (day_number_in_week, day_name, day_number_in_month, calendar_week_number) ATTRIBUTE month DETERMINES (calendar_month_desc, calendar_month_number, calendar_month_name, days_in_cal_month, end_of_cal_month) ATTRIBUTE quarter DETERMINES (calendar_quarter_desc, calendar_quarter_number,days_in_cal_quarter, end_of_cal_quarter) ATTRIBUTE year DETERMINES (calendar_year, days_in_cal_year, end_of_cal_year) ATTRIBUTE fis_week DETERMINES (week_ending_day, fiscal_week_number);
12.2 Types of Query Rewrite
Queries that have aggregates that require computations over a large number of rows or joins between very large tables can be expensive and thus can take a long time to return the results. Query rewrite transparently rewrites such queries using materialized views that have pre-computed results, so that the queries can be answered almost instantaneously. These materialized views can be broadly categorized into two groups, namely materialized aggregate views and materialized join views. Materialized aggregate views are tables that have pre-computed aggregate values for columns from original tables. Similarly, materialized join views are tables that have pre-computed joins between columns from original tables. Query rewrite transforms an incoming query to fetch the results from materialized view columns. Because these columns contain already pre-computed results, the incoming query can be answered almost instantaneously.
This section discusses the following methods that can be used to rewrite a query:
12.2.1 Query Rewrite Method 1: Text Match Rewrite
The query rewrite engine always initially tries to compare the text of incoming query with the text of the definition of any potential materialized views to rewrite the query. This is because the overhead of doing a simple text comparison is usually negligible comparing to the cost of doing a complex analysis required for the general rewrite.
The query rewrite engine uses two text match methods, full text match rewrite and
partial text match rewrite. In full text match the entire text of a query is compared against
the entire text of a materialized view definition (that is, the entire SELECT
expression), ignoring the white space during text comparison. For example, assume that you
have the following materialized view,
sum_sales_pscat_month_city_mv
:
CREATE MATERIALIZED VIEW sum_sales_pscat_month_city_mv ENABLE QUERY REWRITE AS SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold, COUNT(s.amount_sold) AS count_amount_sold FROM sales s, products p, times t, customers c WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id AND s.cust_id=c.cust_id GROUP BY p.prod_subcategory, t.calendar_month_desc, c.cust_city;
Consider the following query:
SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold, COUNT(s.amount_sold) AS count_amount_sold FROM sales s, products p, times t, customers c WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id AND s.cust_id=c.cust_id GROUP BY p.prod_subcategory, t.calendar_month_desc, c.cust_city;
This query matches sum_sales_pscat_month_city_mv
(white space excluded) and is rewritten as:
SELECT mv.prod_subcategory, mv.calendar_month_desc, mv.cust_city, mv.sum_amount_sold, mv.count_amount_sold FROM sum_sales_pscat_month_city_mv;
When full text match fails, the optimizer then attempts a partial text match. In this method, the text starting from the FROM
clause of a query is compared against the text starting with the FROM
clause of a materialized view definition. Therefore, the following query can be rewritten:
SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city, AVG(s.amount_sold) FROM sales s, products p, times t, customers c WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id AND s.cust_id=c.cust_id GROUP BY p.prod_subcategory, t.calendar_month_desc, c.cust_city;
This query is rewritten as:
SELECT mv.prod_subcategory, mv.calendar_month_desc, mv.cust_city, mv.sum_amount_sold/mv.count_amount_sold FROM sum_sales_pscat_month_city_mv mv;
Note that, under the partial text match rewrite method, the average of sales aggregate required by the query is computed using the sum of sales and count of sales aggregates stored in the materialized view.
When neither text match succeeds, the optimizer uses a general query rewrite method.
Text match rewrite can distinguish contexts where the difference between uppercase and lowercase is significant and where it is not. For example, the following statements are equivalent:
SELECT X, 'aBc' FROM Y Select x, 'aBc' From y
12.2.2 Query Rewrite Method 2: Join Back
If some column data requested by a query cannot be obtained from a materialized view, the optimizer further determines if it can be obtained based on a data relationship called a functional dependency. When the data in a column can determine data in another column, such a relationship is called a functional dependency or functional determinance. For example, if a table contains a primary key column called prod_id
and another column called prod_name
, then, given a prod_id
value, it is possible to look up the corresponding prod_name
. The opposite is not true, which means a prod_name
value need not relate to a unique prod_id
.
When the column data required by a query is not available from a materialized view, such column data can still be obtained by joining the materialized view back to the table that contains required column data provided the materialized view contains a key that functionally determines the required column data. For example, consider the following query:
SELECT p.prod_category, t.week_ending_day, SUM(s.amount_sold) FROM sales s, products p, times t WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id AND p.prod_category='Golf' GROUP BY p.prod_category, t.week_ending_day;
The materialized view sum_sales_prod_week_mv
contains p.prod_id
, but not p.prod_category
. However, you can join sum_sales_prod_week_mv
back to products
to retrieve prod_category
because prod_id
functionally determines prod_category
. The optimizer rewrites this query using sum_sales_prod_week_mv
as follows:
SELECT p.prod_category, mv.week_ending_day, SUM(mv.sum_amount_sold) FROM sum_sales_prod_week_mv mv, products p WHERE mv.prod_id=p.prod_id AND p.prod_category='Golf' GROUP BY p.prod_category, mv.week_ending_day;
Here the products
table is called a joinback table because it was originally joined in the materialized view but joined again in the rewritten query.
You can declare functional dependency in two ways:
-
Using the primary key constraint (as shown in the previous example)
-
Using the
DETERMINES
clause of a dimension
The DETERMINES
clause of a dimension definition might be the only way you could declare functional dependency when the column that determines another column cannot be a primary key. For example, the products
table is a denormalized dimension table that has columns prod_id
, prod_name
, and prod_subcategory
that functionally determines prod_subcat_desc
and prod_category
that determines prod_cat_desc
.
The first functional dependency can be established by declaring prod_id
as the primary key, but not the second functional dependency because the prod_subcategory
column contains duplicate values. In this situation, you can use the DETERMINES
clause of a dimension to declare the second functional dependency.
The following dimension definition illustrates how functional dependencies are declared:
CREATE DIMENSION products_dim LEVEL product IS (products.prod_id) LEVEL subcategory IS (products.prod_subcategory) LEVEL category IS (products.prod_category) HIERARCHY prod_rollup ( product CHILD OF subcategory CHILD OF category ) ATTRIBUTE product DETERMINES products.prod_name ATTRIBUTE product DETERMINES products.prod_desc ATTRIBUTE subcategory DETERMINES products.prod_subcat_desc ATTRIBUTE category DETERMINES products.prod_cat_desc;
The hierarchy prod_rollup
declares hierarchical relationships that are also 1:n
functional dependencies. The 1:1
functional dependencies are declared using the DETERMINES
clause, as seen when prod_subcategory
functionally determines prod_subcat_desc
.
If the following materialized view is created:
CREATE MATERIALIZED VIEW sum_sales_pscat_week_mv ENABLE QUERY REWRITE AS SELECT p.prod_subcategory, t.week_ending_day, SUM(s.amount_sold) AS sum_amount_sole FROM sales s, products p, times t WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id GROUP BY p.prod_subcategory, t.week_ending_day;
Then consider the following query:
SELECT p.prod_subcategory_desc, t.week_ending_day, SUM(s.amount_sold) FROM sales s, products p, times t WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id AND p.prod_subcat_desc LIKE '%Men' GROUP BY p.prod_subcat_desc, t.week_ending_day;
This can be rewritten by joining sum_sales_pscat_week_mv
to the products
table so that prod_subcat_desc
is available to evaluate the predicate. However, the join is based on the prod_subcategory
column, which is not a primary key in the products
table; therefore, it allows duplicates. This is accomplished by using an inline view that selects distinct values and this view is joined to the materialized view as shown in the rewritten query.
SELECT iv.prod_subcat_desc, mv.week_ending_day, SUM(mv.sum_amount_sold) FROM sum_sales_pscat_week_mv mv, (SELECT DISTINCT prod_subcategory, prod_subcat_desc FROM products) iv WHERE mv.prod_subcategory=iv.prod_subcategory AND iv.prod_subcat_desc LIKE '%Men' GROUP BY iv.prod_subcat_desc, mv.week_ending_day;
This type of rewrite is possible because prod_subcategory
functionally determines prod_subcategory_desc
as declared in the dimension.
12.2.3 Query Rewrite Method 3: Aggregate Computability
Query rewrite can also occur when the optimizer determines if the aggregates requested by a query can be derived or computed from one or more aggregates stored in a materialized view. For example, if a query requests AVG(X)
and a materialized view contains SUM(X)
and COUNT(X)
, then AVG(X)
can be computed as SUM(X)/COUNT(X)
.
In addition, if it is determined that the rollup of aggregates stored in a materialized view is required, then, if it is possible, query rewrite also rolls up each aggregate requested by the query using aggregates in the materialized view.
For example, SUM(sales)
at the city level can be rolled up to
SUM(sales)
at the state level by summing all
SUM(sales)
aggregates in a group with the same state value. However,
AVG(sales)
cannot be rolled up to a coarser level unless
COUNT(sales)
or SUM(sales)
is also available in the
materialized view. Similarly, VARIANCE(sales)
or
STDDEV(sales)
cannot be rolled up unless both
COUNT(sales)
and SUM(sales)
are also available in the
materialized view. For example, consider the following query from the sh
sample schema:
ALTER TABLE times MODIFY CONSTRAINT time_pk RELY; ALTER TABLE customers MODIFY CONSTRAINT customers_pk RELY; ALTER TABLE sales MODIFY CONSTRAINT sales_time_pk RELY; ALTER TABLE sales MODIFY CONSTRAINT sales_customer_fk RELY; SELECT p.prod_subcategory, AVG(s.amount_sold) AS avg_sales FROM sh.sales s, sh.products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_subcategory;
This statement can be rewritten with materialized view
sum_sales_pscat_month_city_mv
provided the joins between
sales
and times
and sales
and
customers
are lossless and non-duplicating. Further, the query groups
by prod_subcategory
whereas the materialized view groups by
prod_subcategory
, calendar_month_desc
and
cust_city
, which means the aggregates stored in the materialized view
have to be rolled up. The optimizer rewrites the query as the following:
SELECT mv.prod_subcategory, SUM(mv.sum_amount_sold)/COUNT(mv.count_amount_sold) AS avg_sales FROM sum_sales_pscat_month_city_mv mv GROUP BY mv.prod_subcategory;
The argument of an aggregate such as SUM
can be an arithmetic expression such as A+B
. The optimizer tries to match an aggregate SUM(A+B)
in a query with an aggregate SUM(A+B)
or SUM(B+A)
stored in a materialized view. In other words, expression equivalence is used when matching the argument of an aggregate in a query with the argument of a similar aggregate in a materialized view. To accomplish this, Oracle converts the aggregate argument expression into a canonical form such that two different but equivalent expressions convert into the same canonical form. For example, A*(B-C)
, A*B-C*A
, (B-C)*A
, and -A*C+A*B
all convert into the same canonical form and, therefore, they are successfully matched.
12.2.4 Query Rewrite Method 4: Aggregate Rollup
If the grouping of data requested by a query is at a coarser level than the grouping
of data stored in a materialized view, the optimizer can still use the materialized view
to rewrite the query. For example, the materialized view
sum_sales_pscat_week_mv
groups by prod_subcategory
and week_ending_day
. This query groups by
prod_subcategory
, a coarser grouping granularity:
ALTER TABLE times MODIFY CONSTRAINT time_pk RELY; ALTER TABLE sales MODIFY CONSTRAINT sales_time_fk RELY; SELECT p.prod_subcategory, SUM(s.amount_sold) AS sum_amount FROM sh.sales s, sh.products p WHERE s.prod_id=p.prod_id GROUP BY p.prod_subcategory;
Therefore, the optimizer rewrites this query as:
SELECT mv.prod_subcategory, SUM(mv.sum_amount_sold) FROM sum_sales_pscat_week_mv mv GROUP BY mv.prod_subcategory;
12.2.5 Query Rewrite Method 5: Rollup Using a Dimension
When reporting is required at different levels in a hierarchy, materialized views do not have to be created at each level in the hierarchy provided dimensions have been defined. This is because query rewrite can use the relationship information in the dimension to roll up the data in the materialized view to the required level in the hierarchy.
In the following example, a query requests data grouped by prod_category
while a materialized view stores data grouped by prod_subcategory
. If prod_subcategory
is a CHILD
OF
prod_category
(see the dimension example earlier), the grouped data stored in the materialized view can be further grouped by prod_category
when the query is rewritten. In other words, aggregates at prod_subcategory
level (finer granularity) stored in a materialized view can be rolled up into aggregates at prod_category
level (coarser granularity).
For example, consider the following sh
sample schema query:
SELECT p.prod_category, t.week_ending_day, SUM(s.amount_sold) AS sum_amount FROM sh.sales s, sh.products p, sh.times t WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id GROUP BY p.prod_category, t.week_ending_day;
Because prod_subcategory
functionally determines
prod_category
, sum_sales_pscat_week_mv
can be used with a
joinback to products
to retrieve prod_category
column data, and
then aggregates can be rolled up to prod_category
level, as shown in the
following:
SELECT pv.prod_category, mv.week_ending_day, SUM(mv.sum_amount_sold) FROM sum_sales_pscat_week_mv mv, (SELECT DISTINCT prod_subcategory, prod_category FROM products) pv WHERE mv.prod_subcategory= pv.prod_subcategory GROUP BY pv.prod_category, mv.week_ending_day;
12.2.6 Query Rewrite Method 6: When Materialized Views Have Only a Subset of Data
Oracle supports rewriting of queries so that they will use materialized views in which the HAVING
or WHERE
clause of the materialized view contains a selection of a subset of the data in a table or tables. For example, only those customers who live in New Hampshire. In other words, the WHERE
clause in the materialized view will be WHERE state = 'New Hampshire'
.
To perform this type of query rewrite, Oracle must determine if the data requested in the query is contained in, or is a subset of, the data stored in the materialized view. The following sections detail the conditions where Oracle can solve this problem and thus rewrite a query to use a materialized view that contains a filtered portion of the data in the detail table.
To determine if query rewrite can occur on filtered data, a selection computability check is performed when both the query and the materialized view contain selections (non-joins) and the check is done on the WHERE
as well as the HAVING
clause. If the materialized view contains selections and the query does not, then the selection compatibility check fails because the materialized view is more restrictive than the query. If the query has selections and the materialized view does not, then the selection compatibility check is not needed.
A materialized view's WHERE
or HAVING
clause can contain a join, a selection, or both, and still be used to rewrite a query. Predicate clauses containing expressions, or selecting rows based on the values of particular columns, are examples of non-join predicates.
This section contains the following topics:
12.2.6.1 Query Rewrite Definitions When Materialized Views Have Only a Subset of Data
Before describing what is possible when query rewrite works with only a subset of the data, the following definitions are useful:
-
join relop
Is one of the following
(=, <, <=, >, >=)
-
selection relop
Is one of the following
(=, <, <=, >, >=, !=, [NOT] BETWEEN | IN| LIKE |NULL)
-
join predicate
Is of the form
(
column1)
, where columns are from different tables within the sameFROM
clause in the current query block. So, for example, an outer reference is not possible. -
selection predicate
Is of the form left-hand-side-expression
color='red'
means the left-hand side iscolor
and the right-hand side is'red'
and the relational operator is(=)
.
12.2.6.2 Selection Categories When Materialized Views Have Only a Subset of Data
Selections are categorized into the following cases:
-
Simple
Simple selections are of the form expression relop constant.
-
Complex
Complex selections are of the form expression relop expression.
-
Range
Range selections are of a form such as
WHERE (cust_last_name BETWEEN 'abacrombe' AND 'anakin')
.Note that simple selections with relational operators
(<,<=,>,>=)
are also considered range selections. -
IN
-listsSingle and multi-column
IN
-lists such asWHERE(prod_id) IN (102, 233, ....)
.Note that selections of the form
(column1='v1' OR column1='v2' OR column1='v3' OR ....)
are treated as a group and classified as anIN
-list. -
IS [NOT] NULL
-
[NOT] LIKE
-
Other
Other selections are when it cannot determine the boundaries for the data. For example,
EXISTS
.
When comparing a selection from the query with a selection from the materialized view, the left-hand side of both selections are compared.
If the left-hand side selections match, then the right-hand side values are checked for containment. That is, the right-hand side values of the query selection must be contained by right-hand side values of the materialized view selection.
You can also use expressions in selection predicates. This process resembles the following:
expression relational operator constant
Where expression can be any arbitrary arithmetic expression allowed by the Oracle Database. The expression in the materialized view and the query must match. Oracle attempts to discern expressions that are logically equivalent, such as A+B
and B+A
, and always recognizes identical expressions as being equivalent.
You can also use queries with an expression on both sides of the operator or user-defined functions as operators. Query rewrite occurs when the complex predicate in the materialized view and the query are logically equivalent. This means that, unlike exact text match, terms could be in a different order and rewrite can still occur, as long as the expressions are equivalent.
12.2.6.3 Examples of Query Rewrite Selection
Here are a number of examples showing how query rewrite can still occur when the data is being filtered.
Example 12-1 Single Value Selection
If the query contains the following clause:
WHERE prod_id = 102
And, if a materialized view contains the following clause:
WHERE prod_id BETWEEN 0 AND 200
Then, the left-hand side selections match on prod_id
and the right-hand side value of the query 102
is within the range of the materialized view, so query rewrite is possible.
Example 12-2 Bounded Range Selection
A selection can be a bounded range (a range with an upper and lower value). For example, if the query contains the following clause:
WHERE prod_id > 10 AND prod_id < 50
And if a materialized view contains the following clause:
WHERE prod_id BETWEEN 0 AND 200
Then, the selections are matched on prod_id
and the query range is within the materialized view range. In this example, notice that both query selections are based on the same column.
Example 12-3 Selection With Expression
If the query contains the following clause:
WHERE (sales.amount_sold * .07) BETWEEN 1.00 AND 100.00
And if a materialized view contains the following clause:
WHERE (sales.amount_sold * .07) BETWEEN 0.0 AND 200.00
Then, the selections are matched on (sales.amount_sold *.07)
and the right-hand side value of the query is within the range of the materialized view, therefore query rewrite is possible. Complex selections such as this require that the left-hand side and the right-hand side be matched within range of the materialized view.
Example 12-4 Exact Match Selections
If the query contains the following clause:
WHERE (cost.unit_price * 0.95) > (cost_unit_cost * 1.25)
And if a materialized view contains the following:
WHERE (cost.unit_price * 0.95) > (cost_unit_cost * 1.25)
If the left-hand side and the right-hand side match the materialized view and the selection_relop is the same, then the selection can usually be dropped from the rewritten query. Otherwise, the selection must be kept to filter out extra data from the materialized view.
If query rewrite can drop the selection from the rewritten query, all columns from the selection may not have to be in the materialized view so more rewrites can be done. This ensures that the materialized view data is not more restrictive than the query.
Example 12-5 More Selection in the Query
Selections in the query do not have to be matched by any selections in the materialized view but, if they are, then the right-hand side values must be contained by the materialized view. For example, if the query contains the following clause:
WHERE prod_name = 'Shorts' AND prod_category = 'Men'
And if a materialized view contains the following clause:
WHERE prod_category = 'Men'
Then, in this example, only selection with prod_category
is matched. The query has an extra selection that is not matched but this is acceptable because if the materialized view selects prod_name
or selects a column that can be joined back to the detail table to get prod_name
, then query rewrite is possible. The only requirement is that query rewrite must have a way of applying the prod_name
selection to the materialized view.
Example 12-6 No Rewrite Because of Fewer Selections in the Query
If the query contains the following clause:
WHERE prod_category = 'Men'
And if a materialized view contains the following clause:
WHERE prod_name = 'Shorts' AND prod_category = 'Men'
Then, the materialized view selection with prod_name
is not matched. The materialized view is more restrictive that the query because it only contains the product Shorts, therefore, query rewrite does not occur.
Example 12-7 Multi-Column IN-List Selections
Query rewrite also checks for cases where the query has a multi-column IN
-list where the columns are fully matched by individual columns from the materialized view single column IN
-lists. For example, if the query contains the following:
WHERE (prod_id, cust_id) IN ((1022, 1000), (1033, 2000))
And if a materialized view contains the following:
WHERE prod_id IN (1022,1033) AND cust_id IN (1000, 2000)
Then, the materialized view IN
-lists are matched by the columns in the query multi-column IN
-list. Furthermore, the right-hand side values of the query selection are contained by the materialized view so that rewrite occurs.
Example 12-8 Selections Using IN-Lists
Selection compatibility also checks for cases where the materialized view has a multi-column IN
-list where the columns are fully matched by individual columns or columns from IN
-lists in the query. For example, if the query contains the following:
WHERE prod_id = 1022 AND cust_id IN (1000, 2000)
And if a materialized view contains the following:
WHERE (prod_id, cust_id) IN ((1022, 1000), (1022, 2000))
Then, the materialized view IN
-list columns are fully matched by the columns in the query selections. Furthermore, the right-hand side values of the query selection are contained by the materialized view. So rewrite succeeds.
Example 12-9 Multiple Selections or Expressions
If the query contains the following clause:
WHERE (city_population > 15000 AND city_population < 25000 AND state_name = 'New Hampshire')
And if a materialized view contains the following clause:
WHERE (city_population < 5000 AND state_name = 'New York') OR (city_population BETWEEN 10000 AND 50000 AND state_name = 'New Hampshire')
Then, the query is said to have a single disjunct (group of selections separated by AND
) and the materialized view has two disjuncts separated by OR
. The single query disjunct is contained by the second materialized view disjunct so selection compatibility succeeds. It is clear that the materialized view contains more data than needed by the query so the query can be rewritten.
12.2.6.4 About Handling of the HAVING Clause in Query Rewrite
Query rewrite can also occur when the query specifies a range of values for an
aggregate in the HAVING
clause, such as
SUM(s.amount_sold)
BETWEEN
10000
AND
20000
, as long as the range specified is within the range specified in
the materialized view.
CREATE MATERIALIZED VIEW product_sales_mv BUILD IMMEDIATE REFRESH FORCE ENABLE QUERY REWRITE AS SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales FROM sh.products p, sh.sales s WHERE p.prod_id = s.prod_id GROUP BY prod_name HAVING SUM(s.amount_sold) BETWEEN 5000 AND 50000;
Then, a query such as the following could be rewritten:
SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales FROM sh.products p, sh.sales s WHERE p.prod_id = s.prod_id GROUP BY prod_name HAVING SUM(s.amount_sold) BETWEEN 5000 AND 50000;
This query is rewritten as follows:
SELECT mv.prod_name, mv.dollar_sales FROM product_sales_mv mv WHERE mv.dollar_sales BETWEEN 5000 AND 50000;
12.2.6.5 About Query Rewrite When the Materialized View has an IN-List
You can use query rewrite when the materialized view contains an
IN
-list. For example, given the following materialized view
definition:
CREATE MATERIALIZED VIEW popular_promo_sales_mv BUILD IMMEDIATE REFRESH FORCE ENABLE QUERY REWRITE AS SELECT p.promo_name, SUM(s.amount_sold) AS sum_amount_sold FROM promotions p, sales s WHERE s.promo_id = p.promo_id AND p.promo_name IN ('coupon', 'premium', 'giveaway') GROUP BY promo_name;
The following query can be rewritten:
SELECT p.promo_name, SUM(s.amount_sold) FROM promotions p, sales s WHERE s.promo_id = p.promo_id AND p.promo_name IN ('coupon', 'premium') GROUP BY p.promo_name;
This query is rewritten as follows:
SELECT * FROM popular_promo_sales_mv mv WHERE mv.promo_name IN ('coupon', 'premium');
12.2.7 Partition Change Tracking (PCT) Rewrite
PCT rewrite enables the optimizer to accurately rewrite queries with fresh data using materialized views that are only partially fresh. To do so, Oracle Database keeps track of which partitions in the detail tables have been updated. Oracle Database then tracks which rows in the materialized view originate from the affected partitions in the detail tables. The optimizer is then able to use those portions of the materialized view that are known to be fresh. You can check details about freshness with the DBA_MVIEWS
, DBA_DETAIL_RELATIONS
, and DBA_MVIEW_DETAIL_PARTITION
views. See "Viewing Partition Freshness" for examples of using these views.
The optimizer uses PCT rewrite in QUERY_REWRITE_INTEGRITY = ENFORCED
and TRUSTED
modes. The optimizer does not use PCT rewrite in STALE_TOLERATED
mode because data freshness is not considered in that mode. Also, for PCT rewrite to occur, a WHERE
clause is required.
You can use PCT rewrite with partitioning, but hash partitioning is not supported. The following topics discuss aspects of using PCT:
12.2.7.1 PCT Rewrite Based on Range Partitioned Tables
The following example illustrates a PCT rewrite example where the materialized view is PCT enabled through partition key and the underlying base table is range partitioned on the time key.
CREATE TABLE part_sales_by_time (time_id, prod_id, amount_sold, quantity_sold) PARTITION BY RANGE (time_id) ( PARTITION old_data VALUES LESS THAN (TO_DATE('01-01-1999', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M), PARTITION quarter1 VALUES LESS THAN (TO_DATE('01-04-1999', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M), PARTITION quarter2 VALUES LESS THAN (TO_DATE('01-07-1999', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M), PARTITION quarter3 VALUES LESS THAN (TO_DATE('01-10-1999', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M), PARTITION quarter4 VALUES LESS THAN (TO_DATE('01-01-2000', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M), PARTITION max_partition VALUES LESS THAN (MAXVALUE) PCTFREE 0 STORAGE (INITIAL 8M) ) AS SELECT s.time_id, s.prod_id, s.amount_sold, s.quantity_sold FROM sales s;
Then create a materialized view that contains the total number of products sold by date.
CREATE MATERIALIZED VIEW sales_in_1999_mv BUILD IMMEDIATE REFRESH FORCE ON DEMAND ENABLE QUERY REWRITE AS SELECT s.time_id, s.prod_id, p.prod_name, SUM(quantity_sold) FROM part_sales_by_time s, products p WHERE p.prod_id = s.prod_id AND s.time_id BETWEEN TO_DATE('01-01-1999', 'DD-MM-YYYY') AND TO_DATE('31-12-1999', 'DD-MM-YYYY') GROUP BY s.time_id, s.prod_id, p.prod_name;
Note that the following query will be rewritten with materialized view sales_in_1999_mv
:
SELECT s.time_id, p.prod_name, SUM(quantity_sold) FROM part_sales_by_time s, products p WHERE p.prod_id = s.prod_id AND s.time_id < TO_DATE('01-02-1999', 'DD-MM-YYYY') AND s.time_id >= TO_DATE('01-01-1999', 'DD-MM-YYYY') GROUP BY s.time_id, p.prod_name;
If you add a row to quarter4
in part_sales_by_time
as:
INSERT INTO part_sales_by_time VALUES (TO_DATE('26-12-1999', 'DD-MM-YYYY'),38920,2500, 20); commit;
Then the materialized view sales_in_1999_mv
becomes stale. With PCT rewrite, you can rewrite queries that request data from only the fresh portions of the materialized view. Note that because the materialized view sales_in_1999_mv
has the time_id
in its SELECT
and GROUP
BY
clause, it is PCT enabled so the following query will be rewritten successfully as no data from quarter4
is requested.
SELECT s.time_id, p.prod_name, SUM(quantity_sold) FROM part_sales_by_time s, products p WHERE p.prod_id = s.prod_id AND s.time_id < TO_DATE('01-07-1999', 'DD-MM-YYYY') AND s.time_id >= TO_DATE('01-03-1999', 'DD-MM-YYYY') GROUP BY s.time_id, p.prod_name;
The following query cannot be rewritten if multiple materialized view rewrite is set to off. Because multiple materialized view rewrite is on by default, the following query is rewritten with materialized view and base tables:
SELECT s.time_id, p.prod_name, SUM(quantity_sold) FROM part_sales_by_time s, products p WHERE p.prod_id = s.prod_id AND s.time_id < TO_DATE('31-10-1999', 'DD-MM-YYYY') AND s.time_id > TO_DATE('01-07-1999', 'DD-MM-YYYY') GROUP BY s.time_id, p.prod_name;
12.2.7.2 PCT Rewrite Based on Range-List Partitioned Tables
If the detail table is range-list partitioned, a materialized view that depends on this detail table can support PCT at both the partitioning and subpartitioning levels. If both the partition and subpartition keys are present in the materialized view, PCT can be done at a finer granularity; materialized view refreshes can be done to smaller portions of the materialized view and more queries could be rewritten with a stale materialized view. Alternatively, if only the partition key is present in the materialized view, PCT can be done with courser granularity.
Consider the following range-list partitioned table:
CREATE TABLE sales_par_range_list (calendar_year, calendar_month_number, day_number_in_month, country_name, prod_id, prod_name, quantity_sold, amount_sold) PARTITION BY RANGE (calendar_month_number) SUBPARTITION BY LIST (country_name) (PARTITION q1 VALUES LESS THAN (4) (SUBPARTITION q1_America VALUES ('United States of America', 'Argentina'), SUBPARTITION q1_Asia VALUES ('Japan', 'India'), SUBPARTITION q1_Europe VALUES ('France', 'Spain', 'Ireland')), PARTITION q2 VALUES LESS THAN (7) (SUBPARTITION q2_America VALUES ('United States of America', 'Argentina'), SUBPARTITION q2_Asia VALUES ('Japan', 'India'), SUBPARTITION q2_Europe VALUES ('France', 'Spain', 'Ireland')), PARTITION q3 VALUES LESS THAN (10) (SUBPARTITION q3_America VALUES ('United States of America', 'Argentina'), SUBPARTITION q3_Asia VALUES ('Japan', 'India'), SUBPARTITION q3_Europe VALUES ('France', 'Spain', 'Ireland')), PARTITION q4 VALUES LESS THAN (13) (SUBPARTITION q4_America VALUES ('United States of America', 'Argentina'), SUBPARTITION q4_Asia VALUES ('Japan', 'India'), SUBPARTITION q4_Europe VALUES ('France', 'Spain', 'Ireland'))) AS SELECT t.calendar_year, t.calendar_month_number, t.day_number_in_month, c1.country_name, s.prod_id, p.prod_name, s.quantity_sold, s.amount_sold FROM times t, countries c1, products p, sales s, customers c2 WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id AND s.cust_id = c2.cust_id AND c2.country_id = c1.country_id AND c1.country_name IN ('United States of America', 'Argentina', 'Japan', 'India', 'France', 'Spain', 'Ireland');
Then consider the following materialized view sum_sales_per_year_month_mv
, which has the total amount of products sold each month of each year:
CREATE MATERIALIZED VIEW sum_sales_per_year_month_mv BUILD IMMEDIATE REFRESH FORCE ON DEMAND ENABLE QUERY REWRITE AS SELECT s.calendar_year, s.calendar_month_number, SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt FROM sales_par_range_list s WHERE s.calendar_year > 1990 GROUP BY s.calendar_year, s.calendar_month_number;
sales_per_country_mv
supports PCT against sales_par_range_list
at the range partitioning level as its range partition key calendar_month_number
is in its SELECT
and GROUP
BY
list:
INSERT INTO sales_par_range_list VALUES (2001, 3, 25, 'Spain', 20, 'PROD20', 300, 20.50);
This statement inserts a row with calendar_month_number = 3
and country_name = 'Spain'
. This row is inserted into partition q1
subpartition Europe
. After this INSERT
statement, sum_sales_per_year_month_mv
is stale with respect to partition q1
of sales_par_range_list
. So any incoming query that accesses data from this partition in sales_par_range_list
cannot be rewritten, for example, the following statement:
Note that the following query accesses data from partitions q1
and q2
. Because q1 was updated, the materialized view is stale with respect to q1
so PCT rewrite is unavailable.
SELECT s.calendar_year, SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt FROM sales_par_range_list s WHERE s.calendar_year = 2000 AND s.calendar_month_number BETWEEN 2 AND 6 GROUP BY s.calendar_year;
An example of a statement that does rewrite after the INSERT
statement is the following, because it accesses fresh material:
SELECT s.calendar_year, SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt FROM sales_par_range_list s WHERE s.calendar_year = 2000 AND s.calendar_month_number BETWEEN 5 AND 9 GROUP BY s.calendar_year;
Figure 12-3 offers a graphical illustration of what is stale and what is fresh.
Figure 12-3 PCT Rewrite and Range-List Partitioning
Description of "Figure 12-3 PCT Rewrite and Range-List Partitioning"
12.2.7.3 PCT Rewrite Based on List Partitioned Tables
If the LIST
partitioning key is present in the materialized view's SELECT
and GROUP
BY
, then PCT will be supported by the materialized view. Regardless of the supported partitioning type, if the partition marker or rowid of the detail table is present in the materialized view then PCT is supported by the materialized view on that specific detail table.
CREATE TABLE sales_par_list (calendar_year, calendar_month_number, day_number_in_month, country_name, prod_id, quantity_sold, amount_sold) PARTITION BY LIST (country_name) (PARTITION America VALUES ('United States of America', 'Argentina'), PARTITION Asia VALUES ('Japan', 'India'), PARTITION Europe VALUES ('France', 'Spain', 'Ireland')) AS SELECT t.calendar_year, t.calendar_month_number, t.day_number_in_month, c1.country_name, s.prod_id, s.quantity_sold, s.amount_sold FROM times t, countries c1, sales s, customers c2 WHERE s.time_id = t.time_id and s.cust_id = c2.cust_id and c2.country_id = c1.country_id and c1.country_name IN ('United States of America', 'Argentina', 'Japan', 'India', 'France', 'Spain', 'Ireland');
If a materialized view is created on the table sales_par_list
, which has a list partitioning key, PCT rewrite will use that materialized view for potential rewrites.
To illustrate this feature, the following example creates a materialized view that has the total amounts sold of every product in each country for each year. The view depends on detail tables sales_par_list
and products
.
CREATE MATERIALIZED VIEW sales_per_country_mv BUILD IMMEDIATE REFRESH FORCE ON DEMAND ENABLE QUERY REWRITE AS SELECT s.calendar_year AS calendar_year, s.country_name AS country_name, p.prod_name AS prod_name, SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt FROM sales_par_list s, products p WHERE s.prod_id = p.prod_id AND s.calendar_year <= 2000 GROUP BY s.calendar_year, s.country_name, prod_name;
sales_per_country_mv
supports PCT against sales_par_list
as its list partition key country_name
is in its SELECT
and GROUP
BY
list. Table products
is not partitioned, so sales_per_country_mv
does not support PCT against this table.
A query could be rewritten (in ENFORCED
or TRUSTED
modes) in terms of sales_per_country_mv
even if sales_per_country_mv
is stale if the incoming query accesses only fresh parts of the materialized view. You can determine which parts of the materialized view are FRESH
only if the updated tables are PCT enabled in the materialized view. If non-PCT enabled tables have been updated, then the rewrite is not possible with fresh data from that specific materialized view as you cannot identify the FRESH
portions of the materialized view.
sales_per_country_mv
supports PCT on sales_par_list
and does not support PCT on table product. If table products
is updated, then PCT rewrite is not possible with sales_per_country_mv
as you cannot tell which portions of the materialized view are FRESH
.
The following updates sales_par_list
as follows:
INSERT INTO sales_par_list VALUES (2000, 10, 22, 'France', 900, 20, 200.99);
This statement inserted a row into partition Europe in table sales_par_list
. Now sales_per_country_mv
is stale, but PCT rewrite (in ENFORCED
and TRUSTED
modes) is possible as this materialized view supports PCT against table sales_par_list
. The fresh and stale areas of the materialized view are identified based on the partitioned detail table sales_par_list
.
Figure 12-4 illustrates what is fresh and what is stale in this example.
Figure 12-4 PCT Rewrite and List Partitioning
Description of "Figure 12-4 PCT Rewrite and List Partitioning"
Consider the following query:
SELECT s.country_name, p.prod_name, SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt FROM sales_par_list s, products p WHERE s.prod_id = p.prod_id AND s.calendar_year = 2000 AND s.country_name IN ('United States of America', 'Japan') GROUP BY s.country_name, p.prod_name;
This query accesses partitions America
and Asia
in sales_par_list
; these partition have not been updated so rewrite is possible with stale materialized view sales_per_country_mv
as this query will access only FRESH
portions of the materialized view.
The query is rewritten in terms of sales_per_country_mv
as follows:
SELECT country_name, prod_name, SUM(sum_sales) AS sum_sales, SUM(cnt) AS cnt FROM sales_per_country_mv WHERE calendar_year = 2000 AND country_name IN ('United States of America', 'Japan') GROUP BY country_name, prod_name;
Now consider the following query:
SELECT s.country_name, p.prod_name, SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt FROM sales_par_list s, products p WHERE s.prod_id = p.prod_id AND s.calendar_year = 1999 AND s.country_name IN ('Japan', 'India', 'Spain') GROUP BY s.country_name, p.prod_name;
This query accesses partitions Europe
and Asia
in sales_par_list
. Partition Europe
has been updated, so this query cannot be rewritten in terms of sales_per_country_mv
as the required data from the materialized view is stale.
You will be able to rewrite after any kinds of updates to sales_par_list
, that is DMLs, direct loads and Partition Maintenance Operations (PMOPs) if the incoming query accesses FRESH
parts of the materialized view.
12.2.7.4 PCT Rewrite and PMARKER
When a partition marker is provided, the query rewrite capabilities are limited to rewrite queries that access whole detail table partitions as all rows from a specific partition have the same pmarker value. That is, if a query accesses a portion of a detail table partition, it is not rewritten even if that data corresponds to a FRESH
portion of the materialized view. Now FRESH
portions of the materialized view are determined by the pmarker value. To determine which rows of the materialized view are fresh, you associate freshness with the marker value, so all rows in the materialized view with a specific pmarker value are FRESH
or are STALE
.
The following creates a materialized view has the total amounts sold of every product in each detail table partition of sales_par_list
for each year. This materialized view will also depend on detail table products
as shown in the following:
CREATE MATERIALIZED VIEW sales_per_dt_partition_mv BUILD IMMEDIATE REFRESH FORCE ON DEMAND ENABLE QUERY REWRITE AS SELECT s.calendar_year AS calendar_year, p.prod_name AS prod_name, DBMS_MVIEW.PMARKER(s.rowid) pmarker, SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt FROM sales_par_list s, products p WHERE s.prod_id = p.prod_id AND s.calendar_year > 2000 GROUP BY s.calendar_year, DBMS_MVIEW.PMARKER(s.rowid), p.prod_name;
The materialized view sales_per_dt_partition_mv
provides the sum of sales for each detail table partition. This materialized view supports PCT rewrite against table sales_par_list
because the partition marker is in its SELECT
and GROUP
BY
clauses. Table 12-2 lists the partition names and their pmarkers for this example.
Table 12-2 Partition Names and Their Pmarkers
Partition Name | Pmarker |
---|---|
America |
1000 |
Asia |
1001 |
Europe |
1002 |
Then update the table sales_par_list
as follows:
DELETE FROM sales_par_list WHERE country_name = 'India';
You have deleted rows from partition Asia
in table sales_par_list
. Now sales_per_dt_partition_mv
is stale, but PCT rewrite (in ENFORCED
and TRUSTED
modes) is possible as this materialized view supports PCT (pmarker based) against table sales_par_list
.
Now consider the following query:
SELECT p.prod_name, SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt FROM sales_par_list s, products p WHERE s.prod_id = p.prod_id AND s.calendar_year = 2001 AND s.country_name IN ('United States of America', 'Argentina') GROUP BY p.prod_name;
This query can be rewritten in terms of sales_per_dt_partition_mv
as all the data corresponding to a detail table partition is accessed, and the materialized view is FRESH
with respect to this data. This query accesses all data in partition America
, which has not been updated.
The query is rewritten in terms of sales_per_dt_partition_mv
as follows:
SELECT prod_name, SUM(sum_sales) AS sum_sales, SUM(cnt) AS cnt FROM sales_per_dt_partition_mv WHERE calendar_year = 2001 AND pmarker = 1000 GROUP BY prod_name;
12.2.7.5 PCT Rewrite Using Rowid as PMARKER
A materialized view supports PCT rewrite provided a partition key or a partition marker is provided in its SELECT
and GROUP
BY
clause, if there is a GROUP
BY
clause. You can use the rowids of the partitioned table instead of the pmarker or the partition key. Note that Oracle converts the rowids into pmarkers internally. Consider the following table:
CREATE TABLE product_par_list (prod_id, prod_name, prod_category, prod_subcategory, prod_list_price) PARTITION BY LIST (prod_category) (PARTITION prod_cat1 VALUES ('Boys', 'Men'), PARTITION prod_cat2 VALUES ('Girls', 'Women')) AS SELECT prod_id, prod_name, prod_category, prod_subcategory, prod_list_price FROM products;
Let us create the following materialized view on tables, sales_par_list
and product_par_list
:
CREATE MATERIALIZED VIEW sum_sales_per_category_mv BUILD IMMEDIATE REFRESH FORCE ON DEMAND ENABLE QUERY REWRITE AS SELECT p.rowid prid, p.prod_category, SUM (s.amount_sold) sum_sales, COUNT(*) cnt FROM sales_par_list s, product_par_list p WHERE s.prod_id = p.prod_id and s.calendar_year <= 2000 GROUP BY p.rowid, p.prod_category;
All the limitations that apply to pmarker rewrite apply here as well. The incoming query should access a whole partition for the query to be rewritten. The following pmarker table is used in this case:
product_par_list pmarker value ---------------- ------------- prod_cat1 1000 prod_cat2 1001 prod_cat3 1002
Then update table product_par_list
as follows:
DELETE FROM product_par_list WHERE prod_name = 'MEN';
So sum_sales_per_category_mv
is stale with respect to partition prod_list1
from product_par_list
.
Now consider the following query:
SELECT p.prod_category, SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt FROM sales_par_list s, product_par_list p WHERE s.prod_id = p.prod_id AND p.prod_category IN ('Girls', 'Women') AND s.calendar_year <= 2000 GROUP BY p.prod_category;
This query can be rewritten in terms of sum_sales_per_category_mv
as all the data corresponding to a detail table partition is accessed, and the materialized view is FRESH
with respect to this data. This query accesses all data in partition prod_cat2
, which has not been updated. Following is the rewritten query in terms of sum_sales_per_category_mv
:
SELECT prod_category, sum_sales, cnt FROM sum_sales_per_category_mv WHERE DBMS_MVIEW.PMARKER(srid) IN (1000) GROUP BY prod_category;
12.2.8 About Query Rewrite Using Multiple Materialized Views
Query rewrite has been extended to enable the rewrite of a query using multiple materialized views. If query rewrite determines that there is no set of materialized views that returns all of the data, then query rewrite retrieves the remaining data from the base tables.
Query rewrite using multiple materialized views can take advantage of many different types and combinations of rewrite, such as using PCT and IN
-lists. The following examples illustrate some of the queries where query rewrite is now possible.
Consider the following two materialized views, cust_avg_credit_mv1
and cust_avg_credit_mv2
. cust_avg_credit_mv1
asks for all customers average credit limit for each postal code that were born between the years 1940 and 1950. cust_avg_credit_mv2
asks for customers average credit limit for each postal code that were born after 1950 and before or on 1970.
The materialized views' definitions for this example are as follows:
CREATE MATERIALIZED VIEW cust_avg_credit_mv1 ENABLE QUERY REWRITE AS SELECT cust_postal_code, cust_year_of_birth, SUM(cust_credit_limit) AS sum_credit, COUNT(cust_credit_limit) AS count_credit FROM customers WHERE cust_year_of_birth BETWEEN 1940 AND 1950 GROUP BY cust_postal_code, cust_year_of_birth; CREATE MATERIALIZED VIEW cust_avg_credit_mv2 ENABLE QUERY REWRITE AS SELECT cust_postal_code, cust_year_of_birth, SUM(cust_credit_limit) AS sum_credit, COUNT(cust_credit_limit) AS count_credit FROM customers WHERE cust_year_of_birth > 1950 AND cust_year_of_birth <= 1970 GROUP BY cust_postal_code, cust_year_of_birth;
Query 1: One Matched Interval in Materialized View and Query
Consider a query that asks for all customers average credit limit for each postal code who were born between 1940 and 1970. This query is matched by the interval BETWEEN
on cust_year_of_birth
.
SELECT cust_postal_code, AVG(cust_credit_limit) AS avg_credit FROM customers c WHERE cust_year_of_birth BETWEEN 1940 AND 1970 GROUP BY cust_postal_code;
The preceding query can be rewritten in terms of these two materialized views to get all the data as follows:
SELECT v1.cust_postal_code, SUM(v1.sum_credit)/SUM(v1.count_credit) AS avg_credit FROM (SELECT cust_postal_code, sum_credit, count_credit FROM cust_avg_credit_mv1 GROUP BY cust_postal_code UNION ALL SELECT cust_postal_code, sum_credit, count_credit FROM cust_avg_credit_mv2 GROUP BY cust_postal_code) v1 GROUP BY v1.cust_postal_code;
Note that the UNION
ALL
query is used in an inline view because of the re-aggregation that needs to take place. Note also how query rewrite was the count aggregate to perform this rollup.
Query 2: Query Outside of Data Contained in Materialized View
When the materialized view goes beyond the range asked by the query, a filter (also called selection) is added to the rewritten query to drop out the unneeded rows returned by the materialized view. This case is illustrated in the following query:
SELECT cust_postal_code, SUM(cust_credit_limit) AS sum_credit FROM customers c WHERE cust_year_of_birth BETWEEN 1945 AND 1955 GROUP BY cust_postal_code;
Query 2 is rewritten as:
SELECT v1.cust_postal_code, SUM(v1.sum_credit) FROM (SELECT cust_postal_code, SUM(sum_credit) AS sum_credit FROM cust_avg_credit_mv1 WHERE cust_year_of_birth BETWEEN 1945 AND 1950 GROUP BY cust_postal_code UNION ALL SELECT cust_postal_code, SUM(sum_credit) AS sum_credit FROM cust_birth_mv2 WHERE cust_year_of_birth > 1950 AND cust_year_of_birth <= 1955 GROUP BY cust_postal_code) v1 GROUP BY v1.cust_postal_code;
Query 3: Requesting More Data Than is in the Materialized View
What if a query asks for more data than is contained in the two materialized views? It still rewrites using both materialized views and the data in the base table. In the following example, a new set of materialized views without aggregates is defined It will still rewrite using both materialized views and the data in the base table.
CREATE MATERIALIZED VIEW cust_birth_mv1 ENABLE QUERY REWRITE AS SELECT cust_last_name, cust_first_name, cust_year_of_birth FROM customers WHERE cust_year_of_birth BETWEEN 1940 AND 1950; CREATE MATERIALIZED VIEW cust_avg_credit_mv2 ENABLE QUERY REWRITE AS SELECT cust_last_name, cust_first_name, cust_year_of_birth FROM customers WHERE cust_year_of_birth > 1950 AND cust_year_of_birth <= 1970;
Our queries now require all customers born between 1940 and 1990.
SELECT cust_last_name, cust_first_name FROM customers c WHERE cust_year_of_birth BETWEEN 1940 AND 1990;
Query rewrite needs to access the base table to access the customers that were born after 1970 and before or on 1990. Therefore, Query 3 is rewritten as the following:
SELECT cust_last_name, cust_first_name FROM cust_birth_mv1 UNION ALL SELECT cust_last_name, cust_first_name FROM cust_birth_mv2 UNION ALL SELECT cust_last_name, cust_first_name FROM customers c WHERE cust_year_of_birth > 1970 AND cust_year_of_birth <= 1990;
Query 4: Requesting Data on Multiple Selection Columns
Consider the following query, which asks for all customers who have a credit limit between 1,000 and 10,000 and were born between the years 1945 and 1960. This query is a multi-selection query because it is asking for data on multiple selection columns.
SELECT cust_last_name, cust_first_name FROM customers WHERE cust_year_of_birth BETWEEN 1945 AND 1960 AND cust_credit_limit BETWEEN 1000 AND 10000;
Figure 12-5 shows a two-selection query, which can be rewritten with the two-selection materialized views described in the following section.
Figure 12-5 Query Rewrite Using Multiple Materialized Views
Description of "Figure 12-5 Query Rewrite Using Multiple Materialized Views"
The graph in Figure 12-5 illustrates the materialized views that can be used to satisfy this query. credit_mv1
asks for customers that have credit limits between 1,000 and 5,000 and were born between 1945 and 1950. credit_mv2
asks for customers that have credit limits > 5,000 and <= 10,000 and were born between 1945 and 1960. credit_mv3
asks for customers that have credit limits between 1,000 and 5,000 and were born after 1950 and before or on 1955.
The materialized views' definitions for this case are as follows:
CREATE MATERIALIZED VIEW credit_mv1 ENABLE QUERY REWRITE AS SELECT cust_last_name, cust_first_name, cust_credit_limit, cust_year_of_birth FROM customers WHERE cust_credit_limit BETWEEN 1000 AND 5000 AND cust_year_of_birth BETWEEN 1945 AND 1950; CREATE MATERIALIZED VIEW credit_mv2 ENABLE QUERY REWRITE AS SELECT cust_last_name, cust_first_name, cust_credit_limit, cust_year_of_birth FROM customers WHERE cust_credit_limit > 5000 AND cust_credit_limit <= 10000 AND cust_year_of_birth BETWEEN 1945 AND 1960; CREATE MATERIALIZED VIEW credit_mv3 ENABLE QUERY REWRITE AS SELECT cust_last_name, cust_first_name, cust_credit_limit, cust_year_of_birth FROM customers WHERE cust_credit_limit BETWEEN 1000 AND 5000 AND cust_year_of_birth > 1950 AND cust_year_of_birth <= 1955;
Query 4 can be rewritten by using all three materialized views to access most of the data. However, because not all the data can be obtained from these three materialized views, query rewrite also accesses the base tables to retrieve the data for customers who have credit limits between 1,000 and 5,000 and were born between 1955 and 1960. It is rewritten as follows:
SELECT cust_last_name, cust_first_name FROM credit_mv1 UNION ALL SELECT cust_last_name, cust_first_name FROM credit_mv2 UNION ALL SELECT cust_last_name, cust_first_name FROM credit_mv3 UNION ALL SELECT cust_last_name, cust_first_name FROM customers WHERE cust_credit_limit BETWEEN 1000 AND 5000 AND cust_year_of_birth > 1955 AND cust_year_of_birth <= 1960;
This example illustrates how a multi-selection query can be rewritten with multiple materialized views. The example was simplified to show no overlapping data among the three materialized views. However, query rewrite can perform similar rewrites.
Query 5: Intervals and Constrained Intervals
This example illustrates how a multi-selection query can be rewritten using a single selection materialized view. In this example, there are two intervals in the query and one constrained interval in the materialized view. It asks for customers that have credit limits between 1,000 and 10,000 and were born between 1945 and 1960. But suppose that credit_mv1
asks for just customers that have credit limits between 1,000 and 5,000. credit_mv1
is not constrained by a selection in cust_year_of_birth
, therefore covering the entire range of birth year values for the query.
Figure 12-6 Constrained Materialized View Selections
Description of "Figure 12-6 Constrained Materialized View Selections"
The area between the lines in Figure 12-6 represents the data credit1_mv
.
The new credit_mv1
is defined as follows:
CREATE MATERIALIZED VIEW credit_mv1 ENABLE QUERY REWRITE AS SELECT cust_last_name, cust_first_name, cust_credit_limit, cust_year_of_birth FROM customers WHERE cust_credit_limit BETWEEN 1000 AND 5000;
The query is as follows:
SELECT cust_last_name, cust_first_name FROM customers WHERE cust_year_of_birth BETWEEN 1945 AND 1960 AND cust_credit_limit BETWEEN 1000 AND 10000;
And finally the rewritten query is as follows:
SELECT cust_last_name, cust_first_name FROM credit_mv1 WHERE cust_year_of_birth BETWEEN 1945 AND 1960 UNION ALL SELECT cust_last_name, cust_first_name FROM customers WHERE cust_year_of_brith BETWEEN 1945 AND 1960 AND cust_credit_limit > 5000 AND cust_credit_limit <= 10000;
Query 6: Query has Single Column IN-List and Materialized Views have Single Column Intervals
Multiple materialized view query rewrite can process an IN
-list in the incoming query and rewrite the query in terms of materialized views that have intervals on the same selection column. Given that an IN
-list represents discrete values in an interval, this rewrite capability is a natural extension to the intervals only scenario described earlier.
The following is an example of a one column IN
-list selection in the query and one column interval selection in the materialized views. Consider a query that asks for the number of customers for each country who were born in any of the following year: 1945, 1950, 1955, 1960, 1965, 1970 or 1975. This query is constrained by an IN
-list on cust_year_of_birth
.
SELECT c2.country_name, count(c1.country_id) FROM customers c1, countries c2 WHERE c1.country_id = c2.country_id AND c1.cust_year_of_birth IN (1945, 1950, 1955, 1960, 1965, 1970, 1975) GROUP BY c2.country_name;
Consider the following two materialized views. cust_country_birth_mv1
asks for the number of customers for each country that were born between the years 1940 and 1950. cust_country_birth_mv2
asks for the number of customers for each country that were born after 1950 and before or on 1970. The preceding query can be rewritten in terms of these two materialized views to get the total number of customers for each country born in 1945, 1950, 1955, 1960, 1965 and 1970. The base table access is required to obtain the number of customers that were born in 1975.
The materialized views' definitions for this example are as follows:
CREATE MATERIALIZED VIEW cust_country_birth_mv1 ENABLE QUERY REWRITE AS SELECT c2.country_name, c1.cust_year_of_birth, COUNT(c1.country_id) AS count_customers FROM customers c1, countries c2 WHERE c1.country_id = c2.country_id AND cust_year_of_birth BETWEEN 1940 AND 1950 GROUP BY c2.country_name, c1.cust_year_of_birth; CREATE MATERIALIZED VIEW cust_country_birth_mv2 ENABLE QUERY REWRITE AS SELECT c2.country_name, c1.cust_year_of_birth, COUNT(c1.country_id) AS count_customers FROM customers c1, countries c2 WHERE c1.country_id = c2.country_id AND cust_year_of_birth > 1950 AND cust_year_of_birth <= 1970 GROUP BY c2.country_name, c1.cust_year_of_birth;
So, Query 6 is rewritten as:
SELECT v1.country_name, SUM(v1.count_customers) FROM (SELECT country_name, SUM(count_customers) AS count_customers FROM cust_country_birth_mv1 WHERE cust_year_of_birth IN (1945, 1950) GROUP BY country_name UNION ALL SELECT country_name, SUM(count_customers) AS count_customers FROM cust_country_birth_mv2 WHERE cust_year_of_birth IN (1955, 1960, 1965, 1970) GROUP BY country_name UNION ALL SELECT c2.country_name, COUNT(c1.country_id) AS count_customers FROM customers c1, countries c2 WHERE c1.country_id = c2.country_id AND cust_year_of_birth IN (1975) GROUP BY c2.country_name) v1 GROUP BY v1.country_name;
Query 7: PCT Rewrite with Multiple Materialized Views
Rewrite with multiple materialized views can also take advantage of PCT rewrite. PCT rewrite refers to the capability of rewriting a query with only the fresh portions of a materialized view when the materialized view is stale. This feature is used in ENFORCED
or TRUSTED
integrity modes, and with multiple materialized view rewrite, it can use the fresh portions of the materialized view to get the fresh data from it, and go to the base table to get the stale data. So the rewritten query will UNION
ALL
only the fresh data from one or more materialized views and obtain the rest of the data from the base tables to answer the query. Therefore, all the PCT rules and conditions apply here as well. The materialized view should be PCT enabled and the changes made to the base table should be such that the fresh and stale portions of the materialized view can be clearly identified.
This example assumes you have a query that asks for customers who have credit limits between 1,000 and 10,000 and were born between 1945 and 1964. Also, the customer table is partitioned by cust_date_of_birth
and there is a PCT-enabled materialized view called credit_mv1
that also asks for customers who have a credit limit between 1,000 and 10,000 and were born between 1945 and 1964.
SELECT cust_last_name, cust_first_name FROM customers WHERE cust_credit_limit BETWEEN 1000 AND 10000;
In Figure 12-7, the diagram illustrates those regions of the materialized view that are fresh (dark) and stale (light) with respect to the base table partitions p1
-p6
.
Figure 12-7 PCT and Multiple Materialized View Rewrite
Description of "Figure 12-7 PCT and Multiple Materialized View Rewrite"
Let us say that you are in ENFORCED
mode and that p1
, p2
, p3
, p5
, and p6
of the customer table are fresh and partition p4
is stale. This means that all partitions of credit_mv1
cannot be used to answer the query. The rewritten query must get the results for customer partition p4
from some other materialized view or as shown in this example, from the base table. Below, you can see part of the table definition for the customers
table showing how the table is partitioned:
CREATE TABLE customers (PARTITION BY RANGE (cust_year_of_birth) PARTITION p1 VALUES LESS THAN (1945), PARTITION p2 VALUES LESS THAN (1950), PARTITION p3 VALUES LESS THAN (1955), PARTITION p4 VALUES LESS THAN (1960), PARTITION p5 VALUES LESS THAN (1965), PARTITION p6 VALUES LESS THAN (1970);
The materialized view definition for the preceding example is as follows:
CREATE MATERIALIZED VIEW credit_mv1 ENABLE QUERY REWRITE AS SELECT cust_last_name, cust_first_name, cust_credit_limit, cust_year_of_birth FROM customers WHERE cust_credit_limit BETWEEN 1000 AND 10000 AND cust_year_of_birth BETWEEN 1945 AND 1964;
Note that this materialized view is PCT enabled with respect to table customers
.
The rewritten query is as follows:
SELECT cust_last_name, cust_first_name FROM credit_mv1
WHERE cust_credit_limit BETWEEN 1000 AND 10000 AND
(cust_year_of_birth >= 1945 AND cust_year_of_birth < 1955 OR
cust_year_of_birth BETWEEN 1945 AND 1964)
UNION ALL
SELECT cust_last_name, cust_first_name
FROM customers WHERE cust_credit_limit BETWEEN 1000 AND 10000
AND cust_year_of_birth < 1960 AND cust_year_of_birth >= 1955;
12.3 Other Query Rewrite Considerations
The following topics discusses some of the other cases when query rewrite is possible:
12.3.1 About Query Rewrite Using Nested Materialized Views
Query rewrite attempts to iteratively take advantage of nested materialized views. Oracle Database first tries to rewrite a query with materialized views having aggregates and joins, then with a materialized view containing only joins. If any of the rewrites succeeds, Oracle repeats that process again until no rewrites are found. For example, assume that you had created materialized views join_sales_time_product_mv
and sum_sales_time_product_mv
as in the following:
CREATE MATERIALIZED VIEW join_sales_time_product_mv ENABLE QUERY REWRITE AS SELECT p.prod_id, p.prod_name, t.time_id, t.week_ending_day, s.channel_id, s.promo_id, s.cust_id, s.amount_sold FROM sales s, products p, times t WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id; CREATE MATERIALIZED VIEW sum_sales_time_product_mv ENABLE QUERY REWRITE AS SELECT mv.prod_name, mv.week_ending_day, COUNT(*) cnt_all, SUM(mv.amount_sold) sum_amount_sold, COUNT(mv.amount_sold) cnt_amount_sold FROM join_sales_time_product_mv mv GROUP BY mv.prod_name, mv.week_ending_day;
Then consider the following query:
SELECT p.prod_name, t.week_ending_day, SUM(s.amount_sold) FROM sales s, products p, times t WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id GROUP BY p.prod_name, t.week_ending_day;
Oracle finds that join_sales_time_product_mv
is eligible for rewrite. The rewritten query has this form:
SELECT mv.prod_name, mv.week_ending_day, SUM(mv.amount_sold) FROM join_sales_time_product_mv mv GROUP BY mv.prod_name, mv.week_ending_day;
Because a rewrite occurred, Oracle tries the process again. This time, the query can be rewritten with single-table aggregate materialized view sum_sales_store_time
into the following form:
SELECT mv.prod_name, mv.week_ending_day, mv.sum_amount_sold FROM sum_sales_time_product_mv mv;
12.3.2 About Query Rewrite in the Presence of Inline Views
Oracle Database supports query rewrite with inline views in two ways:
-
when the text from the inline views in the materialized view exactly matches the text in the request query
-
when the request query contains inline views that are equivalent to the inline views in the materialized view
Two inline views are considered equivalent if their SELECT
lists and GROUP
BY
lists are equivalent, FROM
clauses contain the same or equivalent objects, their join graphs, including all the selections in the WHERE
clauses are equivalent and their HAVING
clauses are equivalent.
The following examples illustrate how a query with an inline view can rewrite with a materialized view using text match and general inline view rewrites. Consider the following materialized view that contains an inline view:
CREATE MATERIALIZED VIEW SUM_SALES_MV ENABLE QUERY REWRITE AS SELECT mv_iv.prod_id, mv_iv.cust_id, sum(mv_iv.amount_sold) sum_amount_sold FROM (SELECT prod_id, cust_id, amount_sold FROM sales, products WHERE sales.prod_id = products.prod_id) MV_IV GROUP BY mv_iv.prod_id, mv_iv.cust_id;
The following query has an inline view whose text matches exactly with that of the materialized view's inline view. Hence, the query inline view is internally replaced with the materialized view's inline view so that the query can be rewritten:
SELECT iv.prod_id, iv.cust_id, SUM(iv.amount_sold) sum_amount_sold FROM (SELECT prod_id, cust_id, amount_sold FROM sales, products WHERE sales.prod_id = products.prod_id) IV GROUP BY iv.prod_id, iv.cust_id;
The following query has an inline view that does not have exact text match with the inline view in the preceding materialized view. Note that the join predicate in the query inline view is switched. Even though this query does not textually match with that of the materialized view's inline view, query rewrite identifies the query's inline view as equivalent to the materialized view's inline view. As before, the query inline view will be internally replaced with the materialized view's inline view so that the query can be rewritten.
SELECT iv.prod_id, iv.cust_id, SUM(iv.amount_sold) sum_amount_sold FROM (SELECT prod_id, cust_id, amount_sold FROM sales, products WHERE products.prod_id = sales.prod_id) IV GROUP BY iv.prod_id, iv.cust_id;
Both of these queries are rewritten with SUM_SALES_MV
as follows:
SELECT prod_id, cust_id, sum_amount_sold FROM SUM_SALES_MV;
General inline view rewrite is not supported for queries that contain set operators, GROUPING
SET
clauses, nested subqueries, nested inline views, and remote tables.
12.3.3 About Query Rewrite Using Remote Tables
Oracle Database supports query rewrite with materialized views that reference tables at a single remote database site. Note that the materialized view should be present at the site where the query is being issued. Because any remote table update cannot be propagated to the local site simultaneously, query rewrite only works in the stale_tolerated
mode. Whenever a query contains columns that are not found in the materialized view, it uses a technique called join back to rewrite the query. However, if the join back table is not found at the local site, query rewrite does not take place. Also, because the constraint information of the remote tables is not available at the remote site, query rewrite does not make use of any constraint information.
The following query contains tables that are found at a single remote site:
SELECT p.prod_id, t.week_ending_day, s.cust_id, SUM(s.amount_sold) AS sum_amount_sold FROM sales@remotedbl s, products@remotedbl p, times@remotedbl t WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id GROUP BY p.prod_id, t.week_ending_day, s.cust_id;
The following materialized view is present at the local site, but it references tables that are all found at the remote site:
CREATE MATERIALIZED VIEW sum_sales_prod_week_mv ENABLE QUERY REWRITE AS SELECT p.prod_id, t.week_ending_day, s.cust_id, SUM(s.amount_sold) AS sum_amount_sold FROM sales@remotedbl s, products@remotedbl p, times@remotedbl t WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id GROUP BY p.prod_id, t.week_ending_day, s.cust_id;
Even though the query references remote tables, it is rewritten using the previous materialized view as follows:
SELECT prod_id, week_ending_day, cust_id, sum_amount_sold FROM sum_sales_prod_week_mv;
12.3.4 About Query Rewrite in the Presence of Duplicate Tables
Oracle Database accomplishes query rewrite of queries that contain multiple references to the same tables, or self joins by employing two different strategies. Using the first strategy, you need to ensure that the query and the materialized view definitions have the same aliases for the multiple references to a table. If you do not provide a matching alias, Oracle tries the second strategy, where the joins in the query and the materialized view are compared to match the multiple references in the query to the multiple references in the materialized view.
The following is an example of a materialized view and a query. In this example, the query is missing a reference to a column in a table so an exact text match does not work. General query rewrite can occur, however, because the aliases for the table references match.
To demonstrate the self-join rewriting possibility with the sh
sample schema, the following addition is assumed to include the actual shipping and payment date in the fact table, referencing the same dimension table times. This is for demonstration purposes only and does not return any results:
ALTER TABLE sales ADD (time_id_ship DATE); ALTER TABLE sales ADD (CONSTRAINT time_id_book_fk FOREIGN key (time_id_ship) REFERENCES times(time_id) ENABLE NOVALIDATE); ALTER TABLE sales MODIFY CONSTRAINT time_id_book_fk RELY; ALTER TABLE sales ADD (time_id_paid DATE); ALTER TABLE sales ADD (CONSTRAINT time_id_paid_fk FOREIGN KEY (time_id_paid) REFERENCES times(time_id) ENABLE NOVALIDATE); ALTER TABLE sales MODIFY CONSTRAINT time_id_paid_fk RELY;
Now, you can define a materialized view as follows:
CREATE MATERIALIZED VIEW sales_shipping_lag_mv ENABLE QUERY REWRITE AS SELECT t1.fiscal_week_number, s.prod_id, t2.fiscal_week_number - t1.fiscal_week_number AS lag FROM times t1, sales s, times t2 WHERE t1.time_id = s.time_id AND t2.time_id = s.time_id_ship;
The following query fails the exact text match test but is rewritten because the aliases for the table references match:
SELECT s.prod_id, t2.fiscal_week_number - t1.fiscal_week_number AS lag FROM times t1, sales s, times t2 WHERE t1.time_id = s.time_id AND t2.time_id = s.time_id_ship;
Note that Oracle Database performs other checks to ensure the correct match of an instance of a multiply instanced table in the request query with the corresponding table instance in the materialized view. For instance, in the following example, Oracle correctly determines that the matching alias names used for the multiple instances of table times
does not establish a match between the multiple instances of table times
in the materialized view.
The following query cannot be rewritten using sales_shipping_lag_mv
, even though the alias names of the multiply instanced table time
match because the joins are not compatible between the instances of time
aliased by t2
:
SELECT s.prod_id, t2.fiscal_week_number - t1.fiscal_week_number AS lag FROM times t1, sales s, times t2 WHERE t1.time_id = s.time_id AND t2.time_id = s.time_id_paid;
This request query joins the instance of the time
table aliased by t2
on the s.time_id_paid
column, while the materialized views joins the instance of the times
table aliased by t2
on the s.time_id_ship
column. Because the join conditions differ, Oracle correctly determines that rewrite cannot occur.
The following query does not have any matching alias in the materialized view, sales_shipping_lag_mv
, for the table, times. But query rewrite now compares the joins between the query and the materialized view and correctly match the multiple instances of times.
SELECT s.prod_id, x2.fiscal_week_number - x1.fiscal_week_number AS lag FROM times x1, sales s, times x2 WHERE x1.time_id = s.time_id AND x2.time_id = s.time_id_ship;
12.3.5 About Query Rewrite Using Date Folding
Date folding rewrite is a specific form of expression matching rewrite. In this type of rewrite, a date range in a query is folded into an equivalent date range representing higher date granules. The resulting expressions representing higher date granules in the folded date range are matched with equivalent expressions in a materialized view. The folding of date range into higher date granules such as months, quarters, or years is done when the underlying data type of the column is an Oracle DATE
. The expression matching is done based on the use of canonical forms for the expressions.
DATE
is a built-in data type which represents ordered time units such as seconds, days, and months, and incorporates a time hierarchy (second -> minute -> hour -> day -> month -> quarter -> year). This hard-coded knowledge about DATE
is used in folding date ranges from lower-date granules to higher-date granules. Specifically, folding a date value to the beginning of a month, quarter, year, or to the end of a month, quarter, year is supported. For example, the date value 1-jan-1999
can be folded into the beginning of either year 1999
or quarter 1999-1
or month 1999-01
. And, the date value 30-sep-1999
can be folded into the end of either quarter 1999-03
or month 1999-09
.
Note:
Due to the way date folding works, you should be careful when using BETWEEN
and date columns. The best way to use BETWEEN
and date columns is to increment the later date by 1. In other words, instead of using date_col BETWEEN '1-jan-1999' AND '30-jun-1999'
, you should use date_col
BETWEEN '1-jan-1999' AND '1-jul-1999'
. You could also use the TRUNC
function to get the equivalent result, as in TRUNC(date_col)
BETWEEN '1-jan-1999' AND '30-jun-1999'
. TRUNC
will, however, strip time values.
Because date values are ordered, any range predicate specified on date columns can be folded from lower level granules into higher level granules provided the date range represents an integral number of higher level granules. For example, the range predicate date_col >= '1-jan-1999'
AND
date_col
<
'30-jun-1999' can be folded into either a month range or a quarter range using the TO_CHAR
function, which extracts specific date components from a date value.
The advantage of aggregating data by folded date values is the compression of data achieved. Without date folding, the data is aggregated at the lowest granularity level, resulting in increased disk space for storage and increased I/O to scan the materialized view.
Consider a query that asks for the sum of sales by product types for the year 1998:
SELECT p.prod_category, SUM(s.amount_sold) FROM sales s, products p WHERE s.prod_id=p.prod_id AND s.time_id >= TO_DATE('01-jan-1998', 'dd-mon-yyyy') AND s.time_id < TO_DATE('01-jan-1999', 'dd-mon-yyyy') GROUP BY p.prod_category; CREATE MATERIALIZED VIEW sum_sales_pcat_monthly_mv ENABLE QUERY REWRITE AS SELECT p.prod_category, TO_CHAR(s.time_id,'YYYY-MM') AS month, SUM(s.amount_sold) AS sum_amount FROM sales s, products p WHERE s.prod_id=p.prod_id GROUP BY p.prod_category, TO_CHAR(s.time_id, 'YYYY-MM'); SELECT p.prod_category, SUM(s.amount_sold) FROM sales s, products p WHERE s.prod_id=p.prod_id AND TO_CHAR(s.time_id, 'YYYY-MM') >= '01-jan-1998' AND TO_CHAR(s.time_id, 'YYYY-MM') < '01-jan-1999' GROUP BY p.prod_category; SELECT mv.prod_category, mv.sum_amount FROM sum_sales_pcat_monthly_mv mv WHERE month >= '01-jan-1998' AND month < '01-jan-1999';
The range specified in the query represents an integral number of years, quarters, or months. Assume that there is a materialized view mv3
that contains pre-summarized sales by prod_type
and is defined as follows:
CREATE MATERIALIZED VIEW mv3 ENABLE QUERY REWRITE AS SELECT prod_name, TO_CHAR(sales.time_id,'yyyy-mm') AS month, SUM(amount_sold) AS sum_sales FROM sales, products WHERE sales.prod_id = products.prod_id GROUP BY prod_name, TO_CHAR(sales_time_id, 'yyyy-mm');
The query can be rewritten by first folding the date range into the month range and then matching the expressions representing the months with the month expression in mv3
. This rewrite is shown in two steps (first folding the date range followed by the actual rewrite).
SELECT prod_name, SUM(amount_sold) AS sum_sales FROM sales, products WHERE sales.prod_id = products.prod_id AND TO_CHAR(sales.time_id, 'yyyy-mm') >= TO_CHAR('01-jan-1998', 'yyyy-mm') AND TO_CHAR(sales.time_id, '01-jan-1999', 'yyyy-mm') < TO_CHAR(TO_DATE(''01-jan-1999'', ''dd-mon-yyyy''), ''yyyy-mm'') GROUP BY prod_name; SELECT prod_name, sum_sales FROM mv3 WHERE month >= TO_CHAR(TO_DATE('01-jan-1998', 'dd-mon-yyyy'), 'yyyy-mm') AND month < TO_CHAR(TO_DATE('01-jan-1999', 'dd-mon-yyyy'), 'yyyy-mm');
If mv3
had pre-summarized sales by prod_name
and year instead of prod_name
and month, the query could still be rewritten by folding the date range into year range and then matching the year expressions.
12.3.6 About Query Rewrite Using View Constraints
Data warehouse applications recognize multi-dimensional cubes in the database by identifying integrity constraints in the relational schema. Integrity constraints represent primary and foreign key relationships between fact and dimension tables. By querying the data dictionary, applications can recognize integrity constraints and hence the cubes in the database. However, this does not work in an environment where database administrators, for schema complexity or security reasons, define views on fact and dimension tables. In such environments, applications cannot identify the cubes properly. By allowing constraint definitions between views, you can propagate base table constraints to the views, thereby allowing applications to recognize cubes even in a restricted environment.
View constraint definitions are declarative in nature, but operations on views are subject to the integrity constraints defined on the underlying base tables, and constraints on views can be enforced through constraints on base tables. Defining constraints on base tables is necessary, not only for data correctness and cleanliness, but also for materialized view query rewrite purposes using the original base objects.
See Also:
Materialized view rewrite extensively uses constraints for query rewrite. They are used for determining lossless joins, which, in turn, determine if joins in the materialized view are compatible with joins in the query and thus if rewrite is possible.
DISABLE
NOVALIDATE
is the only valid state for a view constraint. However, you can choose RELY
or NORELY
as the view constraint state to enable more sophisticated query rewrites. For example, a view constraint in the RELY
state allows query rewrite to occur when the query integrity level is set to TRUSTED
. Table 12-3 illustrates when view constraints are used for determining lossless joins.
Note that view constraints cannot be used for query rewrite integrity level ENFORCED
. This level enforces the highest degree of constraint enforcement ENABLE
VALIDATE
.
Table 12-3 View Constraints and Rewrite Integrity Modes
Constraint States | RELY | NORELY |
---|---|---|
|
No |
No |
|
Yes |
No |
|
Yes |
No |
Example 12-10 View Constraints
To demonstrate the rewrite capabilities on views, you need to extend the sh
sample schema as follows:
CREATE VIEW time_view AS SELECT time_id, TO_NUMBER(TO_CHAR(time_id, 'ddd')) AS day_in_year FROM times;
You can now establish a foreign key/primary key relationship (in RELY
mode) between the view and the fact table, and thus rewrite takes place as described in Table 12-3, by adding the following constraints. Rewrite will then work for example in TRUSTED
mode.
ALTER VIEW time_view ADD (CONSTRAINT time_view_pk PRIMARY KEY (time_id) DISABLE NOVALIDATE); ALTER VIEW time_view MODIFY CONSTRAINT time_view_pk RELY; ALTER TABLE sales ADD (CONSTRAINT time_view_fk FOREIGN KEY (time_id) REFERENCES time_view(time_id) DISABLE NOVALIDATE); ALTER TABLE sales MODIFY CONSTRAINT time_view_fk RELY;
Consider the following materialized view definition:
CREATE MATERIALIZED VIEW sales_pcat_cal_day_mv ENABLE QUERY REWRITE AS SELECT p.prod_category, t.day_in_year, SUM(s.amount_sold) AS sum_amount_sold FROM time_view t, sales s, products p WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id GROUP BY p.prod_category, t.day_in_year;
The following query, omitting the dimension table products
, is also rewritten without the primary key/foreign key relationships, because the suppressed join between sales
and products
is known to be lossless.
SELECT t.day_in_year, SUM(s.amount_sold) AS sum_amount_sold FROM time_view t, sales s WHERE t.time_id = s.time_id GROUP BY t.day_in_year;
However, if the materialized view sales_pcat_cal_day_mv
were defined only in terms of the view time_view
, then you could not rewrite the following query, suppressing then join between sales
and time_view
, because there is no basis for losslessness of the delta materialized view join. With the additional constraints as shown previously, this query will also rewrite.
SELECT p.prod_category, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p WHERE p.prod_id = s.prod_id GROUP BY p.prod_category;
To undo the changes you have made to the sh
schema, issue the following statements:
ALTER TABLE sales DROP CONSTRAINT time_view_fk; DROP VIEW time_view;
12.3.6.1 Abut View Constraints Restrictions
If the referential constraint definition involves a view, that is, either the foreign key or the referenced key resides in a view, the constraint can only be in DISABLE
NOVALIDATE
mode.
A RELY
constraint on a view is allowed only if the referenced UNIQUE
or PRIMARY
KEY
constraint in DISABLE
NOVALIDATE
mode is also a RELY
constraint.
The specification of ON
DELETE
actions associated with a referential Integrity constraint, is not allowed (for example, DELETE
cascade). However, DELETE
, UPDATE
, and INSERT
operations are allowed on views and their base tables as view constraints are in DISABLE
NOVALIDATE
mode.
12.3.7 About Query Rewrite in the Presence of Hybrid Partitioned Tables
Query rewrite considers external partitions in a hybrid partitioned table to be of UNKNOWN freshness. Therefore, when a query requests data from one or more external partitions, it can only be rewritten under TRUSTED
or STALE_TOLERATED
integrity mode.
When a materialized view that is based on a hybrid partitioned table includes the partition key or partition marker in its SELECT
list, it is eligible for partition tracking. For materialized views based on hybrid partitioned table tables that are not PCT-enabled, STALE_TOLERATED
is the only possible integrity mode.
Queries against hybrid partitioned tables can be rewritten using PCT rewrite under ENFORCED
and TRUSTED
integrity modes only if the hybrid partitioned table is range or list partitioned.
Example 12-11 Query Rewrite and Materialized Views Based on Hybrid Partitioned Tables
The hybrid partitioned table named hybrid_sales
uses the ENFORCED
integrity mode. One of the internal partitions is stale.
The following query is run:
SELECT customer_no, sum(price) as sum_price
FROM hybrid_sales WHERE
time_id > TO_DATE(‘01-01-1950’) and time_id < TO_DATE(‘06-01-2001’)
GROUP BY customer_no;
This query can be rewritten to use the hybrid partitioned table. PCT rewrite selects the fresh partitions from the materialized view and any stale partitions and external partitions directly from the base table. The rewritten query is as follows:
SELECT v1.customer_no, SUM(v1.total_price) sum_price
FROM
(SELECT customer_no, SUM(total_price) FROM Hybrid_sales WHERE
time_id > TO_DATE(‘01-01-1950’) and time_id < TO_DATE(‘01-01-2000’)
GROUP BY customer_no
UNION ALL
SELECT customer_no, SUM(total_price) FROM HyPT_MV WHERE
time_id > TO_DATE(‘01-01-2000’) and time_id < TO_DATE(‘01-01-2001’)
GROUP BY customer_no
UNION ALL
SELECT customer_no, SUM(total_price) FROM Hybrid_sales WHERE
time_id > TO_DATE(‘01-01-2001’) and time_id < TO_DATE(‘06-01-2001’)
GROUP BY customer_no
) v1
GROUP BY v1.customer_no;
12.3.8 Query Rewrite Using Set Operator Materialized Views
You can use query rewrite with materialized views that contain set operators. In this case, the query and materialized view do not have to match textually for rewrite to occur. As an example, consider the following materialized view, which uses the postal codes for male customers from San Francisco or Los Angeles:
CREATE MATERIALIZED VIEW cust_male_postal_mv ENABLE QUERY REWRITE AS SELECT c.cust_city, c.cust_postal_code FROM sh.customers c WHERE c.cust_gender = 'M' AND c.cust_city = 'San Francisco' UNION ALL SELECT c.cust_city, c.cust_postal_code FROM sh.customers c WHERE c.cust_gender = 'M' AND c.cust_city = 'Los Angeles';
If you have the following query, which displays the postal codes for male customers from San Francisco or Los Angeles:
SELECT c.cust_city, c.cust_postal_code FROM sh.customers c WHERE c.cust_city = 'Los Angeles' AND c.cust_gender = 'M' UNION ALL SELECT c.cust_city, c.cust_postal_code FROM sh.customers c WHERE c.cust_city = 'San Francisco' AND c.cust_gender = 'M';
The rewritten query will be the following:
SELECT mv.cust_city, mv.cust_postal_code FROM cust_male_postal_mv mv;
The rewritten query has dropped the UNION
ALL
and replaced it with the materialized view. Normally, query rewrite has to use the existing set of general eligibility rules to determine if the SELECT
subselections under the UNION
ALL
are equivalent in the query and the materialized view.
See UNION ALL Marker and Query Rewrite.
If, for example, you have a query that retrieves the postal codes for male customers from San Francisco, Palmdale, or Los Angeles, the same rewrite can occur as in the previous example but query rewrite must keep the UNION
ALL
with the base tables, as in the following:
SELECT c.cust_city, c.cust_postal_code FROM sh.customers c WHERE c.cust_city= 'Palmdale' AND c.cust_gender ='M' UNION ALL SELECT c.cust_city, c.cust_postal_code FROM sh.customers c WHERE c.cust_city = 'Los Angeles' AND c.cust_gender = 'M' UNION ALL SELECT c.cust_city, c.cust_postal_code FROM sh.customers c WHERE c.cust_city = 'San Francisco' AND c.cust_gender = 'M';
The rewritten query will be:
SELECT mv.cust_city, mv.cust_postal_code FROM cust_male_postal_mv mv UNION ALL SELECT c.cust_city, c.cust_postal_code FROM sh.customers c WHERE c.cust_city = 'Palmdale' AND c.cust_gender = 'M';
So query rewrite detects the case where a subset of the UNION
ALL
can be rewritten using the materialized view cust_male_postal_mv
.
UNION
, UNION
ALL
, and INTERSECT
are commutative, so query rewrite can rewrite regardless of the order the subselects are found in the query or materialized view. However, MINUS
is not commutative. A MINUS
B is not equivalent to B MINUS
A. Therefore, the order in which the subselects appear under the MINUS
operator in the query and the materialized view must be in the same order for rewrite to happen. As an example, consider the case where there exists an old version of the customer table called customer_old
and you want to find the difference between the old one and the current customer table only for male customers who live in London. That is, you want to find those customers in the current one that were not in the old one. The following example shows how this is done using a MINUS
operator:
SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city= 'Los Angeles' AND c.cust_gender = 'M' MINUS SELECT c.cust_city, c.cust_postal_code FROM customers_old c WHERE c.cust_city = 'Los Angeles' AND c.cust_gender = 'M';
Switching the subselects would yield a different answer. This illustrates that MINUS
is not commutative.
12.3.8.1 UNION ALL Marker and Query Rewrite
If a materialized view contains one or more UNION
ALL
operators, it can also include a UNION
ALL
marker. The UNION
ALL
marker is used to identify from which UNION
ALL
subselect each row in the materialized view originates. Query rewrite can use the marker to distinguish what rows coming from the materialized view belong to a certain UNION
ALL
subselect. This is useful if the query needs only a subset of the data from the materialized view or if the subselects of the query do not textually match with the subselects of the materialized view. As an example, the following query retrieves the postal codes for male customers from San Francisco and female customers from Los Angeles:
SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_gender = 'M' and c.cust_city = 'San Francisco' UNION ALL SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_gender = 'F' and c.cust_city = 'Los Angeles';
The query can be answered using the following materialized view:
CREATE MATERIALIZED VIEW cust_postal_mv ENABLE QUERY REWRITE AS SELECT 1 AS marker, c.cust_gender, c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city = 'Los Angeles' UNION ALL SELECT 2 AS marker, c.cust_gender, c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city = 'San Francisco';
The rewritten query is as follows:
SELECT mv.cust_city, mv.cust_postal_code FROM cust_postal_mv mv WHERE mv.marker = 2 AND mv.cust_gender = 'M' UNION ALL SELECT mv.cust_city, mv.cust_postal_code FROM cust_postal_mv mv WHERE mv.marker = 1 AND mv.cust_gender = 'F';
The WHERE
clause of the first subselect includes mv.marker = 2
and mv.cust_gender = 'M'
, which selects only the rows that represent male customers in the second subselect of the UNION
ALL
. The WHERE
clause of the second subselect includes mv.marker = 1
and mv.cust_gender = 'F'
, which selects only those rows that represent female customers in the first subselect of the UNION
ALL
. Note that query rewrite cannot take advantage of set operators that drop duplicate or distinct rows. For example, UNION
drops duplicates so query rewrite cannot tell what rows have been dropped, as in the following:
SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city= 'Palmdale' AND c.cust_gender ='M' SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_gender = 'M' and c.cust_city = 'San Francisco' UNION ALL SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_gender = 'F' and c.cust_city = 'Los Angeles';
The rewritten query using UNION
ALL
markers is as follows:
SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city= 'Palmdale' AND c.cust_gender ='M' UNION ALL SELECT mv.cust_city, mv.cust_postal_code FROM cust_postal_mv mv WHERE mv.marker = 2 AND mv.cust_gender = 'M' UNION ALL SELECT mv.cust_city, mv.cust_postal_code FROM cust_postal_mv mv WHERE mv.marker = 1 AND mv.cust_gender = 'F';
The rules for using a marker are that it must:
-
Be a constant number or string and be the same data type for all
UNION ALL
subselects. -
Yield a constant, distinct value for each
UNION ALL
subselect. You cannot reuse the same value in multiple subselects. -
Be in the same ordinal position for all subselects.
12.3.9 About Query Rewrite in the Presence of Grouping Sets
This section discusses the following considerations for using query rewrite with grouping sets:
12.3.9.1 About Query Rewrite When Using GROUP BY Extensions
Several extensions to the GROUP
BY
clause in the form of GROUPING
SETS
, CUBE, ROLLUP
, and their concatenation are available.
These extensions enable you to selectively specify the groupings of interest in the
GROUP
BY
clause of the query. For example, the following is a typical query with
grouping sets. This query uses the sh
sample schema.
SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold FROM sh.sales s, sh.customers c, sh.products p, sh.times t WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY GROUPING SETS ((p.prod_subcategory, t.calendar_month_desc), (c.cust_city, p.prod_subcategory));
The term base grouping for queries with GROUP
BY
extensions denotes all unique expressions present in the GROUP
BY
clause. In the previous query, the following grouping (p.prod_subcategory, t.calendar_month_desc, c.cust_city
) is a base grouping.
The extensions can be present in user queries and in the queries defining materialized views. In both cases, materialized view rewrite applies and you can distinguish rewrite capabilities into the following scenarios:
12.3.9.1.1 Materialized View has Simple GROUP BY and Query has Extended GROUP BY
When a query contains an extended GROUP
BY
clause, it can be rewritten with a materialized view if its base grouping
can be rewritten using the materialized view as listed in the rewrite rules explained in "When Does Oracle Rewrite a Query?". For example, in
the following query which uses the sh
sample schema:
SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold FROM sh.sales s, sh.customers c, sh.products p, sh.times t WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY GROUPING SETS ((p.prod_subcategory, t.calendar_month_desc), (c.cust_city, p.prod_subcategory));
The base grouping is (p.prod_subcategory, t.calendar_month_desc, c.cust_city, p.prod_subcategory))
and, consequently, Oracle can rewrite the query using sum_sales_pscat_month_city_mv
as follows:
SELECT mv.prod_subcategory, mv.calendar_month_desc, mv.cust_city, SUM(mv.sum_amount_sold) AS sum_amount_sold FROM sum_sales_pscat_month_city_mv mv GROUP BY GROUPING SETS ((mv.prod_subcategory, mv.calendar_month_desc), (mv.cust_city, mv.prod_subcategory));
A special situation arises if the query uses the EXPAND_GSET_TO_UNION
hint. See "Hint for Rewriting Queries with Extended GROUP BY" for an example of using EXPAND_GSET_TO_UNION
.
12.3.9.1.2 Materialized View has Extended GROUP BY and Query has Simple GROUP BY
In order for a materialized view with an extended GROUP
BY
to be used for rewrite, it must satisfy two additional conditions:
-
It must contain a grouping distinguisher, which is the
GROUPING_ID
function on allGROUP
BY
expressions. For example, if theGROUP
BY
clause of the materialized view isGROUP
BY
CUBE(a, b)
, then theSELECT
list should containGROUPING_ID(a, b)
. -
The
GROUP
BY
clause of the materialized view should not result in any duplicate groupings. For example,GROUP
BY
GROUPING
SETS
((a, b),
(a, b))
would disqualify a materialized view from general rewrite.
A materialized view with an extended GROUP
BY
contains multiple groupings. Oracle finds the grouping with the lowest
cost from which the query can be computed and uses that for rewrite. For example, consider the
following materialized view based on the sh
sample schema:
CREATE MATERIALIZED VIEW sum_grouping_set_mv ENABLE QUERY REWRITE AS SELECT p.prod_category, p.prod_subcategory, c.cust_state_province, c.cust_city, GROUPING_ID(p.prod_category,p.prod_subcategory, c.cust_state_province,c.cust_city) AS gid, SUM(s.amount_sold) AS sum_amount_sold FROM sh.sales s, sh.products p, sh.customers c WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY GROUPING SETS ((p.prod_category, p.prod_subcategory, c.cust_city), (p.prod_category, p.prod_subcategory, c.cust_state_province, c.cust_city), (p.prod_category, p.prod_subcategory));
In this case, the following query is rewritten:
SELECT p.prod_subcategory, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, customers c WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY p.prod_subcategory, c.cust_city;
This query is rewritten with the closest matching grouping from the materialized view. That is, the (prod_category, prod_subcategory, cust_city)
grouping:
SELECT prod_subcategory, cust_city, SUM(sum_amount_sold) AS sum_amount_sold
FROM sum_grouping_set_mv
WHERE gid = grouping identifier of (prod_category,prod_subcategory, cust_city)
GROUP BY prod_subcategory, cust_city;
12.3.9.1.3 Both Materialized View and Query Have Extended GROUP BY
When both materialized view and the query contain GROUP
BY
extensions, Oracle uses two strategies for rewrite: grouping match and UNION
ALL
rewrite. First, Oracle tries grouping match. The groupings in the query are matched against groupings in the materialized view and if all are matched with no rollup, Oracle selects them from the materialized view. For example, consider the following query:
SELECT p.prod_category, p.prod_subcategory, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, customers c WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY GROUPING SETS ((p.prod_category, p.prod_subcategory, c.cust_city), (p.prod_category, p.prod_subcategory));
This query matches two groupings from sum_grouping_set_mv
and Oracle rewrites the query as the following:
SELECT prod_subcategory, cust_city, sum_amount_sold FROM sum_grouping_set_mv WHERE gid = grouping identifier of (prod_category,prod_subcategory, cust_city) OR gid = grouping identifier of (prod_category,prod_subcategory)
If grouping match fails, Oracle tries a general rewrite mechanism called UNION
ALL
rewrite. Oracle first represents the query with the extended GROUP
BY
clause as an equivalent UNION
ALL
query. Every grouping of the original query is placed in a separate UNION
ALL
branch. The branch will have a simple GROUP
BY
clause. For example, consider this query:
SELECT p.prod_category, p.prod_subcategory, c.cust_state_province, t.calendar_month_desc, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, customers c, times t WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY GROUPING SETS ((p.prod_subcategory, t.calendar_month_desc), (t.calendar_month_desc), (p.prod_category, p.prod_subcategory, c.cust_state_province), (p.prod_category, p.prod_subcategory));
This is first represented as UNION
ALL
with four branches:
SELECT null, p.prod_subcategory, null, t.calendar_month_desc, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, customers c, times t WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY p.prod_subcategory, t.calendar_month_desc UNION ALL SELECT null, null, null, t.calendar_month_desc, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, customers c, times t WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY t.calendar_month_desc UNION ALL SELECT p.prod_category, p.prod_subcategory, c.cust_state_province, null, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, customers c, times t WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY p.prod_category, p.prod_subcategory, c.cust_state_province UNION ALL SELECT p.prod_category, p.prod_subcategory, null, null, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, customers c, times t WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY p.prod_category, p.prod_subcategory;
Each branch is then rewritten separately using the rules from "When Does Oracle Rewrite a Query?". Using the materialized view sum_grouping_set_mv
, Oracle can rewrite only branches three (which requires materialized view rollup) and four (which matches the materialized view exactly). The unrewritten branches will be converted back to the extended GROUP
BY
form. Thus, eventually, the query is rewritten as:
SELECT null, p.prod_subcategory, null, t.calendar_month_desc, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, customers c, times t WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY GROUPING SETS ((p.prod_subcategory, t.calendar_month_desc), (t.calendar_month_desc),) UNION ALL SELECT prod_category, prod_subcategory, cust_state_province, null, SUM(sum_amount_sold) AS sum_amount_sold FROM sum_grouping_set_mv WHERE gid = <grouping id of (prod_category,prod_subcategory, cust_city)> GROUP BY p.prod_category, p.prod_subcategory, c.cust_state_province UNION ALL SELECT prod_category, prod_subcategory, null, null, sum_amount_sold FROM sum_grouping_set_mv WHERE gid = <grouping id of (prod_category,prod_subcategory)>
Note that a query with extended GROUP
BY
is represented as an equivalent UNION
ALL
and recursively submitted for rewrite optimization. The groupings that cannot be rewritten stay in the last branch of UNION
ALL
and access the base data instead.
12.3.9.2 Hint for Rewriting Queries with Extended GROUP BY
You can use the EXPAND_GSET_TO_UNION
hint to force expansion of the query with GROUP
BY
extensions into the equivalent UNION
ALL
query. This hint can be used in an environment where materialized views have simple GROUP
BY
clauses only. In this case, Oracle extends rewrite flexibility as each branch can be independently rewritten by a separate materialized view.
12.3.10 Query Rewrite in the Presence of Window Functions
Window functions are used to compute cumulative, moving and centered aggregates. These functions work with the following aggregates: AVG
, BIT_AND_AGG
, BIT_OR_AGG
, BIT_XOR_AGG
, CHECKSUM
, COUNT
, FIRST_VALUE
, KURTOSIS_POP
, KURTOSIS_SAMP
, LAST_VALUE
, MAX
, MIN
, SKEWNESS_POP
, SKEWNESS_SAMP
, SUM
, STDDEV
, and VARIANCE
. A query with a window function can be rewritten using exact text match rewrite. This requires that the materialized view definition also matches the query exactly. When there is no window function on the materialized view, then a query with a window function can be rewritten provided the aggregate in the query is found in the materialized view and all other eligibility checks such as the join computability checks are successful. A window function on the query is compared to the window function in the materialized view using its canonical form format. This enables query rewrite to rewrite even complex window functions.
When a query with a window function requires rollup during query rewrite, query rewrite, whenever possible, splits the query into an inner query with the aggregate and an outer query with the windowing function. This permits query rewrite to rewrite the aggregate in the inner query before applying the window function. One exception is that if the query has both a window function and a grouping set, then the presence of the grouping set prevents the splitting of the query, and therefore query rewrite does not take place.
12.3.11 Query Rewrite and Expression Matching
An expression that appears in a query can be replaced with a simple column in a materialized view provided the materialized view column represents a precomputed expression that matches with the expression in the query. If a query can be rewritten to use a materialized view, it will be faster. This is because materialized views contain precomputed calculations and do not need to perform expression computation.
The expression matching is done by first converting the expressions into canonical forms and then comparing them for equality. Therefore, two different expressions will generally be matched as long as they are equivalent to each other. Further, if the entire expression in a query fails to match with an expression in a materialized view, then subexpressions of it are tried to find a match. The subexpressions are tried in a top-down order to get maximal expression matching.
Consider a query against the sh
sample schema that asks for sum of
sales by age brackets (1-10, 11-20, 21-30, and so on).
CREATE MATERIALIZED VIEW sales_by_age_bracket_mv ENABLE QUERY REWRITE AS SELECT TO_CHAR((2000-c.cust_year_of_birth)/10-0.5,999) AS age_bracket, SUM(s.amount_sold) AS sum_amount_sold FROM sh.sales s, sh.customers c WHERE s.cust_id=c.cust_id GROUP BY TO_CHAR((2000-c.cust_year_of_birth)/10-0.5,999);
The following query rewrites, using expression matching:
SELECT TO_CHAR(((2000-c.cust_year_of_birth)/10)-0.5,999), SUM(s.amount_sold) FROM sales s, customers c WHERE s.cust_id=c.cust_id GROUP BY TO_CHAR((2000-c.cust_year_of_birth)/10-0.5,999);
This query is rewritten in terms of sales_by_age_bracket_mv
based on the matching of the canonical forms of the age bracket expressions (that is, 2000 - c.cust_year_of_birth
)/10-0.5), as follows:
SELECT age_bracket, sum_amount_sold FROM sales_by_age_bracket_mv;
12.3.11.1 Query Rewrite Using Partially Stale Materialized Views
When a partition of the detail table is updated, only specific sections of the materialized view are marked stale. The materialized view must have information that can identify the partition of the table corresponding to a particular row or group of the materialized view. The simplest scenario is when the partitioning key of the table is available in the SELECT
list of the materialized view because this is the easiest way to map a row to a stale partition. The key points when using partially stale materialized views are:
-
Query rewrite can use a materialized view in
ENFORCED
orTRUSTED
mode if the rows from the materialized view used to answer the query are known to beFRESH
. -
The fresh rows in the materialized view are identified by adding selection predicates to the materialized view's
WHERE
clause. Oracle rewrites a query with this materialized view if its answer is contained within this (restricted) materialized view.
The fact table sales
is partitioned based on ranges of time_id
as follows:
PARTITION BY RANGE (time_id) (PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998', 'DD-MON-YYYY')), PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998', 'DD-MON-YYYY')), PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998', 'DD-MON-YYYY')), ...
Suppose you have a materialized view grouping by time_id
as follows:
CREATE MATERIALIZED VIEW sum_sales_per_city_mv ENABLE QUERY REWRITE AS SELECT s.time_id, p.prod_subcategory, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, customers c WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id GROUP BY time_id, prod_subcategory, cust_city;
Also suppose new data will be inserted for December 2000, which will be assigned to partition sales_q4_2000
. For testing purposes, you can apply an arbitrary DML operation on sales
, changing a different partition than sales_q1_2000
as the following query requests data in this partition when this materialized view is fresh. For example, the following:
INSERT INTO SALES VALUES(17, 10, '01-DEC-2000', 4, 380, 123.45, 54321);
Until a refresh is done, the materialized view is generically stale and cannot be used for unlimited rewrite in enforced mode. However, because the table sales
is partitioned and not all partitions have been modified, Oracle can identify all partitions that have not been touched. The optimizer can identify the fresh rows in the materialized view (the data which is unaffected by updates since the last refresh operation) by implicitly adding selection predicates to the materialized view defining query as follows:
SELECT s.time_id, p.prod_subcategory, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, customers c WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id AND s.time_id < TO_DATE('01-OCT-2000','DD-MON-YYYY') OR s.time_id >= TO_DATE('01-OCT-2001','DD-MON-YYYY')) GROUP BY time_id, prod_subcategory, cust_city;
Note that the freshness of partially stale materialized views is tracked on a per-partition base, and not on a logical base. Because the partitioning strategy of the sales
fact table is on a quarterly base, changes in December 2000 causes the complete partition sales_q4_2000
to become stale.
Consider the following query, which asks for sales in quarters 1 and 2 of 2000:
SELECT s.time_id, p.prod_subcategory, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, customers c WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id AND s.time_id BETWEEN TO_DATE('01-JAN-2000', 'DD-MON-YYYY') AND TO_DATE('01-JUL-2000', 'DD-MON-YYYY') GROUP BY time_id, prod_subcategory, cust_city;
Oracle Database knows that those ranges of rows in the materialized view are fresh and can therefore rewrite the query with the materialized view. The rewritten query looks as follows:
SELECT time_id, prod_subcategory, cust_city, sum_amount_sold FROM sum_sales_per_city_mv WHERE time_id BETWEEN TO_DATE('01-JAN-2000', 'DD-MON-YYYY') AND TO_DATE('01-JUL-2000', 'DD-MON-YYYY');
Instead of the partitioning key, a partition marker (a function that identifies the partition given a rowid) can be present in the SELECT
(and GROUP
BY
list) of the materialized view. You can use the materialized view to rewrite queries that require data from only certain partitions (identifiable by the partition-marker), for instance, queries that have a predicate specifying ranges of the partitioning keys containing entire partitions. See Advanced Materialized Views for details regarding the supplied partition marker function DBMS_MVIEW.PMARKER
.
The following example illustrates the use of a partition marker in the materialized view instead of directly using the partition key column:
CREATE MATERIALIZED VIEW sum_sales_per_city_2_mv ENABLE QUERY REWRITE AS SELECT DBMS_MVIEW.PMARKER(s.rowid) AS pmarker, t.fiscal_quarter_desc, p.prod_subcategory, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, customers c, times t WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id AND s.time_id = t.time_id GROUP BY DBMS_MVIEW.PMARKER(s.rowid), p.prod_subcategory, c.cust_city, t.fiscal_quarter_desc;
Suppose you know that the partition sales_q1_2000
is fresh and DML changes have taken place for other partitions of the sales
table. For testing purposes, you can apply an arbitrary DML operation on sales
, changing a different partition than sales_q1_2000
when the materialized view is fresh. An example is the following:
INSERT INTO SALES VALUES(17, 10, '01-DEC-2000', 4, 380, 123.45, 54321);
Although the materialized view sum_sales_per_city_2_mv
is now considered generically stale, Oracle Database can rewrite the following query using this materialized view. This query restricts the data to the partition sales_q1_2000
, and selects only certain values of cust_city
, as shown in the following:
SELECT p.prod_subcategory, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, customers c, times t WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id AND s.time_id = t.time_id AND c.cust_city= 'Nuernberg' AND s.time_id >=TO_DATE('01-JAN-2000','dd-mon-yyyy') AND s.time_id < TO_DATE('01-APR-2000','dd-mon-yyyy') GROUP BY prod_subcategory, cust_city;
Note that rewrite with a partially stale materialized view that contains a PMARKER
function can only take place when the complete data content of one or more partitions is accessed and the predicate condition is on the partitioned fact table itself, as shown in the earlier example.
The DBMS_MVIEW.PMARKER
function gives you exactly one distinct value for each partition. This dramatically reduces the number of rows in a potential materialized view compared to the partitioning key itself, but you are also giving up any detailed information about this key. The only information you know is the partition number and, therefore, the lower and upper boundary values. This is the trade-off for reducing the cardinality of the range partitioning column and thus the number of rows.
Assuming the value of p_marker
for partition sales_q1_2000
is 31070, the previously shown queries can be rewritten against the materialized view as follows:
SELECT mv.prod_subcategory, mv.cust_city, SUM(mv.sum_amount_sold) FROM sum_sales_per_city_2_mv mv WHERE mv.pmarker = 31070 AND mv.cust_city= 'Nuernberg' GROUP BY prod_subcategory, cust_city;
So the query can be rewritten against the materialized view without accessing stale data.
12.3.12 Cursor Sharing and Bind Variables During Query Rewrite
Query rewrite is supported when the query contains user bind variables as long as the actual bind values are not required during query rewrite. If the actual values of the bind variables are required during query rewrite, then you can say that query rewrite is dependent on the bind values. Because the user bind variables are not available during query rewrite time, if query rewrite is dependent on the bind values, it is not possible to rewrite the query. For example, consider the following materialized view, customer_mv
, which has the predicate, (customer_id >= 1000
), in the WHERE
clause:
CREATE MATERIALIZED VIEW customer_mv ENABLE QUERY REWRITE AS SELECT cust_id, prod_id, SUM(amount_sold) AS total_amount FROM sales WHERE cust_id >= 1000 GROUP BY cust_id, prod_id;
Consider the following query, which has a user bind variable, :user_id
, in its WHERE
clause:
SELECT cust_id, prod_id, SUM(amount_sold) AS sum_amount FROM sales WHERE cust_id > :user_id GROUP BY cust_id, prod_id;
Because the materialized view, customer_mv
, has a selection in its WHERE
clause, query rewrite is dependent on the actual value of the user bind variable, user_id
, to compute the containment. Because user_id
is not available during query rewrite time and query rewrite is dependent on the bind value of user_id
, this query cannot be rewritten.
Even though the preceding example has a user bind variable in the WHERE
clause, the same is true regardless of where the user bind variable appears in the query. In other words, irrespective of where a user bind variable appears in a query, if query rewrite is dependent on its value, then the query cannot be rewritten.
Now consider the following query which has a user bind variable, :user_id
, in its SELECT
list:
SELECT cust_id + :user_id, prod_id, SUM(amount_sold) AS total_amount FROM sales WHERE cust_id >= 2000 GROUP BY cust_id, prod_id;
Because the value of the user bind variable, user_id
, is not required during query rewrite time, the preceding query will rewrite.
SELECT cust_id + :user_id, prod_id, total_amount FROM customer_mv;
12.3.13 Handling Expressions in Query Rewrite
Rewrite with some expressions is also supported when the expression evaluates to a constant, such as TO_DATE(
'
12-SEP-1999
'
,
'
DD-Mon-YYYY
'
)
. For example, if an existing materialized view is defined as:
CREATE MATERIALIZED VIEW sales_on_valentines_day_99_mv BUILD IMMEDIATE REFRESH FORCE ENABLE QUERY REWRITE AS SELECT s.prod_id, s.cust_id, s.amount_sold FROM times t, sales s WHERE s.time_id = t.time_id AND t.time_id = TO_DATE('14-FEB-1999', 'DD-MON-YYYY');
Then the following query can be rewritten:
SELECT s.prod_id, s.cust_id, s.amount_sold FROM sales s, times t WHERE s.time_id = t.time_id AND t.time_id = TO_DATE('14-FEB-1999', 'DD-MON-YYYY');
This query would be rewritten as follows:
SELECT * FROM sales_on_valentines_day_99_mv;
Whenever TO_DATE
is used, query rewrite only occurs if the date mask supplied is the same as the one specified by the NLS_DATE_FORMAT
.
12.4 Advanced Query Rewrite Using Equivalences
There is a special type of query rewrite that is possible where a declaration is made that two SQL statements are functionally equivalent. This capability enables you to place inside application knowledge into the database so the database can exploit this knowledge for improved query performance. You do this by declaring two SELECT
statements to be functionally equivalent (returning the same rows and columns) and indicating that one of the SELECT
statements is more favorable for performance.
This advanced rewrite capability can generally be applied to a variety of query performance problems and opportunities. Any application can use this capability to affect rewrites against complex user queries that can be answered with much simpler and more performant queries that have been specifically created, usually by someone with inside application knowledge.
There are many scenarios where you can have inside application knowledge that would allow SQL statement transformation and tuning for significantly improved performance. The types of optimizations you may wish to affect can be very simple or as sophisticated as significant restructuring of the query. However, the incoming SQL queries are often generated by applications and you have no control over the form and structure of the application-generated queries.
To gain access to this capability, you need to connect as SYSDBA
and explicitly grant execute access to the desired database administrators who will be declaring rewrite equivalences. See Oracle Database PL/SQL Packages and Types Reference for more information.
To illustrate this type of advanced rewrite, some examples using multidimensional data are provided. To optimize resource usage, an application may employ complicated SQL, custom C code or table functions to retrieve the data from the database. This complexity is irrelevant as far as end users are concerned. Users would still want to obtain their answers using typical queries with SELECT
... GROUP
BY
.
Example 12-12 Rewrite Using Equivalence
This example declares to Oracle that a given user query must be executed using a specified alternative query. Oracle would recognize this relationship and every time the user asked the query, it would transparently rewrite it using the alternative. Thus, the user is saved from the trouble of understanding and writing SQL for complicated aggregate computations.
There are two base tables sales_fact
and geog_dim
. You can compute the total sales for each city, state and region with a rollup, by issuing the following statement:
SELECT g.region, g.state, g.city, GROUPING_ID(g.city, g.state, g.region), SUM(sales) FROM sales_fact f, geog_dim g WHERE f.geog_key = g.geog_key GROUP BY ROLLUP(g.region, g.state, g.city);
An application may want to materialize this query for quick results. Unfortunately, the resulting materialized view occupies too much disk space. However, if you have a dimension rolling up city to state to region, you can easily compress the three grouping columns into one column using a decode statement. (This is also known as an embedded total):
DECODE (gid, 0, city, 1, state, 3, region, 7, "grand_total")
What this does is use the lowest level of the hierarchy to represent the entire information. For example, saying Boston
means Boston, MA, New England Region
and saying CA
means CA, Western Region
. An application can store these embedded total results into a table, say, embedded_total_sales
.
However, when returning the result back to the user, you would want to have all the data columns (city, state, region). In order to return the results efficiently and quickly, an application may use a custom table function (et_function
) to retrieve the data back from the embedded_total_sales
table in the expanded form as follows:
SELECT * FROM TABLE (et_function);
In other words, this feature allows an application to declare the equivalence of the user's preceding query to the alternative query, as in the following:
DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE ( 'EMBEDDED_TOTAL', 'SELECT g.region, g.state, g.city, GROUPING_ID(g.city, g.state, g.region), SUM(sales) FROM sales_fact f, geog_dim g WHERE f.geog_key = g.geog_key GROUP BY ROLLUP(g.region, g.state, g.city)', 'SELECT * FROM TABLE(et_function)');
Note:
Both Oracle join syntax (shown above) and ANSI join syntax are supported.This invocation of DECLARE_REWRITE_EQUIVALENCE
creates an equivalence declaration named EMBEDDED_TOTAL
stating that the specified SOURCE_STMT
and the specified DESTINATION_STMT
are functionally equivalent, and that the specified DESTINATION_STMT
is preferable for performance. After the DBA creates such a declaration, the user need have no knowledge of the space optimization being performed underneath the covers.
This capability also allows an application to perform specialized partial materializations of a SQL query. For instance, it could perform a rollup using a UNION
ALL
of three relations as shown in Example 12-13.
Example 12-13 Rewrite Using Equivalence (UNION ALL)
CREATE MATERIALIZED VIEW T1 AS SELECT g.region, g.state, g.city, 0 AS gid, SUM(sales) AS sales FROM sales_fact f, geog_dim g WHERE f.geog_key = g.geog_key GROUP BY g.region, g.state, g.city; CREATE MATERIALIZED VIEW T2 AS SELECT t.region, t.state, SUM(t.sales) AS sales FROM T1 GROUP BY t.region, t.state; CREATE VIEW T3 AS SELECT t.region, SUM(t.sales) AS sales FROM T2 GROUP BY t.region;
The ROLLUP(region, state, city)
query is then equivalent to:
SELECT * FROM T1 UNION ALL SELECT region, state, NULL, 1 AS gid, sales FROM T2 UNION ALL SELECT region, NULL, NULL, 3 AS gid, sales FROM T3 UNION ALL SELECT NULL, NULL, NULL, 7 AS gid, SUM(sales) FROM T3;
By specifying this equivalence, Oracle Database would use the more efficient second form of the query to compute the ROLLUP
query asked by the user.
DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE ( 'CUSTOM_ROLLUP', 'SELECT g.region, g.state, g.city, GROUPING_ID(g.city, g.state, g.region), SUM(sales) FROM sales_fact f, geog_dim g WHERE f.geog_key = g.geog_key GROUP BY ROLLUP(g.region, g.state, g.city ', ' SELECT * FROM T1 UNION ALL SELECT region, state, NULL, 1 as gid, sales FROM T2 UNION ALL SELECT region, NULL, NULL, 3 as gid, sales FROM T3 UNION ALL SELECT NULL, NULL, NULL, 7 as gid, SUM(sales) FROM T3');
Another application of this feature is to provide users special aggregate computations that may be conceptually simple but extremely complex to express in SQL. In this case, the application asks the user to use a specified custom aggregate function and internally compute it using complex SQL.
Example 12-14 Rewrite Using Equivalence (Using a Custom Aggregate)
Suppose the application users want to see the sales for each city, state and region and also additional sales information for specific seasons. For example, the New England user wants additional sales information for cities in New England for the winter months. The application would provide you a special aggregate Seasonal_Agg
that computes the earlier aggregate. You would ask a classic summary query but use Seasonal_Agg(sales, region)
rather than SUM(sales)
.
SELECT g.region, t.calendar_month_name, Seasonal_Agg(f.sales, g.region) AS sales FROM sales_fact f, geog_dim g, times t WHERE f.geog_key = g.geog_key AND f.time_id = t.time_id GROUP BY g.region, t.calendar_month_name;
Instead of asking the user to write SQL that does the extra computation, the application can do it automatically for them by using this feature. In this example, Seasonal_Agg
is computed using the spreadsheet functionality (see SQL for Modeling). Note that even though Seasonal_Agg
is a user-defined aggregate, the required behavior is to add extra rows to the query's answer, which cannot be easily done with simple PL/SQL functions.
DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE ( 'CUSTOM_SEASONAL_AGG', SELECT g.region, t.calendar_month_name, Seasonal_Agg(sales, region) AS sales FROM sales_fact f, geog_dim g, times t WHERE f.geog_key = g.geog_key AND f.time_id = t.time_id GROUP BY g.region, t.calendar_month_name', 'SELECT g,region, t.calendar_month_name, SUM(sales) AS sales FROM sales_fact f, geog_dim g WHERE f.geog_key = g.geog_key AND t.time_id = f.time_id GROUP BY g.region, g.state, g.city, t.calendar_month_name DIMENSION BY g.region, t.calendar_month_name (sales ['New England', 'Winter'] = AVG(sales) OVER calendar_month_name IN ('Dec', 'Jan', 'Feb', 'Mar'), sales ['Western', 'Summer' ] = AVG(sales) OVER calendar_month_name IN ('May', 'Jun', 'July', 'Aug'), .);
12.5 Creating Result Cache Materialized Views with Equivalences
A special type of materialized view, called a result cache materialized view (RCMV), enables you to use a result cache when running query rewrite. These result cache materialized views offer the main advantages of the result cache, faster access with less space required, without the normal drawback of being unable to run query rewrite against them.
An example of using this type of materialized view is the following.
Example 12-15 Result Cache Materialized View
First, grant the requisite permissions:
CONNECT / AS SYSDBA GRANT CREATE MATERIALIZED VIEW TO sh; GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO sh;
Next, create the result cache materialized view:
CONNECT sh/sh begin sys.DBMS_ADVANCED_REWRITE.Declare_Rewrite_Equivalence ( Name => 'RCMV_SALES', Source_Stmt => 'select channel_id, prod_id, sum(amount_sold), count(amount_sold) from sales group by prod_id, channel_id', Destination_Stmt => 'select * from (select /*+ RESULT_CACHE(name=RCMV_SALES) */ channel_id, prod_id, sum(amount_sold), count(amount_sold) from sales group by prod_id, channel_id)', Validate => FALSE, Rewrite_Mode => 'GENERAL' ); end; / ALTER SESSION SET query_rewrite_integrity = stale_tolerated;
Then, verify that different queries all rewrite to RCMV_SALES
by looking at the explain plan:
EXPLAIN PLAN FOR SELECT channel_id, SUM(amount_sold) FROM sales GROUP BY channel_id; @?/rdbms/admin/utlxpls PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3903632134 -------------------------------------------------------------------------------- |Id | Operation | Name |Rows|Bytes|Cost(%CPU)| Time |Pstart|Pstop| --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 64| 1340 (68)|00:00:17| | | | 1 | HASH GROUP BY | | 4 | 64| 1340 (68)|00:00:17| | | | 2 | VIEW | | 204| 3264| 1340 (68)|00:00:17| | | | 3 | RESULT CACHE |3gps5zr86gyb53y36js9zuay2s| | | | | | | | 4 | HASH GROUP BY | | 204| 2448| 1340 (68)|00:00:17| | | | 5 | PARTITION RANGE ALL| |918K| 10M| 655 (33)|00:00:08| 1 | 28 | | 6 | TABLE ACCESS FULL | SALES |918K| 10M| 655 (33)|00:00:08| 1 | 28 | --------------------------------------------------------------------------------- Result Cache Information (identified by operation id): ------------------------------------------------------ 3 - column-count=4; dependencies=(SH.SALES); name="RCMV_SALES" 18 rows selected.
Then, execute the query that creates the cached result:
SELECT channel_id, SUM(amount_sold) FROM sales GROUP BY channel_id; CHANNEL_ID SUM(AMOUNT_SOLD) ---------- ---------------- 2 26346342.3 4 13706802 3 57875260.6 9 277426.26
Next, verify that the materialized view was materialized in the result cache:
CONNECT / AS SYSDBA SELECT name, scan_count hits, block_count blocks, depend_count dependencies FROM V$RESULT_CACHE_OBJECTS WHERE name = 'RCMV_SALES'; NAME HITS BLOCKS DEPENDENCIES ---------- ---- ------ ------------ RCMV_SALES 0 5 1
Finally, drop the RCMV query equivalence:
begin sys.DBMS_ADVANCED_REWRITE.Drop_Rewrite_equivalence('RCMV_SALES'); end; /
For more information regarding result caches, see Oracle Database SQL Tuning Guide.
12.6 Query Rewrite and Materialized Views Based on Approximate Queries
Queries containing SQL functions that return approximate results are automatically rewritten to use a matching materialized view, if these queries can be answered using the materialized view.
For a query containing SQL functions that return approximate results to be rewritten using a materialized view that is based on an approximate query, ensure that query rewrite is enabled for the materialized view. Query rewrite must also be enabled either at the database level or for the current session.
Consider the materialized view approx_count_distinct_pdt_mv
that was defined as follows:
CREATE MATERIALIZED VIEW approx_count_distinct_pdt_mv
ENABLE QUERY REWRITE AS
SELECT t.calendar_year, t.calendar_month_number, t.day_number_in_month, approx_count_distinct_detail(prod_id) daily_detail
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_year, t.calendar_month_number, t.day_number_in_month;
Note:
Both Oracle join syntax (shown above) and ANSI join syntax are supported.When a query that matches the defining query of approx_count_distinct_pdt_mv
is run, and the prerequisites described in this section are met, the query is automatically rewritten to use this materialized view. The following query is rewritten to use approx_count_distinct_pdt_mv
, as indicated by the execution plan generated for the query.
SELECT t.calendar_year, t.calendar_month_number, t.day_number_in_month, approx_count_distinct(prod_id)
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_year, t.calendar_month_number, t.day_number_in_month;
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Plan hash value: 2307354865
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1460 | 74460 | 205 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| APPROX_COUNT_DISTINCT_PDT_MV | 1460 | 74460 | 205 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
8 rows selected.
The following query is also rewritten to use approx_count_distinct_pdt_mv
as indicated by the execution plan. Note that this query aggregates data to a higher level than that defined by the defining query of approx_count_distinct_pdt_mv
.
SELECT t.calendar_year, t.calendar_month_number, approx_count_distinct(prod_id)
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_year, t.calendar_month_number;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 827336432
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 1632 | 206 (1)| 00:00:01 |
| 1 | HASH GROUP BY APPROX | | 34 | 1632 | 206 (1)| 00:00:01 |
| 2 | MAT_VIEW REWRITE ACCESS FULL| APPROX_COUNT_DISTINCT_PDT_MV | 1460 | 70080 | 205 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
9 rows selected.
Rewriting Queries with Exact Functions to Use Materialized Views that Contain Approximate Functions
If you set database initialization parameters that substitute exact functions with the corresponding SQL functions that return approximate values, then the optimizer can rewrite queries containing exact functions to use materialized views that are defined using the approximate versions of the same functions. You need not rewrite the query to use the corresponding approximate functions.
For example, if the approx_for_count_distinct
parameter is set to TRUE
, then the optimizer rewrites the following query to use the materialized view approx_count_distinct_pdt_mv
:
ALTER SESSION SET approx_for_count_distinct = TRUE;
SELECT t.calendar_year, t.calendar_month_number, COUNT (DISTINCT prod_id)
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_year, t.calendar_month_number;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 827336432
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 1632 | 206 (1)| 00:00:01 |
| 1 | HASH GROUP BY APPROX | | 34 | 1632 | 206 (1)| 00:00:01 |
| 2 | MAT_VIEW REWRITE ACCESS FULL| APPROX_COUNT_DISTINCT_PDT_MV | 1460 | 70080 | 205 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
9 rows selected.
Observe that the above execution plan is the same as the execution plan that was generated when the query uses the approx_count_distinct
in the previous example.
12.7 Query Rewrite and Materialized Views Based on Bitmap-based COUNT(DISTINCT) Functions
Queries that contain COUNT(DISTINCT)
operations on integer columns can be rewritten to use materialized views that contain bitmap-based functions.
Enable query rewrite for the materialized view so that SQL queries can be rewritten using this materialized views.
Example 12-16 Query Rewrite Using Materialized Views Containing COUNT(DISTINCT)
The materialized view mv_sales
was created using the following command:
create materialized view mv_sales as
select PROMO_ID, BITMAP_BUCKET_NUMBER(PROD_ID) bm_bktno,
BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(PROD_ID),'RAW') bm_details
from sales
group by PROMO_ID,BITMAP_BUCKET_NUMBER(PROD_ID);
Query rewrite has been enabled for the materialized view mv_sales
.
When a SQL query performs a COUNT(DISTINCT)
operation on a numeric column that is included in the mv_sales
materialized view definition, the query is rewritten to use the materialized view. The execution plan below shows that the materialized view was used.
SQL> EXPLAIN PLAN FOR select PROMO_ID,count(distinct PROD_ID) from sales group by PROMO_ID order by PROMO_ID;
Explained.
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash value: 2440767223
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | VIEW | |
| 3 | HASH GROUP BY | |
| 4 | MAT_VIEW ACCESS FULL| MV_SALES |
----------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
15 rows selected.
Example 12-17 Query Rewrite with Bitmap-based COUNT(DISTINCT) and Rollup
The following command was used to create the materialized view mv_sales_amount
:
create materialized view mv_sales_amount AS
SELECT PROMO_ID, CHANNEL_ID,
BITMAP_BUCKET_NUMBER(PROD_ID) as bm_bktno,
BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(PROD_ID)) as bm_details,
SUM(AMOUNT_SOLD) as amount_sold
FROM sales
GROUP BY PROMO_ID, CHANNEL_ID, BITMAP_BUCKET_NUMBER(PROD_ID);
Query rewrite has been enabled for the materialized view mv_sales_amount
.
The execution plan for the SQL command shown below demonstrates query rewrite to satisfy queries containing a COUNT(DISTINCT)
function. Query rewrite is performed at different levels of aggregation and in the presence of other aggregates.
EXPLAIN PLAN FOR
SELECT PROMO_ID, COUNT(DISTINCT PROD_ID), SUM(AMOUNT_SOLD)
FROM sales
GROUP BY PROMO_ID;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows| Bytes| Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 163 | 6357 | 8 (13) | 00:00:01 |
| 1 | HASH GROUP BY | | 163 | 6357 | 8 (13) | 00:00:01 |
| 2 | VIEW | | 163 | 6357 | 8 (13) | 00:00:01 |
| 3 | HASH GROUP BY | | 163 | 324K | 8 (13) | 00:00:01 |
| 4 | MAT_VIEW ACCESS FULL| MV_SALES_AMOUNT | 163 | 324K | 7 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------
12.8 Verifying that Query Rewrite has Occurred
Because query rewrite occurs transparently, special steps have to be taken to verify that a query has been rewritten. Of course, if the query runs faster, this should indicate that rewrite has occurred, but that is not proof. Therefore, to confirm that query rewrite does occur, use the EXPLAIN
PLAN
statement or the DBMS_MVIEW.EXPLAIN_REWRITE
procedure.
This section contains the following topics:
12.8.1 Using EXPLAIN PLAN with Query Rewrite
The EXPLAIN
PLAN
facility is used as described in Oracle Database SQL Language Reference. For query rewrite, all you need to check is that the operation shows MAT_VIEW
REWRITE
ACCESS
. If it does, then query rewrite has occurred. An example is the following, which creates the materialized view cal_month_sales_mv
:
CREATE MATERIALIZED VIEW cal_month_sales_mv ENABLE QUERY REWRITE AS SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars FROM sales s, times t WHERE s.time_id = t.time_id GROUP BY t.calendar_month_desc;
If EXPLAIN
PLAN
is used on the following SQL statement, the results are placed in the default table PLAN_TABLE
. However, PLAN_TABLE
must first be created using the utlxplan.sql
script. Note that EXPLAIN
PLAN
does not actually execute the query.
EXPLAIN PLAN FOR SELECT t.calendar_month_desc, SUM(s.amount_sold) FROM sales s, times t WHERE s.time_id = t.time_id GROUP BY t.calendar_month_desc;
For the purposes of query rewrite, the only information of interest from PLAN_TABLE
is the operation OBJECT_NAME
, which identifies the method used to execute this query. Therefore, you would expect to see the operation MAT_VIEW
REWRITE
ACCESS
in the output as illustrated in the following:
SELECT OPERATION, OBJECT_NAME FROM PLAN_TABLE; OPERATION OBJECT_NAME -------------------- ----------------------- SELECT STATEMENT MAT_VIEW REWRITE ACCESS CALENDAR_MONTH_SALES_MV
12.8.2 Using the EXPLAIN_REWRITE Procedure with Query Rewrite
It can be difficult to understand why a query did not rewrite. The rules governing query rewrite eligibility are quite complex, involving various factors such as constraints, dimensions, query rewrite integrity modes, freshness of the materialized views, and the types of queries themselves. In addition, you may want to know why query rewrite chose a particular materialized view instead of another. To help with this matter, Oracle Database provides the DBMS_MVIEW.EXPLAIN_REWRITE
procedure to advise you when a query can be rewritten and, if not, why not. Using the results from DBMS_MVIEW.EXPLAIN_REWRITE
, you can take the appropriate action needed to make a query rewrite if at all possible.
Note that the query specified in the EXPLAIN_REWRITE
statement does not actually execute.
This section contains the following topics:
12.8.2.1 DBMS_MVIEW.EXPLAIN_REWRITE Syntax
You can obtain the output from DBMS_MVIEW.EXPLAIN_REWRITE
in two ways. The first is to use a table, while the second is to create a VARRAY
. The following shows the basic syntax for using an output table:
DBMS_MVIEW.EXPLAIN_REWRITE ( query VARCHAR2, mv VARCHAR2(30), statement_id VARCHAR2(30));
You can create an output table called REWRITE_TABLE
by executing the utlxrw.sql
script.
The query
parameter is a text string representing the SQL query. The parameter, mv
, is a fully-qualified materialized view name in the form of schema.mv
. This is an optional parameter. When it is not specified, EXPLAIN_REWRITE
returns any relevant messages regarding all the materialized views considered for rewriting the given query. When schema
is omitted and only mv
is specified, EXPLAIN_REWRITE
looks for the materialized view in the current schema.
If you want to direct the output of EXPLAIN_REWRITE
to a varray instead of a table, you should call the procedure as follows:
DBMS_MVIEW.EXPLAIN_REWRITE ( query [VARCHAR2 | CLOB], mv VARCHAR2(30), output_array SYS.RewriteArrayType);
Note that if the query is less than 256 characters long, EXPLAIN_REWRITE
can be easily invoked with the EXECUTE
command from SQL*Plus. Otherwise, the recommended method is to use a PL/SQL BEGIN... END
block, as shown in the examples in /rdbms/demo/smxrw*
.
12.8.2.2 Using REWRITE_TABLE to View EXPLAIN_REWRITE Output
The output of EXPLAIN_REWRITE
can be directed to a table named REWRITE_TABLE
. You can create this output table by running the utlxrw.sql
script. This script can be found in the admin
directory. The format of REWRITE_TABLE
is as follows:
CREATE TABLE REWRITE_TABLE( statement_id VARCHAR2(30), -- id for the query mv_owner VARCHAR2(30), -- owner of the MV mv_name VARCHAR2(30), -- name of the MV sequence INTEGER, -- sequence no of the msg query VARCHAR2(2000), -- user query query_block_no INTEGER, -- block no of the current subquery rewritten_txt VARCHAR2(2000), -- rewritten query message VARCHAR2(512), -- EXPLAIN_REWRITE msg pass VARCHAR2(3), -- rewrite pass no mv_in_msg VARCHAR2(30), -- MV in current message measure_in_msg VARCHAR2(30), -- Measure in current message join_back_tbl VARCHAR2(30), -- Join back table in message join_back_col VARCHAR2(30), -- Join back column in message original_cost INTEGER, -- Cost of original query rewritten_cost INTEGER, -- Cost of rewritten query flags INTEGER, -- associated flags reserved1 INTEGER, -- currently not used reerved2 VARCHAR2(10)) -- currently not used;
Example 12-18 EXPLAIN_REWRITE Using REWRITE_TABLE
An example PL/SQL invocation is:
EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE - ('SELECT p.prod_name, SUM(amount_sold) ' || - 'FROM sales s, products p ' || - 'WHERE s.prod_id = p.prod_id ' || - ' AND prod_name > ''B%'' ' || - ' AND prod_name < ''C%'' ' || - 'GROUP BY prod_name', - 'TestXRW.PRODUCT_SALES_MV', - 'SH'); SELECT message FROM rewrite_table ORDER BY sequence; MESSAGE -------------------------------------------------------------------------------- QSM-01033: query rewritten with materialized view, PRODUCT_SALES_MV 1 row selected.
The demo file xrwutl.sql
contains a procedure that you can call to provide a more detailed output from EXPLAIN_REWRITE
. See "About EXPLAIN_REWRITE Output" for more information.
The following is an example where you can see a more detailed explanation of why some materialized views were not considered and, eventually, the materialized view sales_mv
was chosen as the best one.
DECLARE qrytext VARCHAR2(500) :='SELECT cust_first_name, cust_last_name, SUM(amount_sold) AS dollar_sales FROM sales s, customers c WHERE s.cust_id= c.cust_id GROUP BY cust_first_name, cust_last_name'; idno VARCHAR2(30) :='ID1'; BEGIN DBMS_MVIEW.EXPLAIN_REWRITE(qrytext, '', idno); END; / SELECT message FROM rewrite_table ORDER BY sequence;
SQL> MESSAGE -------------------------------------------------------------------------------- QSM-01082: Joining materialized view, CAL_MONTH_SALES_MV, with table, SALES, not possible QSM-01022: a more optimal materialized view than PRODUCT_SALES_MV was used to rewrite QSM-01022: a more optimal materialized view than FWEEK_PSCAT_SALES_MV was used to rewrite QSM-01033: query rewritten with materialized view, SALES_MV
12.8.2.3 Using a Varray to View EXPLAIN_REWRITE Output
You can save the output of EXPLAIN_REWRITE
in a PL/SQL VARRAY
. The elements of this array are of the type RewriteMessage
, which is predefined in the SYS
schema as shown in the following:
TYPE RewriteMessage IS OBJECT( mv_owner VARCHAR2(30), -- MV's schema mv_name VARCHAR2(30), -- Name of the MV sequence NUMBER(3), -- sequence no of the msg query_text VARCHAR2(2000), -- User query query_block_no NUMBER(3), -- block no of the current subquery rewritten_text VARCHAR2(2000), -- rewritten query text message VARCHAR2(512), -- EXPLAIN_REWRITE error msg pass VARCHAR2(3), -- Query rewrite pass no mv_in_msg VARCHAR2(30), -- MV in current message measure_in_msg VARCHAR2(30), -- Measure in current message join_back_tbl VARCHAR2(30), -- Join back table in current msg join_back_col VARCHAR2(30), -- Join back column in current msg original_cost NUMBER(10), -- Cost of original query rewritten_cost NUMBER(10), -- Cost rewritten query flags NUMBER, -- Associated flags reserved1 NUMBER, -- For future use reserved2 VARCHAR2(10) -- For future use );
The array type, RewriteArrayType
, which is a varray of RewriteMessage
objects, is predefined in the SYS
schema as follows:
-
TYPE RewriteArrayType AS VARRAY(256) OF RewriteMessage;
-
Using this array type, now you can declare an array variable and specify it in the
EXPLAIN_REWRITE
statement. -
Each
RewriteMessage
record provides a message concerning rewrite processing. -
The parameters are the same as for
REWRITE_TABLE
, except forstatement_id
, which is not used when using a varray as output. -
The
mv_owner
field defines the owner of materialized view that is relevant to the message. -
The
mv_name
field defines the name of a materialized view that is relevant to the message. -
The
sequence
field defines the sequence in which messages should be ordered. -
The
query_text
field contains the first 2000 characters of the query text under analysis. -
The
message
field contains the text of message relevant to rewrite processing ofquery
. -
The
flags
,reserved1
, andreserved2
fields are reserved for future use.
Example 12-19 EXPLAIN_REWRITE Using a VARRAY
Consider the following materialized view:
CREATE MATERIALIZED VIEW avg_sales_city_state_mv ENABLE QUERY REWRITE AS SELECT c.cust_city, c.cust_state_province, AVG(s.amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_city, c.cust_state_province;
You might try to use this materialized view with the following query:
SELECT c.cust_state_province, AVG(s.amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_state_province;
However, the query does not rewrite with this materialized view. This can be quite confusing to a novice user as it seems like all information required for rewrite is present in the materialized view. You can find out from DBMS_MVIEW.EXPLAIN_REWRITE
that AVG
cannot be computed from the given materialized view. The problem is that a ROLLUP
is required here and AVG
requires a COUNT
or a SUM
to do ROLLUP
.
An example PL/SQL block for the previous query, using a VARRAY
as its output, is as follows:
SET SERVEROUTPUT ON DECLARE Rewrite_Array SYS.RewriteArrayType := SYS.RewriteArrayType(); querytxt VARCHAR2(1500) := 'SELECT c.cust_state_province, AVG(s.amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_state_province'; i NUMBER; BEGIN DBMS_MVIEW.EXPLAIN_REWRITE(querytxt, 'AVG_SALES_CITY_STATE_MV', Rewrite_Array); FOR i IN 1..Rewrite_Array.count LOOP DBMS_OUTPUT.PUT_LINE(Rewrite_Array(i).message); END LOOP; END; /
The following is the output of this EXPLAIN_REWRITE
statement:
QSM-01065: materialized view, AVG_SALES_CITY_STATE_MV, cannot compute measure, AVG, in the query QSM-01101: rollup(s) took place on mv, AVG_SALES_CITY_STATE_MV QSM-01053: NORELY referential integrity constraint on table, CUSTOMERS, in TRUSTED/STALE TOLERATED integrity mode PL/SQL procedure successfully completed.
12.8.2.4 EXPLAIN_REWRITE Benefit Statistics
The output of EXPLAIN_REWRITE
contains two columns, original_cost
and rewritten_cost
, that can help you estimate query cost. original_cost
gives the optimizer's estimation for the query cost when query rewrite was disabled. rewritten_cost
gives the optimizer's estimation for the query cost when query was rewritten using a materialized view. These cost values can be used to find out what benefit a particular query receives from rewrite.
12.8.2.5 Support for Query Text Larger than 32KB in EXPLAIN_REWRITE
In this release, the EXPLAIN_REWRITE
procedure has been enhanced to support large queries. The input query text can now be defined using a CLOB
data type instead of a VARCHAR
data type. This allows EXPLAIN_REWRITE
to accept queries up to 4 GB.
The syntax for using EXPLAIN_REWRITE
using CLOB
to obtain the output into a table is shown as follows:
DBMS_MVIEW.EXPLAIN_REWRITE( query IN CLOB, mv IN VARCHAR2, statement_id IN VARCHAR2);
The second argument, mv
, and the third argument, statement_id
, can be NULL
. Similarly, the syntax for using EXPLAIN_REWRITE
using CLOB
to obtain the output into a varray is shown as follows:
DBMS_MVIEW.EXPLAIN_REWRITE( query IN CLOB, mv IN VARCHAR2, msg_array IN OUT SYS.RewriteArrayType);
As before, the second argument, mv
, can be NULL
. Note that long query texts in CLOB
can be generated using the procedures provided in the DBMS_LOB
package.
12.8.2.6 About EXPLAIN_REWRITE and Multiple Materialized Views
The syntax for using EXPLAIN_REWRITE
with multiple materialized views is the same as using it with a single materialized view, except that the materialized views are specified by a comma-delimited string. For example, to find out whether a given set of materialized views mv1
, mv2
, and mv3
could be used to rewrite the query, query_txt
, and, if not, why not, use EXPLAIN_REWRITE
as follows:
DBMS_MVIEW.EXPLAIN_REWRITE(query_txt, 'mv1, mv2, mv3')
If the query, query_txt
, rewrote with the given set of materialized views, then the following message appears:
QSM-01127: query rewritten with materialized view(s), mv1, mv2, and mv3.
If the query fails to rewrite with one or more of the given set of materialized views, then the reason for the failure will be output by EXPLAIN_REWRITE
for each of the materialized views that did not participate in the rewrite.
12.8.2.7 About EXPLAIN_REWRITE Output
Some examples showing how to use EXPLAIN_REWRITE
are included in /rdbms/demo/smxrw.sql
. There is also a utility called SYS.XRW
included in the demo xrw
area to help you select the output from the EXPLAIN_REWRITE
procedure. When EXPLAIN_REWRITE
evaluates a query, its output includes information such as the rewritten query text, query block number, and the cost of the rewritten query. The utility SYS.XRW
outputs the user specified fields in a neatly formatted way, so that the output can be easily understood. The syntax is as follows:
SYS.XRW(list_of_mvs, list_of_commands, query_text),
where list_of_mvs
are the materialized views the user would expect the query rewrite to use. If there is more than one materialized view, they must be separated by commas, and list_of_commands
is one of the following fields:
QUERY_TXT: User query text REWRITTEN_TXT: Rewritten query text QUERY_BLOCK_NO: Query block number to identify each query blocks in case the query has subqueries or inline views PASS: Pass indicates whether a given message was generated before or after the view merging process of query rewrite. COSTS: Costs indicates the estimated execution cost of the original query and the rewritten query
The following example illustrates the use of this utility:
DROP MATERIALIZED VIEW month_sales_mv; CREATE MATERIALIZED VIEW month_sales_mv ENABLE QUERY REWRITE AS SELECT t.calendar_month_number, SUM(s.amount_sold) AS sum_dollars FROM sales s, times t WHERE s.time_id = t.time_id GROUP BY t.calendar_month_number; SET SERVEROUTPUT ON DECLARE querytxt VARCHAR2(1500) := 'SELECT t.calendar_month_number, SUM(s.amount_sold) AS sum_dollars FROM sales s, times t WHERE s.time_id = t.time_id GROUP BY t.calendar_month_number'; BEGIN SYS.XRW('MONTH_SALES_MV', 'COSTS, PASS, REWRITTEN_TXT, QUERY_BLOCK_NO', querytxt); END; /
Following is the output from SYS.XRW
. As can be seen from the output, SYS.XRW
outputs both the original query cost, rewritten costs, rewritten query text, query block number and whether the message was generated before or after the view merging process.
============================================================================ >> MESSAGE : QSM-01151: query was rewritten >> RW QUERY : SELECT MONTH_SALES_MV.CALENDAR_MONTH_NUMBER CALENDAR_MONTH_NUMBER, MONTH_SALES_MV.SUM_DOLLARS SUM_DOLLARS FROM SH.MONTH_SALES_MV MONTH_SALES_MV >> ORIG COST: 19.952763130792 RW COST: 1.80687108 ============================================================================ >> ------------------------- ANALYSIS OF QUERY REWRITE ------------------------- >> >> QRY BLK #: 0 >> MESSAGE : QSM-01209: query rewritten with materialized view, MONTH_SALES_MV, using text match algorithm >> RW QUERY : SELECT MONTH_SALES_MV.CALENDAR_MONTH_NUMBER CALENDAR_MONTH_NUMBER, MONTH_SALES_MV.SUM_DOLLARS SUM_DOLLARS FROM SH.MONTH_SALES_MV MONTH_SALES_MV >> ORIG COST: 19.952763130792 RW COST: 1.80687108 >> MESSAGE OUTPUT BEFORE VIEW MERGING... ============================ END OF MESSAGES =============================== PL/SQL procedure successfully completed.
12.9 Design Considerations for Improving Query Rewrite Capabilities
This section discusses design considerations that will help in obtaining the maximum benefit from query rewrite. They are not mandatory for using query rewrite and rewrite is not guaranteed if you follow them. They are general rules to consider, and are the following:
12.9.1 Query Rewrite Considerations: Constraints
Make sure all inner joins referred to in a materialized view have referential integrity (foreign key/primary key constraints) with additional NOT
NULL
constraints on the foreign key columns. Because constraints tend to impose a large overhead, you could make them NO
VALIDATE
and RELY
and set the parameter QUERY_REWRITE_INTEGRITY
to STALE_TOLERATED
or TRUSTED
. However, if you set QUERY_REWRITE_INTEGRITY
to ENFORCED
, all constraints must be enabled, enforced, and validated to get maximum rewritability.
You should avoid using the ON
DELETE
clause as it can lead to unexpected results.
12.9.2 Query Rewrite Considerations: Dimensions
You can express the hierarchical relationships and functional dependencies in normalized or denormalized dimension tables using the HIERARCHY
and DETERMINES
clauses of a dimension. Dimensions can express intra-table relationships which cannot be expressed by constraints. Set the parameter QUERY_REWRITE_INTEGRITY
to TRUSTED
or STALE_TOLERATED
for query rewrite to take advantage of the relationships declared in dimensions.
12.9.3 Query Rewrite Considerations: Outer Joins
Another way of avoiding constraints is to use outer joins in the materialized view. Query rewrite will be able to derive an inner join in the query, such as (A.a=B.b)
, from an outer join in the materialized view (A.a = B.b(+))
, as long as the rowid of B
or column B.b
is available in the materialized view. Most of the support for rewrites with outer joins is provided for materialized views with joins only. To exploit it, a materialized view with outer joins should store the rowid or primary key of the inner table of an outer join. For example, the materialized view join_sales_time_product_mv_oj
stores the primary keys prod_id
and time_id
of the inner tables of outer joins.
12.9.4 Query Rewrite Considerations: Text Match
If you need to speed up an extremely complex, long-running query, you could create a materialized view with the exact text of the query. Then the materialized view would contain the query results, thus eliminating the time required to perform any complex joins and search through all the data for that which is required.
12.9.5 Query Rewrite Considerations: Aggregates
To get the maximum benefit from query rewrite, make sure that all aggregates which are needed to compute ones in the targeted set of queries are present in the materialized view. The conditions on aggregates are quite similar to those for incremental refresh. For instance, if AVG(x)
is in the query, then you should store COUNT(x)
and AVG(x)
or store SUM(x)
and COUNT(x)
in the materialized view. See "General Restrictions on Fast Refresh" for fast refresh requirements.
12.9.6 Query Rewrite Considerations: Grouping Conditions
Aggregating data at lower levels in the hierarchy is better than aggregating at higher levels because lower levels can be used to rewrite more queries. Note, however, that doing so will also take up more space. For example, instead of grouping on state, group on city (unless space constraints prohibit it).
Instead of creating multiple materialized views with overlapping or hierarchically related GROUP
BY
columns, create a single materialized view with all those GROUP
BY
columns. For example, instead of using a materialized view that groups by city and another materialized view that groups by month, use a single materialized view that groups by city and month.
Use GROUP
BY
on columns that correspond to levels in a dimension but not on columns that are functionally dependent, because query rewrite will be able to use the functional dependencies automatically based on the DETERMINES
clause in a dimension. For example, instead of grouping on prod_name
, group on prod_id
(as long as there is a dimension which indicates that the attribute prod_id
determines prod_name
, you will enable the rewrite of a query involving prod_name
).
12.9.7 Query Rewrite Considerations: Expression Matching
If several queries share the same common subselect, it is advantageous to create a materialized view with the common subselect as one of its SELECT
columns. This way, the performance benefit due to precomputation of the common subselect can be obtained across several queries.
12.9.8 Query Rewrite Considerations: Date Folding
When creating a materialized view that aggregates data by folded date granules such as months or quarters or years, always use the year component as the prefix but not as the suffix. For example, TO_CHAR
(date_col
, 'yyyy-q'
) folds the date into quarters, which collate in year order, whereas TO_CHAR
(date_col, 'q-yyyy'
) folds the date into quarters, which collate in quarter order. The former preserves the ordering while the latter does not. For this reason, any materialized view created without a year prefix will not be eligible for date folding rewrite.
12.9.10 Query Rewrite Considerations: Hints
This section discusses the following considerations:
12.9.10.1 Query Rewrite: REWRITE and NOREWRITE Hints
You can include hints in the SELECT
blocks of your SQL statements to control whether query rewrite occurs. Using the NOREWRITE
hint in a query prevents the optimizer from rewriting it.
The REWRITE
hint with no argument in a query forces the optimizer to use a materialized view (if any) to rewrite it regardless of the cost. If you use the REWRITE(mv1,mv2,...)
hint with arguments, this forces rewrite to select the most suitable materialized view from the list of names specified.
To prevent a rewrite, you can use the following statement:
SELECT /*+ NOREWRITE */ p.prod_subcategory, SUM(s.amount_sold) FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_subcategory;
To force a rewrite using sum_sales_pscat_week_mv
(if such a rewrite is possible), use the following statement:
SELECT /*+ REWRITE (sum_sales_pscat_week_mv) */ p.prod_subcategory, SUM(s.amount_sold) FROM sales s, products p WHERE s.prod_id=p.prod_id GROUP BY p.prod_subcategory;
Note that the scope of a rewrite hint is a query block. If a SQL statement consists of several query blocks (SELECT
clauses), you must specify a rewrite hint on each query block to control the rewrite for the entire statement.
12.9.10.2 Query Rewrite: REWRITE_OR_ERROR Hint
Using the REWRITE_OR_ERROR
hint in a query causes the following error if the query failed to rewrite:
ORA-30393: a query block in the statement did not rewrite
For example, the following query issues an ORA-30393 error when there are no suitable materialized views for query rewrite to use:
SELECT /*+ REWRITE_OR_ERROR */ p.prod_subcategory, SUM(s.amount_sold) FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_subcategory;
12.9.10.3 Query Rewrite: Multiple Materialized View Rewrite Hints
There are two hints to control rewrites when using multiple materialized views. The NO_MULTIMV_REWRITE
hint prevents the query from being rewritten with more than one materialized view and the NO_BASETABLE_MULTIMV_REWRITE
hint prevents the query from being rewritten with a combination of materialized views and the base tables.
12.9.10.4 Query Rewrite: EXPAND_GSET_TO_UNION Hint
You can use the EXPAND_GSET_TO_UNION
hint to force expansion of the query with GROUP
BY
extensions into the equivalent UNION
ALL
query. See "Hint for Rewriting Queries with Extended GROUP BY" for further information.
12.10 About Semi-Join Materialized View Rewrite
Semi-Join Materialized View Rewrite is a unique form of query rewrite introduced in Oracle Database 23ai.
In Semi-Join Materialized View Rewrite, a single large unified dimension table in the query is replaced with one or more join-specific materialized views. In a unified dimension data model, where multiple dimension tables are merged into a single large dimension table, Semi-Join Materialized Views materialize one or more of the joins of such a single, large unified dimension table with the fact table.
Replacing multiple joins to a large dimension table with joins to much smaller semi-join materialized views significantly improves the run-time and resource consumption for complex analytical operations. Semi-Join Materialized Views are especially beneficial when the number of applicable dimension keys derived from the large unified dimension table (through semi-join) is small.
Here is a candidate for this optimization:
Figure 12-8 Candidate Dimension Table for Semi-Join Materialized View Rewrite
Description of "Figure 12-8 Candidate Dimension Table for Semi-Join Materialized View Rewrite"
Note that this is an extremely large dimension table and the NDVs of the foreign key join columns are small. A query against the fact table in this case can have a very long execution time. For example, the query below may take a few minutes to execute:
SELECT DB1.C4 A, DB2.C4 B, DB3.C4 C, DB4.C4 D, DS1.C2 E, DS2.C2 F,
SUM(M1) H, MAX(M2) I
FROM MY_FACT F
LEFT OUTER JOIN DIM_BIG DB1 ON F.DB_KEY1=DB1.C1
LEFT OUTER JOIN DIM_BIG DB2 ON F.DB_KEY2=DB2.C1
LEFT OUTER JOIN DIM_BIG DB3 ON F.DB_KEY3=DB3.C1
LEFT OUTER JOIN DIM_BIG DB4 ON F.DB_KEY4=DB4.C1
LEFT OUTER JOIN DIM_SMALL1 DS1 ON F.DS1_KEY1=DS1.C1
LEFT OUTER JOIN DIM_SMALL2 DS2 ON F.DS2_KEY1=DS2.C1
GROUP BY DB1.C4, DB2.C4, DB3.C4, DB4.C4, DS1.C2, DS2.C2;
Using the original query against the MY_FACT
table shown
in the example above, we use iterative materialized view rewrite to create a
materialized view for each join predicate between MY_FACT
and
DIM_BIG
. For example:
CREATE MATERIALIZED VIEW MV1
ENABLE QUERY REWRITE AS
SELECT * FROM DIM_BIG
WHERE C1 IN
(SELECT db_key1 from MY_FACT);
A materialized view like this is also be created for
db_key2
, db_key3
, and db_key4
in the MY_FACT
example.. The query is then rewritten as
follows:
SELECT MV1.C4 A,MV2.C4 B,MV3.C4 C,MV4.C4 D,SUM(F.M1),SUM(F.M2)
FROM MV1, MV2, MV3, MV4, MY_FACT F
WHERE F.DB_KEY1=MV1.C1(+) AND F.DB_KEY2=MV2.C1(+) AND F.DB_KEY3=MV3.C1(+)
AND F.DB_KEY4=MV4.C1(+) GROUP BY MV1.C4,MV2.C4,MV3.C4,MV4.C4
The execution time for this rewrite of the original query is reduced to a few seconds.