You may never want an MV to be refreshed. For example, you may want to guarantee that you have a snapshot of a table at a point in time for auditing purposes. Specify the NEVER REFRESH clause when you create the MV to achieve this:

SQL> create materialized view sales_mv never refresh asselect sales_id, sales_amt from sales;

If you attempt to refresh a nonrefreshable MV, you receive this error:

ORA-23538: cannot explicitly refresh a NEVER REFRESH materialized view

You can alter a never refreshable view to be refreshable. Use the ALTER MATERIALIZED VIEW statement to do this:

SQL> alter materialized view sales_mv refresh on demand complete;

You can verify the refresh mode and method with the following query:

SQL> select mview_name, refresh_mode, refresh_method from user_mviews;

Creating MVs for Query Rewrite

The query rewrite feature allows the optimizer to recognize that an MV can be used to fulfill the requirements of a query instead of using the underlying master (base) tables. If you have an environment in which users frequently write their own queries and are unaware of the available MVs, this feature can greatly help with performance. There are three prerequisites for enabling query rewrite:

•     Oracle Enterprise Edition

•     Setting database initialization parameter QUERY_REWRITE_ ENABLED to TRUE

•     MV either created or altered with the ENABLE QUERY REWRITE clause

This example creates an MV with query rewrite enabled:

SQL> create materialized view sales_daily_mv segment creation immediate refresh complete on demand enable query rewrite as select sum(sales_amt) sales_amt ,trunc(sales_dtt) sales_dtt from sales group by trunc(sales_dtt);

You can verify that query rewrite is in use by examining a query’s explain plan via the autotrace utility:

SQL> set autotrace trace explain

Now, suppose a user runs the following query, unaware that an MV exists that already aggregates the required data:

SQL> select sum(sales_amt) sales_amt,trunc(sales_dtt) sales_dtt from sales group by trunc(sales_dtt);

Here is a partial listing of autotrace output that verifies that query rewrite is in use:

—————————————————————————–| Id | Operation                                    | Name                   | Cost (%CPU)| Time                                                   | —————————————————————————–| 0 | SELECT STATEMENT                      |                             | 3        (0)     | 00:00:01 | | 1 | MAT_VIEW REWRITE ACCESS FULL | SALES_DAILY_MV | 3                                                             (0)                     | 00:00:01 | —————————————————————————–

As you can see from the prior output, even though the user selected directly from the SALES table, the optimizer determined that it could more efficiently satisfy the results of the query by accessing the MV.

You can tell if query rewrite is enabled for an MV by selecting the REWRITE_ENABLED column from USER_MVIEWS:

SQL> select mview_name, rewrite_enabled, rewrite_capability from user_mviews

where mview_name = ‘SALES_DAILY_MV’;

If for any reason a query is not using the query rewrite functionality and you think it should be, use the EXPLAIN_REWRITE procedure of the DBMS_MVIEW package to diagnose issues.