In-depth research on the materialized view (mview) of Oracle III

In-depth research on the materialized view (mview) of Oracle III
In-depth research on the materialized view (mview) of Oracle III

Author: anysql.net. During reprinting, please mark it as a hyperlink Article Original Source and author information.
Link: http://www.anysql.net/oracle/star_in_mview.html

 

When creating a view in Oracle, if we use the "*" symbol, it will be automatically extended to the field list according to the definition of the current table. When we add a column later, new columns are not automatically displayed in the view definition until you recreate the view. in mview, an interesting problem is found through a casual operation.In short, do not use the "*" number in the definition of the object view..

The following describes how to create two materialized views on a table:

Create Table t_mvtest as select * From tab;
Create materialized view log on t_mvtest
With rowid, sequence;
Create materialized view mv_test_star
Refresh fast with rowid
As select rowid RID, A. * From t_mvtest;
Create materialized view mv_test_nostar
Refresh fast with rowid
As select rowid RID, A. tname, A. tabtype,
A. clusterid from t_mvtest;

Next, add a column to the table.

SQL> ALTER TABLE t_mvtest add col4 varchar2 (20 );

Table altered.

Next, we will completely refresh the two materialized views to see the running situation:

SQL> exec dbms_mview.refresh ('mv _ test_star ', 'complete ');
Begin dbms_mview.refresh ('mv _ test_star ', 'complete'); end;

*
Error at line 1:
ORA-12018: Following Error encountered during code generation
"Anysql". "mv_test_star"
ORA-00904: "col4": Invalid identifier
ORA-06512: At "SYS. dbms_snapshot", line 2255
ORA-06512: At "SYS. dbms_snapshot", line 2461
ORA-06512: At "SYS. dbms_snapshot", line 2430
ORA-06512: At line 1

SQL> exec dbms_mview.refresh ('mv _ test_nostar', 'complete ');

PL/SQL procedure successfully completed.

This is because the definition of the materialized view is expanded according to the definition of the current table during full refresh, and the new "col4" column will be refreshed, this example is not available in the table "mv_test_star", so the refresh fails. The solution is to manually add a column in this view:

SQL> ALTER TABLE mv_test_star add col4 number;

Table altered.

SQL> exec dbms_mview.refresh ('mv _ test_star ', 'complete ');

PL/SQL procedure successfully completed.

You can solve the problem in this way.