Test and failure of Oracle Materialized View

Test and failure of Oracle Materialized View

Description: The Materialized View (Materialized Views) is the query result set. Changes to all original tables and Views will invalidate the Materialized view, recently, I found a problem that materialized views often fail. The following are the documents.

1. materialized view status query: Oracle provides a view for querying the State USER_MVIEWS of materialized views. The column STALENESS is used to display the status of the current materialized view.

Relationship between the contents of the materialized view and the contents of the materialized view's masters:
• FRESH-Materialized view is a read-consistent view of the current state of its masters (the content of the current Materialized view is in the latest state)
• STALE-Materialized view is out of date because one or more of its masters has changed. if the materialized view was FRESH before it became STALE, then it is a read-consistent view of a former state of its masters. (obsolete state: the master table referenced by the materialized view has been updated, but the materialized view has not been refreshed, so the content is old as the master table)

• NEEDS_COMPILE-Some object upon which the materialized view depends has changed. an alter materialized view... COMPILE statement is required to validate this materialized view and compute the staleness of the contents. (compilation required: After the materialized view references a primary table, such as a view, the corresponding materialized view needs to be compiled after reconstruction. When the view is in this state, the STATUS displayed in dba_objects view is INVALID)

Run the statement alter materialized view MV_NAME COMPILE to re-COMPILE the statement;

• UNUSABLE-Materialized view is not a read-consistent view of its masters from any point in time (the status of the master table referenced by the Materialized view is unknown)
• UNKNOWN-Oracle Database does not know whether the materialized view is in a read-consistent view of its masters from any point in time (this is the case for materialized views created on prebuilt tables) (unknown: tables created through prebuilt)
• UNDEFINED-Materialized view has remote masters. The concept of staleness is not defined for such materialized views. (Materialized view referenced tables come from other databases and are generally linked through dblink)

Ii. Test the experiment:

2.1 create a Materialized View

Create materialized view MV_TEST (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

TABLESPACE USERS

PCTUSED 0

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64 K

NEXT 1 M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

NOCACHE

LOGGING

NOCOMPRESS

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE USERS

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64 K

NEXT 1 M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

REFRESH FORCE ON DEMAND

WITH PRIMARY KEY

AS

/* Formatted on 16:36:55 (QP5 v5.215.12089.000047 )*/

SELECT "EMP". "EMPNO" "EMPNO ",

"EMP". "ENAME" "ENAME ",

"EMP". "JOB" "JOB ",

"EMP". "MGR" "MGR ",

"EMP". "HIREDATE" "HIREDATE ",

"EMP". "SAL" "SAL ",

"EMP". "COMM" "COMM ",

"EMP". "DEPTNO" "DEPTNO"

FROM "SCOTT". "EMP" "EMP"

WHERE "EMP". "DEPTNO" = 20;

Comment on materialized view MV_TEST IS 'snapshot table for snapshot SCOTT. MV_TEST ';

Create unique index PK_EMP1 ON MV_TEST

(EMPNO)

LOGGING

TABLESPACE USERS

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64 K

NEXT 1 M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

NOPARALLEL;

 

2.2 query the status of the current Materialized View

 

SQL> SELECT mview_name, staleness FROM DBA_MVIEWS WHERE MVIEW_NAME = 'mv _ test ';

MVIEW_NAME STALENESS

-------------------------------------------------

MV_TEST FRESH
 

 

2.3 query the status of the database Materialized View

 

Column OBJECT_NAME format a20;

Column STATUS format a20; OBJECT_TYPE

Column OBJECT_TYPE format a20;

SQL> select OBJECT_NAME, STATUS, OBJECT_TYPE from dba_objects where OBJECT_NAME = 'mv _ test ';

OBJECT_NAME STATUS OBJECT_TYPE

------------------------------------------------------------

MV_TEST VALID TABLE

MV_TEST VALID MATERIALIZED VIEW
 

 

2.4 modify data in the source table

 

SQL> SELECT mview_name, staleness FROM DBA_MVIEWS WHERE MVIEW_NAME = 'mv _ test ';

MVIEW_NAME STALENESS

-------------------------------------------------

MV_TEST NEEDS_COMPILE

The display status needs to be compiled.

 

SQL> ALTER MATERIALIZED VIEW SCOTT. MV_TEST COMPILE;

Compile

 

SQL> SELECT mview_name, staleness FROM DBA_MVIEWS WHERE MVIEW_NAME = 'mv _ test ';

MVIEW_NAME STALENESS

-------------------------------------------------

MV_TEST STALE

After compilation, the status changes to STALE.

2.5 test the table structure modification function of the source table.

SQL> ALTER TABLE SCOTT. EMP RENAME COLUMN COMM TO COMMS; // modify the structure of the source TABLE

SQL> SELECT mview_name, staleness FROM DBA_MVIEWS WHERE MVIEW_NAME = 'mv _ test'; // view the status of the Materialized View

MVIEW_NAME STALENESS

-------------------------------------------------

MV_TEST NEEDS_COMPILE

 

SQL> ALTER MATERIALIZED VIEW SCOTT. MV_TEST COMPILE; // re-COMPILE

 

SQL> SELECT mview_name, staleness FROM DBA_MVIEWS WHERE MVIEW_NAME = 'mv _ test'; // the recompilation status remains unchanged;

 

MVIEW_NAME STALENESS

-------------------------------------------------

MV_TEST NEEDS_COMPILE

 

SQL> select OBJECT_NAME, STATUS, OBJECT_TYPE from dba_objects where OBJECT_NAME = 'mv _ test ';

OBJECT_NAME STATUS OBJECT_TYPE

------------------------------------------------------------

MV_TEST VALID TABLE

MV_TEST INVALID MATERIALIZED VIEW

Displays the status INVALID of the materialized view.
 

 

2.5 modify the structure of the source table to be consistent with the Materialized View

 

SQL> ALTER TABLE SCOTT. EMP RENAME COLUMN COMMS TO COMM; // modify the structure of the source TABLE

SQL> SELECT mview_name, staleness FROM DBA_MVIEWS WHERE MVIEW_NAME = 'mv _ test'; // you need to compile

MVIEW_NAME STALENESS

-------------------------------------------------

MV_TEST NEEDS_COMPILE

SQL> ALTER MATERIALIZED VIEW SCOTT. MV_TEST COMPILE; // recompile

Materialized view altered.

SQL> SELECT mview_name, staleness FROM DBA_MVIEWS WHERE MVIEW_NAME = 'mv _ test'; // The status changes to STALE.

MVIEW_NAME STALENESS

-------------------------------------------------

MV_TEST STALE

SQL> select OBJECT_NAME, STATUS, OBJECT_TYPE from dba_objects where OBJECT_NAME = 'mv _ test'; // The STATUS changes to VALID.

OBJECT_NAME STATUS OBJECT_TYPE

------------------------------------------------------------

MV_TEST VALID TABLE

MV_TEST VALID MATERIALIZED VIEW

Conclusion: when the source table of the materialized view is re-compiled, if the structure of the created table is not changed, then, after the script alter materialized view MV_NAME COMPILE is run, the status of the materialized view will be refreshed to be valid;

However, if the table structure changes, you need to modify the scripts of the materialized view to make the corresponding materialized view VALID. The status displayed by dba_objects is the VALID status;

Oracle performance problems caused by improper definition of materialized views

Oracle materialized view test

Connection-based materialized views of Oracle [refresh regularly]

Oracle materialized view creation reports ORA-00942 error resolution

Refresh the materialized view of Oracle stored procedures

Use of Oracle Materialized View