REWRITE and NO_REWRITE Hint

Introduction

Oracle's REWRITE hint forces the optimizer to consider rewriting a specific SQL query to use a materialized view, instead of the underlying base tables.  Specifically, it tells the optimizer to use materialized views where possible.  The idea is to boost performance by using pre-calculated results of the MV. 

Example

SELECT /*+ REWRITE */ col1 

  FROM myTab1

  WHERE myTab1.col1= 100

/

OR

SELECT /*+ REWRITE(myMv1, myMv2) */ col1 

  FROM myTab1

  WHERE myTab1.col1= 100

/

You can specify a list of materialized view names within the hint  as shown above e.g. /*+ REWRITE(mv1, mv2) */. This limits the scope of the optimizer's search to only those listed MV's. If you omit the list of MV's, then the optimizer will consider all eligible materialized views. 

Important Notes

• If the optimizer finds any eligible materialized view, whether from a specified list or not, it will use that materialized view regardless of the cost implications. Cost is not a factor when the REWRITE hint is used.

• Even if the QUERY_REWRITE_ENABLED initialization parameter is set to false, the hint will override this and Oracle will attempt to rewrite the query. 

• The materialized view must be created with ENABLE QUERY REWRITE to be considered eligible

Detailed Example

You have a large sales table  on which you frequently run aggregate queries to get the total sales per region. To optimize performance, you create a materialized view that pre-aggregates the data. See below.

CREATE MATERIALIZED VIEW sales_mv

BUILD IMMEDIATE

REFRESH COMPLETE

ENABLE QUERY REWRITE AS

SELECT region, SUM(amount) AS total_sales

FROM sales

GROUP BY region

/

Selecting data from this table with the initialization parameter QUERY_REWRITE_ENABLED set to false, means the query will not be re-written

SELECT region, SUM(amount)

FROM sales

GROUP BY region

/

Assuming, no indexes on the sales table you would a plan similar to the following

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

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

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

|   0 | SELECT STATEMENT   |       |  1000 | 10000 |   50  (10) | 00:00:01         |

|   1 |  HASH GROUP BY     |       |  1000 | 10000 |   50  (10) | 00:00:01         |

|   2 |   TABLE ACCESS FULL| SALES | 10000 | 100K  |   40  (15) | 00:00:02         |

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

If you were to run the query with the hint

SELECT /*+ REWRITE(sales_mv) */ region, SUM(amount)

FROM sales

GROUP BY region

/

then the query plan would look something like this

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

| Id  | Operation            | Name               | Rows  | Bytes | Cost (%CPU)    |

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

|   0 | SELECT STATEMENT     |                    |  1000 | 10000 |    5  (1)      |

|   1 |  INDEX FULL SCAN     | IDX_SALES_MV_REGION|  1000 | 10000 |    5  (1)      |

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

Obviously, this query with and without the hint, could be improved with standard indexing too, but the principle of how the optimizer re-writes the query is shown in the plan above,.

Note

The NO_REWRITE hint instructs the optimizer to disable query rewrite for the query block, overriding the setting of the initialization  parameter QUERY_REWRITE_ENABLED = TRUE

Example

SELECT /*+ NO_REWRITE */ col1 

  FROM myTab1

  WHERE myTab1.col1= 100

/

Further Reading

• You can find more information on using the hint in the documentation 

• You can find more information on query rewrite for materialized views here

Published on 

Published 14th February 2025