This module shows you how to use execution plans to make query rewrites with materialized views easier to interpret and use the REWRITE_OR_ERROR hint to make query rewrite debugging easier.
This module discusses the following topics:
|Generating Explain Plans and Interpreting the Results|
|Using the REWRITE_ON_ERROR Hint|
Using Explain Plans to Analyze Query Rewrites
Prior to Oracle Database 10g, external tables were read-only. In Oracle Database 10g, external tables can also be written to. Although neither data manipulation language (DML) operations nor index creation is allowed on an external table, you can use the CREATE TABLE AS SELECT command to populate an external table composed of proprietary format (Direct Path API) flat files that are operating system independent.
In the context of external tables, loading data refers to the act of data being read from an external table and loaded into a table in the database. Unloading data refers to the act of reading data from a table in the database and inserting it into an external table. Both these operations can be used with external tables using the new Data Pump access driver.
There may be situations where you want to stop the query from executing if it did not rewrite. One such situation can be when you expect the un-rewritten query to take an unacceptably long time to execute. To support this requirement, Oracle Database 10g provides a new hint called
REWRITE_OR_ERROR. This is a query block-level hint. For example, if the
SELECT statement is not rewritten, the error displayed in the message is thrown. The
REWRITE_OR_ERROR hint allows you to run
DBMS_MVIEW.EXPLAIN_REWRITE() on the query, resolve the problems that caused rewrite to fail, and run the query again.
Before starting this module, you should have:
Completed the Configuring Linux for the Installation of Oracle Database 10g lesson
Completed the Installing the Oracle Database 10g on Linux lesson
Completed the Postinstallation Tasks lesson.
Downloaded and unzipped mvplans.zip into your working directory (for example, /home/oracle/wkdir)
It is common practice to use naming conventions for materialized views (MVs); for example, to distinguish MVs from regular tables in execution plans. Oracle Database 10g improves this situation by providing better information in the PLAN_TABLE and in the V$SQL_PLAN view; they show MATERIALIZED VIEW instead of TABLE. Moreover, they show the difference between MV usage as a result of query rewrite and direct MV access. Perform the following steps:
Create the materialized view and gather statistics on the materialized view and its underlying tables. From your terminal window, execute the following commands:
cd wkdir sqlplus sh/sh @mvsetup
The query in the mvsetup.sql script is as follows:
drop materialized view sales_prod;
Now you can generate an execution plan for a query that will be rewritten. Execute the following script from your terminal window:
The query in the execplan01.sql script is as follows:
EXPLAIN PLAN FOR
Now that the execution plan is generated, you can use the DBMS_XPLAN package to display the execution plan. From your terminal window, execute the following command:
SELECT * FROM table(dbms_xplan.display);
Note the execution plan explicitly shows materialized view usage and the purpose ( REWRITE).
To see what would happen if the materialized view was explicitly accessed in the FROM component of a query, execute the following command:
EXPLAIN PLAN FOR SELECT * FROM sales_prod;
Now you can display the execution plan again to see the difference. From your terminal window, execute the following command:
SELECT * FROM table(dbms_xplan.display);
Note the execution plan still shows MAT_VIEW access (as opposed to TABLE access) but the REWRITE is gone.
There may be situations where you want to stop a query from executing if it did not rewrite. One such situation is when you expect the un-rewritten query to take an unacceptably long time to execute. To support this requirement, Oracle Database 10g provides a new hint called REWRITE_OR_ERROR. If a query is not rewritten, error ORA-30393 is thrown. This feature allows you to run DBMS_MVIEW.EXPLAIN_REWRITE() on the query, resolve the problems that caused rewrite to fail, and run the query again. To obtain EXPLAIN_REWRITE output into a table, you must run the utlxrw.sql script before calling EXPLAIN_REWRITE. This script creates a table named REWRITE_TABLE in the current schema. Perform the following steps:
Before you begin you need to re-create your rewrite table. The utlxrw.sql script creates the REWRITE table. You need this table to capture the output of the EXPLAIN_REWRITE procedure. From your terminal window, execute the following command:
The command in the crewrt01 .sql script is as follows:
drop table rewrite_table;
Typically when a query rewrite fails, the Oracle database executes the statement against the underlying base tables. The REWRITE_OR_ERROR hint changes the behavior, and generates an error message when query rewrite fails. To use a hint in a query, execute the following command from your terminal window:
The command in the hint01.sql script is as follows:
SELECT /*+ REWRITE_OR_ERROR */
Note that an error message was generated.
You can use the EXPLAIN_REWRITE procedure to determine why query rewrite failed. The results will be captured in the REWRITE_TABLE you created previously. Execute the following script:
The command in the exprewrt 01.sql script is as follows:
execute dbms_mview.EXPLAIN_REWRITE -
Now you can query the REWRITE_TABLE to obtain results. Execute the following script:
The command in the shresult 01.sql script is as follows:
Note that there are three issues with the query.
To see where the issues are, look at the materialized view definition you created at the beginning of this lesson as follows:
CREATE MATERIALIZED VIEW sales_prod build immediate enable query rewrite as SELECT s.prod_id , t.fiscal_month_number , sum(s.amount_sold) AS sum_amount FROM sales s, times t WHERE s.time_id = t.time_id AND t.fiscal_year = 2000 GROUP BY s.prod_id, t.fiscal_month_number;
Then you need to look at the query you executed as follows:
SELECT s.prod_id , sum(s.quantity_sold) FROM sales s GROUP BY s.prod_id;
As you can see, the materialized view utilizes the TIMES dimension, which is not used in the query, and the materialized view aggregates AMOUNT_SOLD while the query is aggregating QUANTITY_SOLD. Therefore query rewrite is impossible.