Query Rewrite Feature

Query Rewrite can be very useful in Data warehouse where we have tables with huge amounts of data, more than millions. When we run query on these huge tables we might have to join with other tables like date or time and with all these join its going to take a lot of time to fetch the results.

Here we can make use of Query rewrite. Query rewrite will transform our SQL statements in terms of tables or views into a statement accessing one or more materialized views.

Points to be taken care of while using Query Rewrite Feature

• DBMS_MVIEW.EXPLAIN_REWRITE procedure tells us whether Query rewrite is possible.

• Query rewrite runs on following queries:

SELECT
CREATE TABLE … AS SELECT
INSERT INTO … SELECT

• Also, Query Rewrite must be enabled at the session level

Alter session set query_rewrite_enabled = TRUE;

• A materialized view must be enabled for query rewrite:

Syntax:
CREATE MATERIALIZED VIEW SUM_AMT
ENABLE QUERY REWRITE AS
SELECT ...