Technology BUSINESS INTELLIGENCE
Improve Results with Query Rewrite
By Susan Hillson,Lilian Hobbs, and Shilpa Lawande
Boost performance without changing your SQL queries.
Are you tired of waiting for your queries to return results? Have you already tried adding indexes and tuning the SQL, and still can't improve query performance? Well, have you considered creating materialized views? With materialized views, reports that used to take hours to run can complete in minutes. A materialized view can contain joins and aggregates, providing a way of storing precomputed results.
When a query executes, the optimizer decides whether it is faster to access the materialized view or the underlying tables where the data resides. If the optimizer decides that querying the materialized view is the better solution, the optimizer rewrites the SQL query in a process known as query rewrite. This process occurs without requiring a change to any SQL or application code, so any application or ad hoc query tool that uses SQL to access the database can benefit by using the materialized view. Query rewrite works best where the amount of data accessed to compute the result is significantly greater than the size of the result, such as aggregation; however, it can
also be used to speed up expensive
joins or projections.
This article introduces the types of query rewrites the optimizer can perform. It then discusses tools to help determine the best set of materialized views to create for enabling the optimizer to rewrite many queries. Materialized views created using these tools are also fast-refreshable when the underlying data on which the materialized view is based changes. If you don't know whether it would be better to create a materialized view, an index, or both, the SQL Access Advisor,
introduced in Oracle Database 10g,
can help you decide by analyzing a given workload.
Query Rewrite Types
Many types of query rewrite are possible; the simplest and most obvious type occurs when the materialized view's defining query matches the query's text exactly. However, the greatest benefit of query rewrite is realized when the same materialized view can be used to answer several queries. We will now illustrate some of the rules the Oracle optimizer uses to determine whether it will use a materialized view to answer a query.
For the examples in this article, consider a star schema with the PURCHASES table as the fact table, range partitioned by time_key. The dimension tables—TIME, PRODUCT, and CUSTOMERS—have primary keys time_key, product_id, and cust_id. There are foreign-key constraints in the PURCHASES table referencing each of the dimension tables. (See "Next Steps" to download the code to create these tables.)
Consider the materialized view created in Listing 1 that computes the sum of sales and the total sales by month by product_id. Note that for a materialized view to be used for query
rewrite, it must have the ENABLE QUERY REWRITE clause. Also,
the initialization parameter QUERY_REWRITE_ENABLED must be
set to TRUE.
Aggregate Computation
In the article examples, we will show queries of materialized views and display the execution plan obtained from EXPLAIN PLAN. The query in Listing 2 asks for the average purchase price by month and product. The optimizer
can use the materialized view, monthly_sales_mv, by using the
SUM and COUNT aggregates to compute the AVG. This example illustrates a technique called aggregate computation.
Joinback
The joinback technique is very useful because it allows query rewrite to
occur when a column is not present in the materialized view. The query in Listing 3 asks for total sales by month
by product category, and the column product.category is not present in
the materialized view. However, the product_id column, the primary key of the product table, is in the materialized view. Hence, the optimizer can join the materialized view with the product table to obtain the category.
Query Rewrite Using a Dimension
In a typical data warehouse designed using dimensional modeling techniques, well-known hierarchical relationships exist in the data. For instance, in the time hierarchy, days roll up into months, which in turn roll up into years. In Oracle Database you can create an object called a DIMENSION using the CREATE DIMENSION statement to declare such relationships to the optimizer. The dimension object is a descriptive object that consumes no space other than for its metadata. Relationships declared using a DIMENSION object are said to be trusted. Oracle will not verify that the relationship actually holds true in your data and will assume that the DBA has determined these relationships to be correct. Other examples of trusted information are constraints using the NOVALIDATE RELY flag and preexisting tables registered as materialized views.
For query rewrite to use trusted information, including dimensions,
the initialization parameter QUERY_ REWRITE_INTEGRITY must be set to
TRUSTED as follows:
ALTER SESSION SET query_rewrite_integrity = TRUSTED;
For example, suppose you have a time dimension declared as follows:
CREATE DIMENSION time_dim
LEVEL time_key IS time.time_key
LEVEL month IS time.month
LEVEL quarter IS time.quarter
LEVEL year IS time.year
HIERARCHY calendar_rollup (
time_key CHILD OF
month CHILD OF
quarter CHILD OF
year
)
ATTRIBUTE time_key determines (day_of_week, holiday)
ATTRIBUTE month determines (month_name);
Now, if you have the query in Listing 4 that asks for sales by year, you can still use the monthly_sales_mv materialized view, because the HIERARCHY clause in the dimension object tells Oracle Database that monthly sales can be rolled up into yearly sales. It uses the joinback technique described earlier to obtain the values for the year column from the month column in the materialized view.
The ATTRIBUTE clause of the dimension specifies one-to-one relationships. For example, you can determine
what day of the week it was from the time_key. Suppose you want the sum
of sales for January each year: You can still use the monthly_sales_mv materialized view as shown in Listing 5. Notice how the WHERE clause of the query has
a selection condition, which is not present in the materialized view.
If the optimizer does not rewrite a query as expected, use the DBMS_MVIEW .EXPLAIN_REWRITE procedure to diagnose the problem. This feature is available in Oracle9i Database and later releases.
Filtered Data
All the examples we have shown so far use a materialized view corresponding to all the data in the purchases table. Oracle9i Database introduced the ability to rewrite a query when the materialized view has only a subset of the data. For example, if you are interested only in sales for 1997 through 2002, you could modify the materialized view as follows:
CREATE MATERIALIZED VIEW five_yr_monthly_sales_mv
ENABLE QUERY REWRITE
AS
SELECT t.month, p.product_id,
SUM(ps.purchase_price) as sum_of_sales,
COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id AND
t.year between 1997 and 2002
GROUP BY t.month, p.product_id;
This materialized view can be used to answer queries asking for data from 1997 through 2002. For example, the query in Listing 6 asks for sales for 2000.
In Oracle9i Database, if the data requested by the query is not entirely present in the materialized view, the query does not use the materialized view. In Oracle Database 10g, this restriction has been relaxed so that query rewrite will obtain as much data as possible from the materialized view and use the detail tables to obtain the data not in the materialized view. As always, the optimizer's decision to perform this action takes into consideration the costs of the query with and without rewrite.
For instance, the query in Listing 7 asks for monthly sales between 2000 and 2003 and will use the materialized view for 2000 to 2002 and the detail tables only for 2003.
Query Rewrite with
Stale Materialized Views
You may wonder what happens if the data in the detail tables changes. Will query rewrite still use the materialized view? The answer depends on the setting of the initialization parameter QUERY_REWRITE_ INTEGRITY. The QUERY_REWRITE_INTEGRITY parameter can take three values:
- STALE_TOLERATED means that the materialized view will be used even when the data in the detail tables has changed.
- TRUSTED means that a materialized view will be used provided it is not stale. However, query rewrite may use trusted relationships such as those declared by dimension objects or constraints that have not been validated.
- ENFORCED (default) means that a materialized view will be used provided it guarantees to give the same result as using the detail tables. Using this parameter means the query rewrite will use no stale materialized views or trusted relationships.
The appropriate setting depends on the data needs of the application. Query rewrite using a stale materialized view may produce different results from those obtained without using query rewrite. However, by using detail data instead, performance may worsen due to the large amount of data processed to answer the query. In a data warehouse, the TRUSTED integrity level is typically used, because you are guaranteed that only materialized views with the latest data are used; however, relationships declared to be correct (trusted) are also available for query rewrite. In most data warehouses, such relationships are already verified during extraction, transformation, and loading (ETL) and hence do not need to be validated again.
Partition Change Tracking
With Oracle9i Database, Oracle introduced Partition Change Tracking (PCT). With this feature, Oracle9i Database can keep track of which portions of the materialized view correspond to the updated portions of partitioned detail tables. Thus, if a query does not require the portion of the table that has been updated, the materialized view can still be used.
For changes to a detail table to be tracked in a materialized view, the table must be partitioned and the materialized view must include (in the SELECT list) the detail table's partition key or a special function, DBMS_MVIEW.PMARKER. This function generates a unique identifier for each partition in the detail table.
For example, the purchases table is partitioned by time_key. The materialized view created in Listing 8 is almost the same as the monthly_sales_mv materialized view used earlier but includes an additional DBMS_MVIEW.PMARKER function on the purchases table. By including this function, this materialized view now allows PCT when the purchases table is updated. Notice that the materialized view itself does not need to be partitioned.
Now, suppose we add a new partition for April 2003 to the purchases table, and a user issues a query that requests data for March 2002 as shown in Listing 9. The updated data for April 2003 is not of interest to this query, so it will be rewritten using the materialized view, even though the materialized view is stale.
If the query were asking for data from January through April, in Oracle9i, the query would not be rewritten to use the materialized view. However, in Oracle Database 10g, the query would be rewritten using a combination of MONTHLY_SALES_ PCT_MV and the detail tables.
Query Rewrite with Multiple Materialized Views
As mentioned earlier, in Oracle10g Database, query rewrite has been enhanced so it can answer a query using part of the data from a materialized view and the remaining data from the detail tables. In fact, query rewrite may use a combination of two or more materialized views. For instance, suppose you maintain a separate materialized view for every five years' worth of data: monthly_sales_1990-1994, monthly_sales_1995_to_2000, monthly_sales_2001_to_2005, and so on.
Then, for the query in Listing 10 that requests data from 1993 to 2003, query rewrite can use all three materialized views.
The query in Listing 11 requests data from years 1989 to 1999, so query rewrite can use the materialized views monthly_sales_1990_to_1994 and monthly_sales_1995_to_2000 and obtain the data for 1989 from the detail tables. This process can be substantially quicker than getting all the data from the detail tables.
Oracle10g Database has several other improvements to query rewrite. Notable among these enhancements are better support for set operators (UNION, UNIONALL, etc.), enhanced support for queries involving multiple instances of a table, and support for LIST and RANGE-LIST partitioning types in partition change tracking.
Tools
You may be reading this article and saying to yourself, "Well, I think I understand what you are saying, but isn't there some tool that could do all this for me?" The answer is yes. In fact, there are quite a few of them.
Oracle9i Database introduced explain_mview and explain_rewrite. The application-programming interface (API) EXPLAIN_MVIEW takes a materialized-view definition and advises what types of partition-change-tracking operations are available, whether fast refresh is possible, and which types of query rewrite can be done. In the case of a query that is not rewritten, the API EXPLAIN_REWRITE will advise why the SQL query is not using query rewrite. In both cases, the packages will tell you what the problem is—for example, can't join on a certain column, but neither package will tell you exactly how to fix it. This is where two more new tools—TUNE_MVIEW and the SQL Access Advisor, included in Oracle10g Database—come to your rescue.
The TUNE_MVIEW API will tell you how to write your materialized view so it is fast refreshable and can use as many as possible of the advanced types of query rewrite described in this article. The TUNE_MVIEW API is very easy to use: Just give it your materialized-view statement and it will determine the most optimum form for the materialized view. However, don't be surprised if you see that your original materialized view has been transformed into more than one new version.
Let's see how TUNE_MVIEW can transform your materialized view. Suppose we have a simple query and we pass it to EXPLAIN_MVIEW, as shown in Listing 12, to determine whether the materialized view can fast refresh it in its current form.
Let's now take the same query and pass it into TUNE_MVIEW, as shown in the following code:
variable task_name varchar2(2000);
BEGIN
DBMS_ADVISOR.TUNE_MVIEW (:task_name,
'CREATE MATERIALIZED VIEW customer_mv
BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE
AS
SELECT c.customer_id, c.town,
COUNT(DISTINCT(product_id)) AS dist_promo_cnt
FROM purchases ps, customer c
WHERE ps.customer_id = c.customer_id
GROUP BY c.customer_id, c.town');
END;
/
The catalog view USER_TUNE_MVIEW will show the resulting materialized view as shown in Listing 13. Although it looks a little different from our original materialized view, this one can be used to rewrite any query where the original materialized view could have been used and, in addition, is fast refreshable.
You can also generate a script to implement these recommendations, and the only modifications you might want to make are to change the name of the materialized view and to specify a storage clause and tablespace for where the materialized view should be located.
So far, we have always had a materialized view, but what if we don't know what materialized views to create? This is where the SQL Access Advisor can help, because it will look at your system and make recommendations as to the indexes and materialized views it thinks are needed.
These recommendations are made based on either an actual workload or a hypothetical one it constructs from your schema. The best results will occur when an actual workload of SQL statements is provided; this workload can be obtained from the current contents of the SQL cache, a SQL Tuning Set, a an Oracle9i Summary Advisor workload, or a user-supplied workload table, which comprises the SQL statements you have defined.
The SQL Access Advisor can be used either via the command-line API or using the SQL Access Advisor wizard, which is part of Enterprise Manager. Using the wizard, you must complete only three steps before the recommendations are displayed. Let's see how to use the SQL Access Advisor via the command-line interface:
First, create a task, which is where all the information for this tuning process is held. The task will then use the workload information in order to generate its recommendations, which are stored as part of the task. Therefore, the entire process is entirely self-contained and allows each task to be slightly different so that one can see the effect of making changes to the configuration. In the example shown in Listing 14, the workload is defined by manually defining the SQL statements.
Once the workload and the task have been defined, we can generate the recommendations as shown below, using EXECUTE_TASK and specifying the name of the task we created, Task_mag:
execute dbms_advisor.execute_task ('Task_mag');
Depending on the complexity of the workload, the time to generate the recommendations could run from a few seconds to several minutes. Therefore, although this process can be run interactively, you may want to consider submitting a job, which is what the wizard in Enterprise Manager will do.
You can quickly check whether there are recommendations by querying the table USER_ADVISOR_RECOMMENDATIONS for your task_name. When we do that for this example, we see that one recommendation has been made.
SELECT 'No of Recommendations:' , COUNT(*)
FROM user_advisor_recommendations r
WHERE task_name='Task_mag';
'NOOFRECOMMENDATIONS:' COUNT(*)
---------------------- ----------
No of Recommendations: 1
A single recommendation can result in a number of actions. For this example, the SQL Access Advisor recommends creating materialized view logs, one CREATE MATERIALIZED VIEW, and a call to analyze the materialized view (which, because of space limitations, is not shown here).
Although you can query various catalog views to see these actions, the easiest way to see them is to generate a script, as illustrated below:
execute
dbms_advisor.create_file(dbms_advisor.get_task_script('Task_mag'),
'ADVISOR_RESULTS', 'mag_example.sql');
In Listing 15, you can see an excerpt from the script, showing the materialized view recommended for our query.
Conclusion
By using query rewrite, you can use a few materialized views to significantly improve performance of many queries, thus reducing disk-space utilization and the refresh time needed to keep the materialized views synchronized with the underlying detail data.
Susan Hillson (susan.hillson@oracle.com) is a senior director of engineering at Oracle. Lilian Hobbs (lilian.hobbs@oracle.com) is the product manager of Oracle's Summary Management team. Shilpa Lawande (shilpa.lawande@oracle.com) is a principal developer at Oracle. The authors have written Oracle9iR2 Data Warehousing, available from Digital Press (www.digitalpressbooks.com).
Bookmark
