Database Oracle - Query Rewrite

> Database > Oracle Database

1 - About

Advertising

3 - Enable Query Rewrite

To enable query rewrite, the following conditions must be met:

  • Individual materialized views must have the ENABLE QUERY REWRITE clause.
  • The QUERY_REWRITE_ENABLED initialization parameter must be set to TRUE. Alternatively you can set this parameter to FORCE; this will deactivate any costing evaluation of a rewritten plan and will rewrite a query whenever possible.
  • The rewrite integrity mode and the status of a particular materialized view must match to enable the rewrite with this particular materialized view.
ALTER SESSION SET query_rewrite_integrity=TRUSTED;
 
ALTER SESSION SET query_rewrite_enabled=FORCE;
 
show parameters query 

query_rewrite_integrity

  • TRUSTED

You are enabling query rewrite and are using the so-called “trusted” mode. This is the most commonly used integrity level. In trusted mode, the optimizer trusts that the data in the materialized views is fresh and the relationships declared in dimensions and RELY constraints are correct. In this mode, the optimizer will also use prebuilt materialized views or materialized views based on views, and it will use relationships that are not enforced as well as those that are enforced. In this mode, the optimizer also trusts declared but not ENABLED VALIDATED primary/unique key constraints and data relationships specified using dimensions.

  • ENFORCED

Queries will be rewritten using only constraints and rules that are enforced and guaranteed by Oracle. There are mechanisms by which we can tell Oracle about other inferred relationships, and this would allow for more queries to be rewritten, but since Oracle does not enforce those relationships, it would not make use of these facts at this level.

  • STALE_TOLERATED

Queries will be rewritten to use materialized views even if Oracle knows the data contained in the materialized view is ' stale ' (out-of-sync with the details). This might be useful in an environment where the summary tables are refreshed on a recurring basis, not on commit, and a slightly out-of-sync answer is acceptable.

Grant

grant query rewrite TO scott;
Advertising

4 - Query Rewrite Capabilities of Oracle

The optimizer uses a number of different methods to rewrite a query. The first, most important step is to determine if all or parts of the results requested by the query can be obtained from the precomputed results stored in a materialized view.

The simplest case occurs when the result stored in a materialized view exactly matches what is requested by a query. The Oracle optimizer makes this type of determination by comparing the text of the query with the text of the materialized view definition. This method is most straightforward but the number of queries eligible for this type of query rewrite will be minimal.

When the text comparison test fails, the Oracle 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.

4.1 - Using Partial Text Match Rewrite

The most simple rewrite mechanism is the 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. When full text match fails, the optimizer then attempts a partial text match. In this method, the text starting with the FROM clause of a query is compared against the text starting with the FROM clause of a materialized view definition.

Rem REWRITE
DELETE FROM plan_table;
COMMIT;
 
EXPLAIN PLAN FOR
   SELECT c.cust_id,
          SUM(amount_sold) AS dollar_sales
   FROM   sales s, customers c
   WHERE  s.cust_id= c.cust_id
   GROUP BY c.cust_id;
 
SET linesize 132
SET pagesize 999
SELECT * FROM TABLE(DBMS_XPLAN.display);

The plan below shows that the query is rewritten with the cust_sales_mv materialized view, using the partial text match rewrite mechanism; starting with the FROM clause, the SQL statement and the materialized view are identical.

While the query is rewritten, the access plan for a materialized view is investigated like the access of a normal table, so that any existing indexes might be used.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
--
Plan hash value: 626465932

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |  7057 |   179K|     7   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| CUST_SALES_MV |  7057 |   179K|     7   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

12 rows selected.

Executing the query delivers a result very quickly, because it only has to access the already joined and aggregated information in cust_sales_mv.

SET timing ON
 
SELECT c.cust_id,
       SUM(amount_sold) AS dollar_sales
FROM   sales s, customers c
WHERE  s.cust_id= c.cust_id
GROUP BY c.cust_id;
     12600     49980.28
      8400      26976.6
      7220      7693.54
     13740      3812.55
     11780     10565.68

7057 rows selected.

Elapsed: 00:00:01.17
SQL>

4.2 - Using a Simple Join Back Rewrite

When the text comparison test fails, the Oracle 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. The query does not always have to match exactly for query rewrite to occur.

For example, suppose your materialized view is grouped by cust_id but your query groups on cust_last_name. Query rewrite is still possible using what is known as a join back method.

The following is a simple example for a join back rewrite. The cust_sales_mv materialized view stores the cust_id join column for joining with customers the same way that the sales and customers tables determine the value of cust_credit_limit.

DELETE FROM plan_table;
COMMIT;
 
EXPLAIN PLAN FOR
  SELECT c.cust_last_name,
    c.cust_credit_limit,
    SUM(amount_sold) AS dollar_sales
  FROM sales s, customers c 
  WHERE s.cust_id= c.cust_id
  GROUP BY c.cust_last_name,
    c.cust_credit_limit
  ORDER BY 1;
 
SET linesize 140
SELECT * FROM TABLE(DBMS_XPLAN.display);

Plan :

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 2930846342

--------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |  5137 |   215K|       |   411   (3)| 00:00:05 |
|   1 |  SORT GROUP BY                 |               |  5137 |   215K|   688K|   411   (3)| 00:00:05 |
|*  2 |   HASH JOIN                    |               |  7057 |   296K|       |   342   (3)| 00:00:05 |
|   3 |    MAT_VIEW REWRITE ACCESS FULL| CUST_SALES_MV |  7057 |   179K|       |     7   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL           | CUSTOMERS     | 55500 |   921K|       |   333   (2)| 00:00:04 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"."CUST_ID"="CUST_SALES_MV"."CUST_ID")

Note
-----
   - dynamic sampling used for this statement

20 rows selected.

Elapsed: 00:00:00.25

You can see in the plan that Oracle uses the cust_sales_mv materialized view and joins it back to the customers table using the cust_id column, which is part of the materialized view and represents the primary key-foreign key relationship between the sales and customers tables.

Without query rewrite, you have to process the join between your complete sales fact table and the customers dimension table.

Furthermore, the requested attribute cust_last_name in the query is a determined attribute from cust_id, so that the system knows that no additional aggregation on the Dimensional Modeling - Dimension site must take place and it only has to join back the materialized view to the customers table.

The information about hierarchies and determined attributes is part of customers_dim, the Oracle dimension object for the customers dimension. The important part of the dimension definition customers_dim is shown below:

    LEVEL customer IS (customers.cust_id)
    …
    ATTRIBUTE customer DETERMINES
    (cust_first_name, cust_last_name, cust_credit_limit, cust_gender, ...
Advertising

4.3 - Rewrite Using Join Back and Rollup

Besides a simple join back of materialized views, requesting the same aggregation level of information, materialized views can also be aggregated to a higher level the so-called ROLLUP operation. Consider the following query:

DELETE FROM plan_table;
COMMIT;
 
EXPLAIN PLAN FOR
   SELECT   c.cust_state_province,
            SUM(amount_sold) AS dollar_sales
   FROM     sales s, customers c
   WHERE    s.cust_id= c.cust_id
   GROUP BY c.cust_state_province;
 
SET linesize 132
SET pagesize 999
SELECT * FROM TABLE(DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 1245940963

------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |   145 |  6090 |   343   (3)| 00:00:05 |
|   1 |  HASH GROUP BY                 |               |   145 |  6090 |   343   (3)| 00:00:05 |
|*  2 |   HASH JOIN                    |               |  7057 |   289K|   341   (3)| 00:00:05 |
|   3 |    MAT_VIEW REWRITE ACCESS FULL| CUST_SALES_MV |  7057 |   179K|     7   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL           | CUSTOMERS     | 55500 |   867K|   332   (2)| 00:00:04 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"."CUST_ID"="CUST_SALES_MV"."CUST_ID")

Note
-----
   - dynamic sampling used for this statement

20 rows selected.

You can see in the plan that Oracle uses the cust_sales_mv materialized view and joins it back to the customers table, using the cust_id column, which is part of the materialized view and represents the primary key-foreign key relationship between the sales and customers tables.

However, the existence of a possible join key column does not necessarily represent all the information, the query rewrite mechanism needs for guaranteeing data integrity.

Examine the customers_dim dimension definition:

    LEVEL customer  IS (customers.cust_id)
    LEVEL city      IS (customers.cust_city)
    LEVEL state     IS (customers.cust_state_province)
    . . .
    HIERARCHY geog_rollup (
          customer     CHILD OF
          city         CHILD OF
          state        CHILD OF
    . . .

The requested cust_state_province attribute represents the so-called level state, a higher aggregation level than customer, represented by cust_id. Levels and hierarchies represent a declarative way for representing a 1:n relationship inside one table. In this case, it expresses the validity of aggregating all customer information to the level state without violating data integrity. For every distinct customer value, you will get one and only one state value.

4.4 - Rewrite Using a Complex Join Back and Rollup

The following example demonstrates the power and flexibility of Oracle's query rewrite capabilities. The following example not only does a join back, it uses the information in the customers_dim dimension to join back over two tables in a snowflake schema.

DELETE FROM plan_table;
COMMIT;
 
EXPLAIN PLAN FOR
   SELECT   co.country_name, 
            c.cust_state_province, SUM(amount_sold) AS dollar_sales 
   FROM     sales s, customers c, countries co
   WHERE    s.cust_id = c.cust_id 
     AND    c.country_id = co.country_id 
   GROUP BY co.country_name, c.cust_state_province 
   ORDER BY 1,2;
 
SET linesize 132
SET pagesize 999
SELECT * FROM TABLE(DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash value: 592022229

---------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |  2359 |   142K|       |   408   (3)| 00:00:05 |
|   1 |  SORT GROUP BY                  |               |  2359 |   142K|   680K|   408   (3)| 00:00:05 |
|*  2 |   HASH JOIN                     |               |  7057 |   427K|       |   345   (3)| 00:00:05 |
|   3 |    TABLE ACCESS FULL            | COUNTRIES     |    23 |   345 |       |     3   (0)| 00:00:01 |
|*  4 |    HASH JOIN                    |               |  7057 |   323K|       |   341   (3)| 00:00:05 |
|   5 |     MAT_VIEW REWRITE ACCESS FULL| CUST_SALES_MV |  7057 |   179K|       |     7   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL           | CUSTOMERS     | 55500 |  1138K|       |   333   (2)| 00:00:04 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C"."COUNTRY_ID"="CO"."COUNTRY_ID")
   4 - access("C"."CUST_ID"="CUST_SALES_MV"."CUST_ID")

Note
-----
   - dynamic sampling used for this statement

23 rows selected.

The optimizer rewrites the query to take advantage of the cust_sales_mv materialized view and joins it back to customers and customers to countries to satisfy the query. In the same way that you enforced a query on the statement level not to be rewritten you can enforce the use of a specific materialized view.

Various data integrity checks must take place to guarantee the validity of using this materialized view. Besides the check for losslessness and nonduplicating joins, the evaluation of the dimension information plays an important role for the rewrite process.

Examine what the optimizer uses to rewrite this query. The following is an excerpt of the dimension definition of customers_dim. It shows the important parts for this query:

    LEVEL customer    IS (customers.cust_id)
    LEVEL . . .
    LEVEL state       IS (customers.cust_state_province)
    LEVEL country     IS (countries.country_id)
    . . .
    HIERARCHY geog_rollup (
           customer      CHILD OF
           . . .
           state         CHILD OF
           country       CHILD OF
    . . .
    JOIN KEY (customers.country_id) REFERENCES country
    )
    . . .
    ATTRIBUTE country DETERMINES (countries.country_name)

The Oracle database has to determine whether or not it can derive all requested attributes based on the information that is stored in the materialized view. You are requesting countries.country_name and customers.cust_state_province in your query; the materialized view contains only cust_id as information.

Based on the information in the customers_dim dimension, the Oracle database determines the following:

  • customers.cust_state_province can be determined by cust_id in the materialized view. It represents a higher aggregation level in the dimension than level customers.
  • countries.country_id can also be determined by cust_id in the materialized view. countries.country_id describes a higher aggregation level than customers.
  • countries.country_name is a determined attribute of the hierarchical level country and can therefore be determined based on countries.country_id.
  • The customers_dim dimension describes a hierarchical dependency across two tables. The join condition is part of the dimension information.

The Oracle database uses all of this information to join the materialized view not only with customers, but customers also with countries to get the result for the query and to guarantee that the query result is correct.

4.5 - Enhanced Rewrite Capabilities with the GROUP BY Extensions

Oracle9i introduced extensions to the GROUP BY clause in the form of GROUPING SETS, ROLLUP, and their concatenation. These extensions enable you to selectively specify the groupings of interest in the GROUP BY clause of the query.

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 all GROUP BY expressions. For example, if the GROUP BY clause of the materialized view is GROUP BY CUBE(a, b), then the SELECT list should contain GROUPING_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.

DROP MATERIALIZED VIEW sales_cube_mv;
 
CREATE MATERIALIZED VIEW sales_cube_mv
   ENABLE QUERY REWRITE
   AS
   SELECT calendar_year YEAR, calendar_quarter_desc quarter, 
          calendar_month_desc MONTH, cust_state_province state, 
          cust_city city,
          GROUPING_ID (calendar_year,calendar_quarter_desc,
                       calendar_month_desc,
                       cust_state_province,cust_city) gid,
          GROUPING(calendar_year) grp_y,
          GROUPING(calendar_quarter_desc) grp_q,
          GROUPING(calendar_month_desc) grp_m,
          GROUPING(cust_state_province) grp_s,
          GROUPING(cust_city) grp_c,
          SUM(amount_sold) sum_sales
   FROM sales s, times t, customers c
   WHERE s.time_id=t.time_id AND s.cust_id=c.cust_id
   GROUP BY GROUPING SETS ((calendar_year, cust_city),
                           (calendar_year, cust_city, 
                            cust_state_province),
                           (calendar_year, calendar_quarter_desc, 
                            calendar_month_desc,cust_city));
 
exec DBMS_STATS.gather_table_stats('SH','sales_cube_mv');
 
DROP MATERIALIZED VIEW sales_gby_mv;
 
CREATE MATERIALIZED VIEW sales_gby_mv
   ENABLE QUERY REWRITE
   AS
   SELECT   calendar_year YEAR, cust_state_province state,
            SUM(amount_sold) sum_sales
   FROM     sales s, times t, customers c
   WHERE    s.time_id=t.time_id AND s.cust_id=c.cust_id
   GROUP BY (calendar_year, cust_state_province);
 
exec DBMS_STATS.gather_table_stats('SH','sales_gby_mv');

The following query contains exactly the same GROUPING SETS than the created materialized view. You see that the query is rewritten as-is against the materialized view.

DELETE FROM plan_table;
COMMIT;
 
EXPLAIN PLAN FOR
  SELECT calendar_year YEAR, calendar_quarter_desc quarter,
         calendar_month_desc MONTH,
         cust_state_province state,
         SUM(amount_sold) sum_sales
  FROM   sales s, times t, customers c
  WHERE  s.time_id=t.time_id
    AND  s.cust_id=c.cust_id
  GROUP BY GROUPING SETS ((calendar_year, cust_city),
           (calendar_year, cust_city, cust_state_province),
           (calendar_year, calendar_quarter_desc,
            calendar_month_desc, cust_city));
 
PROMPT NEW output, using TABLE FUNCTION
SET linesize 132
SET pagesize 999
SELECT * FROM TABLE(DBMS_XPLAN.display);

Output :

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
--------------------------------
Plan hash value: 3203911263

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               | 24168 |   613K|    45   (5)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| SALES_CUBE_MV | 24168 |   613K|    45   (5)| 00:00:01 |
----------------------------------------------------------------------------------------------

8 rows selected.

We have a grouping match.

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. The grouping match takes place in this example; a full table scan of the materialized view, without any filter conditions, satisfies our query.

The following query contains different GROUPING SETS than the materialized view definition. Furthermore, it select a column from table customers, country_id, that is not part of the materialized view.

PROMPT full access AND joinback TO dimension customers
PROMPT DECOMPOSE OF GROUPING levels INTO UNION ALL
 
DELETE FROM plan_table;
COMMIT;
 
EXPLAIN PLAN FOR
  SELECT   calendar_year YEAR, calendar_quarter_desc quarter,
           cust_state_province state, country_id,
           SUM(amount_sold) sum_sales
  FROM     sales s, times t, customers c
  WHERE    s.time_id=t.time_id
    AND    s.cust_id=c.cust_id
  GROUP BY GROUPING SETS ((calendar_year, country_id),
                          (calendar_year, cust_state_province),
                          (calendar_year, calendar_quarter_desc,
                           cust_state_province));
 
PROMPT NEW output, using TABLE FUNCTION
SET linesize 132
SET pagesize 999
SELECT * FROM TABLE(DBMS_XPLAN.display);

Output :

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2699856904

------------------------------------------------------------------------------------------------------------------------------------
-

| Id  | Operation                      | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop
|

------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                            | 97936 |  6312K|       |  2235   (5)| 00:00:27 |       |
|   1 |  VIEW                          |                            | 97936 |  6312K|       |  2235   (5)| 00:00:27 |       |
|   2 |   UNION-ALL                    |                            |       |       |       |         |     |       |
|   3 |    MAT_VIEW REWRITE ACCESS FULL| SALES_GBY_MV               |   532 | 10640 |       |     3   (0)| 00:00:01 |       |
|   4 |    TEMP TABLE TRANSFORMATION   |                            |       |       |       |         |     |       |
|   5 |     LOAD AS SELECT             |                            |       |       |       |         |     |       |
|*  6 |      HASH JOIN                 |                            |   918K|    51M|       |  2224   (5)| 00:00:27 |       |
|   7 |       TABLE ACCESS FULL        | TIMES                      |  1826 | 36520 |       |    15   (0)| 00:00:01 |       |
|*  8 |       HASH JOIN                |                            |   918K|    34M|  1792K|  2192   (4)| 00:00:27 |       |
|   9 |        TABLE ACCESS FULL       | CUSTOMERS                  | 55500 |  1138K|       |   333   (2)| 00:00:04 |       |
|  10 |        PARTITION RANGE ALL     |                            |   918K|    15M|       |   434  (11)| 00:00:06 |     1 |    20
|  11 |         TABLE ACCESS FULL      | SALES                      |   918K|    15M|       |   434  (11)| 00:00:06 |     1 |    20
|  12 |     LOAD AS SELECT             |                            |       |       |       |         |     |       |
|  13 |      HASH GROUP BY             |                            |     1 |    57 |       |     3  (34)| 00:00:01 |       |
|  14 |       TABLE ACCESS FULL        | SYS_TEMP_0FD9D6612_13767B3 |     1 |    57 |       |     2   (0)| 00:00:01 |       |
|  15 |     LOAD AS SELECT             |                            |       |       |       |         |     |       |
|  16 |      HASH GROUP BY             |                            |     1 |    39 |       |     3  (34)| 00:00:01 |       |
|  17 |       TABLE ACCESS FULL        | SYS_TEMP_0FD9D6612_13767B3 |     1 |    39 |       |     2   (0)| 00:00:01 |       |
|  18 |     VIEW                       |                            |     1 |    70 |       |     2   (0)| 00:00:01 |       |
|  19 |      TABLE ACCESS FULL         | SYS_TEMP_0FD9D6613_13767B3 |     1 |    70 |       |     2   (0)| 00:00:01 |       |

------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("SYS_TBL_$3$"."TIME_ID"="SYS_TBL_$2$"."TIME_ID")
   8 - access("SYS_TBL_$3$"."CUST_ID"="SYS_TBL_$1$"."CUST_ID")

32 rows selected.

In this case the 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 (LOAD AS SELECT). The branch will have a simple GROUP BY clause.

To satisfy the above shown query with the existing materialized view, Oracle has rewrite the GROUPING SETS into three simple GROUP BY expressions, combined with a UNION ALL operator. It then investigates the rewrite capabilities for each of the UNION ALL branches independently. Each of the branches might be rewritten with a materialized view containing a simple or an extended GROUP BY condition.

All basic rewrite mechanisms, such as JOIN BACK are being used.

The following query contains different GROUPING SETS than the materialized view definition; one of the GROUPING SETS cannot be resolved with any existing materialized view, so that Oracle has to join back to the detail tables to satisfy this query.

PROMPT full access OF MV AND usage OF SALES fact ofr missing LEVEL
PROMPT DECOMPOSE OF GROUPING levels INTO UNION ALL
 
DELETE FROM plan_table;
COMMIT;
 
EXPLAIN PLAN FOR
  SELECT   calendar_year YEAR, calendar_quarter_desc quarter,
           week_ending_day,cust_state_province state,
           SUM(amount_sold) sum_sales
  FROM     sales s, times t, customers c
  WHERE    s.time_id=t.time_id
    AND    s.cust_id=c.cust_id
  GROUP BY GROUPING SETS ((calendar_year),
                          (calendar_year, week_ending_day),
                          (calendar_year, cust_state_province),
                          (calendar_year, calendar_quarter_desc,
                           cust_state_province));
 
PROMPT NEW output, using TABLE FUNCTION
SET linesize 132
SET pagesize 999
SELECT * FROM TABLE (DBMS_XPLAN.display);

Output :

|  17 |     LOAD AS SELECT              |                            |       |       |       |         |             |       |
|  18 |      HASH GROUP BY              |                            |     1 |    35 |       |     3  (34)| 00:00:01 |       |
|  19 |       TABLE ACCESS FULL         | SYS_TEMP_0FD9D6616_13767B3 |     1 |    35 |       |     2   (0)| 00:00:01 |       |
|  20 |     VIEW                        |                            |     1 |    66 |       |     2   (0)| 00:00:01 |       |
|  21 |      TABLE ACCESS FULL          | SYS_TEMP_0FD9D6617_13767B3 |     1 |    66 |       |     2   (0)| 00:00:01 |       |
------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("SYS_TBL_$3$"."TIME_ID"="SYS_TBL_$2$"."TIME_ID")
  10 - access("SYS_TBL_$3$"."CUST_ID"="SYS_TBL_$1$"."CUST_ID")

34 rows selected.

5 - Hint

5.1 - Norewrite hint

The plan for the nonwritten statement can be enforced by using the NOREWRITE hint. This gives you the control down to statement level, whether a query is rewritten or not.

DELETE FROM plan_table;
COMMIT;
 
EXPLAIN PLAN FOR
   SELECT   /*+ norewrite */
            c.cust_id,
            SUM(amount_sold) AS dollar_sales
   FROM     sales s, customers c
   WHERE    s.cust_id= c.cust_id
   GROUP BY c.cust_id;
 
SET linesize 132
SET pagesize 999
SELECT * FROM TABLE(DBMS_XPLAN.display);

Without Oracle’s query rewrite capabilities, you would have to do the full scan from SALES and the join with customers.

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 3783035061

---------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |  7059 |   103K|       |  2300  (10)| 00:00:28 |       |       |
|   1 |  HASH GROUP BY         |              |  7059 |   103K|    21M|  2300  (10)| 00:00:28 |       |       |
|*  2 |   HASH JOIN            |              |   918K|    13M|       |   480  (14)| 00:00:06 |       |       |
|   3 |    INDEX FAST FULL SCAN| CUSTOMERS_PK | 55500 |   270K|       |    28   (4)| 00:00:01 |       |       |
|   4 |    PARTITION RANGE ALL |              |   918K|  8973K|       |   434  (11)| 00:00:06 |     1 |    20 |
|   5 |     TABLE ACCESS FULL  | SALES        |   918K|  8973K|       |   434  (11)| 00:00:06 |     1 |    20 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("S"."CUST_ID"="C"."CUST_ID")

17 rows selected.

Elapsed: 00:00:00.20

When we run the query.

     13740      3812.55
     11780     10565.68

7057 rows selected.

Elapsed: 00:00:02.87

5.2 - REWRITE_OR_ERROR

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.

ALTER MATERIALIZED VIEW cust_sales_aggr disable query rewrite;
ALTER MATERIALIZED VIEW  cust_sales_mv disable query rewrite;
ALTER MATERIALIZED VIEW  sales_cube_mv disable query rewrite;
ALTER MATERIALIZED VIEW  sales_gby_mv disable query rewrite;
 
SELECT /*+ REWRITE_OR_ERROR */ c.cust_last_name,
  c.cust_credit_limit,
  SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_last_name,
  c.cust_credit_limit
ORDER BY 1;

The following statement will not be rewritten with any materialized view and consequently, the query will fail when you leverage the new REWRITE_OR_ERROR capabilities. Output :

     *
ERROR at line 4:
ORA-30393: a query block in the statement did not rewrite