Skip to Main Content
  • Questions

  • How to make Materialized View unavailable.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Shiv.

Asked: January 28, 2016 - 10:54 am UTC

Last updated: February 01, 2016 - 11:20 am UTC

Version: 11r

Viewed 1000+ times

You Asked

Hi Tom,

We have Materialized Views in our project.
Due to some reason I want to make them unavailable for use.
I can see in Sybase there is direct query to make it disable.

ALTER MATERIALIZED VIEW EmployeeConfid55 DISABLE;


But in Oracle I can't find any such query. Can you please let me know is there any way through which I can make them unavailable .

Thanks,
Shiv

and Chris said...

I'm not sure what you mean by "unavailable".

If you mean stop Oracle using it for query rewrite then you can do this in a number of ways:

- Disabling query rewrite on the MV (alter materialized view mv disable query rewrite)
- Setting query_rewrite_enabled = false at the session or system level
- Adding a no_rewrite hint to your query

SQL> create table t as
  2    select rownum x from dual connect by level <= 1000;

Table created.

SQL>
SQL> create materialized view mv
  2  enable query rewrite as
  3    select count(*) from t;

Materialized view created.

SQL>
SQL> set autotrace trace exp
SQL> select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 572630632

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV   |     1 |     3 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

SQL>
SQL> alter materialized view mv disable query rewrite;

Materialized view altered.

SQL>
SQL> select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  1000 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------

SQL>
SQL> alter materialized view mv enable query rewrite;

Materialized view altered.

SQL>
SQL> select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 572630632

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV   |     1 |     3 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

SQL>
SQL> alter session set query_rewrite_enabled = false;

Session altered.

SQL>
SQL> select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  1000 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------

SQL>
SQL> alter session set query_rewrite_enabled = true;

Session altered.

SQL>
SQL> select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 572630632

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV   |     1 |     3 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

SQL> select /*+ no_rewrite */count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  1000 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------


If you mean, make it unusable, so you can't query it directly (select * from mv), then I believe you need to drop it.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

good pointers

Shiv, January 29, 2016 - 11:48 am UTC


Shiv, January 29, 2016 - 12:15 pm UTC

Thanks Chris for response.

We have a MView "MView_Data" and same MView is kept in two schema. Each schema is used for different application but MView_Data is used in only one of application for other it is just dummy but due to its default refresh (which is every alternate minute). it is hampering other application performance. This is a legacy system and we can't remove MView from other schema.

That's why we decided to stop its auto refresh and start it base on needed application at start up.

Approach I choose is.
1. Drop exiting MView_Data MView.
2.Recreate it with "BUILD DEFERRED" clause which will set "STALENESS" to "UNUSABLE" until complete refresh is done on it and no refresh will be invoked on it.
3. Based on application, Do complete refresh in application start up.(Here we may face performance impact if there is huge record in master table.)

Can you please tell me what will be performance impact in case if there is 1 million record in master table?
Chris Saxon
January 29, 2016 - 1:21 pm UTC

The performance of a complete refresh depends upon the driving query. Run the query you'll use in the MV on its own. This will give you an indicator of how long the complete refresh will take.

Can we restrict Materialized view to populate only last 1 month data?

Shiv, February 01, 2016 - 9:37 am UTC

Is there any way through which we restrict Materialized view to populate only last 1 month data?
Chris Saxon
February 01, 2016 - 11:20 am UTC

Add the restriction to the where clause in your MV query, e.g. 

where insert_date >= add_months(sysdate, -1)

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions