Why was I able to refresh Invalid MATERIALIZED VIEW in Oracle Database?

With this post, I am sharing, Why I was able to refresh an Invalid MATERIALIZED VIEW in Oracle Database and SELECT data from it after one of the base tables were altered, which made the MATERIALIZED VIEW invalid (as expected).

To demonstrate this, I am using my favorite table "EMP", Which has the following structure...
ngarg> desc emp
 Name                                  Null?    Type
 ------------------------------------ -------- -------- 
 EMPNO                                 NOT NULL NUMBER(4)
 ENAME                                          VARCHAR2(10)
 JOB                                            VARCHAR2(9)
 MGR                                            NUMBER(4)
 HIREDATE                                       DATE
 SAL                                            NUMBER(7,2)
 COMM                                           NUMBER(7,2)
 DEPTNO                                         NUMBER(2)

and has the following data...
ngarg> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81    5000.15                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81     2850.3                    30
      7782 CLARK      MANAGER         7839 09-JUN-81    2450.45                    10
      7566 JONES      MANAGER         7839 02-APR-81     2975.5                    20
      7788 SCOTT      ANALYST         7566 19-APR-87    3000.55                    20
      7902 FORD       ANALYST         7566 03-DEC-81     3000.6                    20
      7369 SMITH      CLERK           7902 17-DEC-80     800.75                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81     1600.9        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81    1250.05       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81    1500.95          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87    1100.01                    20
      7900 JAMES      CLERK           7698 03-DEC-81     950.99                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

So let us create a MATERIALIZED VIEW on top of EMP table...
ngarg> create materialized view emp_mv
  2  build deferred
  3  refresh force on demand
  4  with primary key
  5  as
  6  select * from emp;

Materialized view created.

EMP_MV has the same structure as expected...
ngarg> desc emp_mv
 Name                                  Null?    Type
 ------------------------------------ -------- -------- 
 EMPNO                                 NOT NULL NUMBER(4)
 ENAME                                          VARCHAR2(10)
 JOB                                            VARCHAR2(9)
 MGR                                            NUMBER(4)
 HIREDATE                                       DATE
 SAL                                            NUMBER(7,2)
 COMM                                           NUMBER(7,2)
 DEPTNO                                         NUMBER(2)

and refresh the materialized view...
ngarg> exec dbms_mview.refresh('emp_mv');

PL/SQL procedure successfully completed.

after which EMP_MV has the same data as of EMP table
ngarg> select * from emp_mv;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81    5000.15                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81     2850.3                    30
      7782 CLARK      MANAGER         7839 09-JUN-81    2450.45                    10
      7566 JONES      MANAGER         7839 02-APR-81     2975.5                    20
      7788 SCOTT      ANALYST         7566 19-APR-87    3000.55                    20
      7902 FORD       ANALYST         7566 03-DEC-81     3000.6                    20
      7369 SMITH      CLERK           7902 17-DEC-80     800.75                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81     1600.9        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81    1250.05       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81    1500.95          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87    1100.01                    20
      7900 JAMES      CLERK           7698 03-DEC-81     950.99                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Now, I am altering the EMP table, so that ENAME column can contains 10 characters no matter how many bytes each character has. The size of the ENAME was VARCHAR2(10 bytes). By default, VARCHAR2 uses NLS_LENGTH_SEMANTICS to determine the length type (byte or char).
ngarg> alter table emp modify ename varchar2(10 char);
Table altered.

ngarg> desc emp
 Name                                  Null?    Type
 ------------------------------------ -------- -------- 
 EMPNO                                 NOT NULL NUMBER(4)
 ENAME                                          VARCHAR2(10 CHAR)
 JOB                                            VARCHAR2(9)
 MGR                                            NUMBER(4)
 HIREDATE                                       DATE
 SAL                                            NUMBER(7,2)
 COMM                                           NUMBER(7,2)
 DEPTNO                                         NUMBER(2)

After this ALTER in EMP table, Oracle Database marked our MATERIALIZED VIEW as invalid.
ngarg> select object_name, object_type, status from user_objects where object_name in ('EMP','EMP_MV');

OBJECT_NAME            OBJECT_TYPE             STATUS
---------------------- ----------------------- -------
EMP_MV                 MATERIALIZED VIEW       INVALID
EMP_MV                 TABLE                   VALID
EMP                    TABLE                   VALID

You can notice here with EMP_MV name there are two objects, one is MATERIALIZED VIEW, second is a TABLE which is an actual segment and holds data for this MATERIALIZED VIEW.

Our MATERIALIZED VIEW is invalid but We are able to refresh INVALID MATERIALIZED VIEW, and SELECT data from it. To demonstrate this let us update one record in EMP table
ngarg> update emp set ename = 'NIMISH' where empno = 7839;
1 row updated.

ngarg> commit;
Commit complete.

and refresh the MATERIALIZED VIEW
ngarg> exec dbms_mview.refresh('emp_mv');

PL/SQL procedure successfully completed.

Hmmm, it got refreshed, does it has the latest data -
ngarg> select * from emp_mv;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 NIMISH     PRESIDENT            17-NOV-81    5000.15                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81     2850.3                    30
      7782 CLARK      MANAGER         7839 09-JUN-81    2450.45                    10
      7566 JONES      MANAGER         7839 02-APR-81     2975.5                    20
      7788 SCOTT      ANALYST         7566 19-APR-87    3000.55                    20
      7902 FORD       ANALYST         7566 03-DEC-81     3000.6                    20
      7369 SMITH      CLERK           7902 17-DEC-80     800.75                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81     1600.9        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81    1250.05       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81    1500.95          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87    1100.01                    20
      7900 JAMES      CLERK           7698 03-DEC-81     950.99                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Wow, Oracle database is smart enough and allows us to refresh even an INVALID MATERIALIZED VIEW, as long as SQL of MATERIALIZED VIEW is valid and data is valid for the structure created by MATERIALIZED VIEW (EMP_MV table).

Related Posts:
- Tune Complete Refresh of Materialized View by ATOMIC_REFRESH
- Oracle: Fast Refresh Materialized View with Joins
- ORA-23404 refresh group does not exist
- Top 18 features of Oracle 18c Database
- Top 15 features of Oracle 12.2 Database for developers
- Oracle Database 12c New Features for Developers

9 comments:

  1. Did you also try alter mat view compile command ?

    ReplyDelete
    Replies
    1. Yes, but it did not worked bcoz the definition of source column wasn't inline with mview

      Delete
  2. Hi Nimish,

    Small doubt here you didn't modify ename datatype and size. You simply execute alter statement "alter table emp modify ename varchar2(10 char);" due to this materialized view become invalid. Is this is same case when we add new column to emp table.
    ALTER TABLE emp ADD city VARCHAR(50);

    ReplyDelete
  3. Hi Nimish,
    Here you run the alter command i.e.alter table emp modify ename varchar2(10 char)
    which will make materialized view invalid.
    Is this the same case when we add column to table
    ALTER TABLE emp ADD city VARCHAR(50);

    ReplyDelete
    Replies
    1. Yes, adding a column in base table may also make mview invalid. but it can be resolved using alter materialized view emp_mv compile;

      Delete
    2. My Question is in this case also you are able to refresh an Invalid MATERIALIZED VIEW in Oracle Database

      Delete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Good article, but you did not answer the question "Why" (that is your title of the post)

    ReplyDelete