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...
and has the following data...
So let us create a MATERIALIZED VIEW on top of EMP table...
EMP_MV has the same structure as expected...
and refresh the materialized view...
after which EMP_MV has the same data as of EMP table
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).
After this ALTER in EMP table, Oracle Database marked our MATERIALIZED VIEW as invalid.
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
and refresh the MATERIALIZED VIEW
Hmmm, it got refreshed, does it has the latest data -
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
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
Did you also try alter mat view compile command ?
ReplyDeleteYes, but it did not worked bcoz the definition of source column wasn't inline with mview
DeleteI'm learning it.
DeleteHi Nimish,
ReplyDeleteSmall 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);
Hi Nimish,
ReplyDeleteHere 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);
Yes, adding a column in base table may also make mview invalid. but it can be resolved using alter materialized view emp_mv compile;
DeleteMy Question is in this case also you are able to refresh an Invalid MATERIALIZED VIEW in Oracle Database
DeleteThis comment has been removed by the author.
ReplyDeleteGood article, but you did not answer the question "Why" (that is your title of the post)
ReplyDelete