Friday, March 14, 2014

INVALID Materialized View NEEDS_COMPILE

This example was tested on Oracle 11.2. It shows how an on demand materialized view becomes invalid following DML on the underlying table. First I created a table:

SQL> create table tab1 (col1 number)
  2  /
 
Table created.
 
SQL>

Then I inserted the value 1 into it: 

SQL> insert into tab1 values (1)
  2  /
 
1 row created.
 
SQL>

I created an on demand materialized view on the table: 

SQL> create materialized view mv1
  2  refresh on demand
  3  as select * from tab1
  4  /
 
Materialized view created.
 
SQL>

I checked that it was VALID:

SQL> select object_type, status
  2  from user_objects
  3  where object_name = 'MV1'
  4  /
 
OBJECT_TYPE         STATUS
------------------- -------
TABLE               VALID
MATERIALIZED VIEW   VALID
 
SQL>

I updated the table, making the materialized view out of date: 

SQL> update tab1 set col1 = 2
  2  /
 
1 row updated.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> select * from tab1
  2  /
 
      COL1
----------
         2
 
SQL> select * from mv1
  2  /
 
      COL1
----------
         1
 
SQL>

Although you can still query the materialized view when this happens, it shows as INVALID:

SQL> select object_type, status
  2  from user_objects
  3  where object_name = 'MV1'
  4  /
 
OBJECT_TYPE         STATUS
------------------- -------
TABLE               VALID
MATERIALIZED VIEW   INVALID
 
SQL>

... and in need of compilation:

SQL> select mview_name, compile_state
  2  from user_mviews
  3  /
 
MVIEW_NAME                     COMPILE_STATE
------------------------------ -------------------
MV1                            NEEDS_COMPILE
 
SQL>

This isn't a problem but, if you are not happy with it, you can refresh the materialized view:

SQL> exec dbms_mview.refresh('MV1');
 
PL/SQL procedure successfully completed.
 
SQL>

This brings it in line with the underlying table:

SQL> select * from mv1
  2  /
 
      COL1
----------
         2
 
SQL>

It is then shown as VALID again:

SQL> select object_type, status
  2  from user_objects
  3  where object_name = 'MV1'
  4  /
 
OBJECT_TYPE         STATUS
------------------- -------
TABLE               VALID
MATERIALIZED VIEW   VALID
 
SQL>

... and not in need of compilation:

SQL> select mview_name, compile_state
  2  from user_mviews
  3  /
 
MVIEW_NAME                     COMPILE_STATE
------------------------------ -------------------
MV1                            VALID
 
SQL>

I updated the table, making the materialized view out of date again: 

SQL> update tab1 set col1 = 3
  2  /
 
1 row updated.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> select * from tab1
  2  /
 
      COL1
----------
         3
 
SQL> select * from mv1
  2  /
 
      COL1
----------
         2

SQL>

It was also INVALID and in need of compilation as before:
 
SQL> select object_type, status
  2  from user_objects
  3  where object_name = 'MV1'
  4  /
 
OBJECT_TYPE         STATUS
------------------- -------
TABLE               VALID
MATERIALIZED VIEW   INVALID
 
SQL> select mview_name, compile_state
  2  from user_mviews
  3  /
 
MVIEW_NAME                     COMPILE_STATE
------------------------------ -------------------
MV1                            NEEDS_COMPILE
 
SQL>

Another way to change this, if it bothers you, is to compile it:

SQL> alter materialized view mv1 compile
  2  /
 
Materialized view altered.
 
SQL>

This way, the materialized view is still out of date: 

SQL> select * from mv1
  2  /
 
      COL1
----------
         2
 
SQL>

... but it is no longer INVALID: 

SQL> select object_type, status
  2  from user_objects
  3  where object_name = 'MV1'
  4  /
 
OBJECT_TYPE         STATUS
------------------- -------
TABLE               VALID
MATERIALIZED VIEW   VALID
 
SQL>

... and it does not need compiling either:

SQL> select mview_name, compile_state
  2  from user_mviews
  3  /
 
MVIEW_NAME                     COMPILE_STATE
------------------------------ -------------------
MV1                            VALID
 
SQL>

4 comments:

  1. Hi,

    Did you find why this happens? is their a workaround for it?

    ReplyDelete
  2. Did you find out why this happens? would this become a problem?

    ReplyDelete
  3. I'm not sure why it happens. It could just be Oracle's way of telling you that the materialized view is out of date. I would need to do more research to find out for certain.

    ReplyDelete
  4. Anonymous12:54 am

    This is expected behavior.
    Check Oracle Note 264036.1

    --
    Dmitriy Kotkov

    ReplyDelete