When base tables contain large amount of data, it is expensive and time-consuming to compute the required aggregates or to compute joins between these tables. In such cases, queries can take minutes or even hours. Because materialized views contain already precomputed aggregates and joins, Oracle Database employs an extremely powerful process called query rewrite to quickly answer the query using materialized views.
One of the major benefits of creating and maintaining materialized views is the ability to take advantage of query rewrite, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables. The transformation is transparent to the end user or application, requiring no intervention and no reference to the materialized view in the SQL statement. Because query rewrite is transparent, materialized views can be added or dropped just like indexes without invalidating the SQL in the application code.
A query undergoes several checks to determine whether it is a candidate for query rewrite. If the query fails any of the checks, then the query is applied to the detail tables rather than the materialized view. This can be costly in terms of response time and processing power.
The optimizer uses two different methods to recognize when to rewrite a query in terms of a materialized view. The first method is based on matching the SQL text of the query with the SQL text of the materialized view definition. If the first method fails, the optimizer uses the more general method in which it compares joins, selections, data columns, grouping columns, and aggregate functions between the query and materialized views.
The DBMS_MVIEW.EXPLAIN_REWRITE procedure advises whether query rewrite is possible on a query and, if so, which materialized views are used. It also explains why a query cannot be rewritten.
A query is rewritten only when a certain number of conditions are met :-
• Query rewrite must be enabled for the session.
• A materialized view must be enabled for query rewrite.
• The rewrite integrity level should allow the use of the materialized view. For example, if a materialized view is not fresh and query rewrite integrity is set to ENFORCED, then the materialized view is not used.
• Either all or part of the results requested by the query must be obtainable from the precomputed result stored in the materialized view or views.
SQL> CREATE MATERIALIZED VIEW LOG ON t3 WITH PRIMARY KEY,ROWID,SEQUENCE (t_key,amt) INCLUDING NEW VALUES;
Materialized view log created.
SQL> CREATE MATERIALIZED VIEW mv
2 REFRESH FAST ON COMMIT
3 AS
4 SELECT t_key,MAX(amt) amt_max
5 FROM t3
6 GROUP BY t_key;
Materialized view created.
SQL> SELECT * FROM mv ORDER BY t_key;
T_KEY AMT_MAX
---------- ----------
1 300
2 250
SQL> SELECT t_key,MAX(amt) amt_max
2 FROM t3
3 GROUP BY t_key;
T_KEY AMT_MAX
---------- ----------
1 300
2 250
SQL> ALTER MATERIALIZED VIEW mv ENABLE QUERY REWRITE;
Materialized view altered.
-- Note that materialized views which do not include the ENABLE QUERY REWRITE clause will have Query Rewrite disabled by default.
SQL> EXECUTE dbms_stats.gather_table_stats( USER, 'MV' );
PL/SQL procedure successfully completed.
SQL> SHOW PARAMETER QUERY_REWRITE_ENABLED
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT t_key,MAX(amt) amt_max
2 FROM t3
3 GROUP BY t_key;
T_KEY AMT_MAX
---------- ----------
1 300
2 250
Execution Plan
----------------------------------------------------------
Plan hash value: 572630632
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV | 2 | 14 | 3 (0)| 00:00:01 |
-- Note how the optimizer chose to access MV for its pre-calculated MAX(AMT) values in line 1 even though the query itself made no mention of MV.
SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED = FALSE;
Session altered.
SQL> SHOW PARAMETER QUERY_REWRITE_ENABLED
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string FALSE
SQL> SELECT t_key,MAX(amt) amt_max
2 FROM t3
3 GROUP BY t_key;
T_KEY AMT_MAX
---------- ----------
1 300
2 250
Execution Plan
----------------------------------------------------------
Plan hash value: 3640192438
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 2 | 14 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T3 | 5 | 35 | 3 (0)| 00:00:01 |
--Note how the optimizer chose to access T3 this time. Each time this query is executed it has to re-calculate MAX(VAL) from the information in T3 for each group, a more expensive approach than simply selecting pre-calculated column values from MV is.Materialized views with the following characteristics cannot have query rewrite enabled :-
• The defining query references functions which are not DETERMINISTIC.
• An expression in the defining query is not repeatable; e.g. an expression containing the USER pseudo column or the SYSTIMESTAMP function.
SQL> CREATE MATERIALIZED VIEW mv6
2 ENABLE QUERY REWRITE
3 AS
4 SELECT key, val,USER from t;
SELECT key, val,USER from t
*
ERROR at line 4:
ORA-30353: expression not supported for query rewrite
