Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Creating an mview makes other mview not refresh any more

David BalažicMay 10 2024 — edited May 10 2024

Using the old Oracle Database 11.2.0.4, I encountered this situation:

Creating a new materialized view makes some existing materialized views not update any more.

Details:

create materialized view FIRST_VIEW
refresh fast
on commit
as
select A.col1,B.col2,... 
from TABLE_1 A, TABLE_2 B
where A.col1 != B.col2
and ... someother conditions, basically: colX is null and the join condition

alter table FIRST_VIEW add constraint FOO_NOT_PERMITTED check (
col1 = col2 -- "no rows allowed at all in this table" as this condition is never met in the SELECT
);

This works. When new rows are INSERT-ed, the view is updated after commit. And the commit fails if the constraint is violated. (this is a multi table constraint implemented with mviews, as per Toms example with currencies )

But then I do:

create materialized view SECOND_VIEW
refresh fast
on commit
as
select ...
UNION ALL
select ...

After this command, the first view is not updated any more after commits.

No error is reported (for executed SQL, did not check logs yet).

If I INSERT rows into source tables that violate the above constraint, no error is reported and the data is commited,

If I run the SELECT statement from the FIRST_VIEW definition, I get correct results (one row).

If I select from FIRST_VIEW directly (that is: SELECT * from FIRST_VIEW;), I get zero rows.

If I create a another mview with the same SQL as FIRST_VIEW (but with a different name, let's say FIRST_VIEW_2) then that view is also not updated on commits. It only shows the original one row. If after creating FIRST_VIEW_2 I insert another row into source tables, then both FIRST_VIEW and FIRST_VIEW_2 are not updated. SELECT-ing from FIRST_VIEW shows 0 rows, while SELECT from FIRST_VIEW_2 show one row. I can update FIRST_VIEW_2 manually with:

exec dbms_mview.refresh('FIRST_VIEW_2');

and then SELECT * from FIRST_VIEW_2;
I get the correct two rows.

Manual update of FIRST_VIEW gives a constraint violation error, which is also expected.

This looks like a bug. Creating a new mviews should not make existing one to suddenly stop updating.

Or am I overlooking something?

Additional details:

I created the needed materialized logs (otherwise the create materialized view commands would fail).

The DBMS_MVIEW.EXPLAIN_MVIEW for SECOND_VIEW, shows REFRESH_FAST = N (actually every row except REFRESH_COMPLETE is N), but create materialized view command succeeds.
The SECOND_VIEW mview is also refreshed after commits, despite of above. EDIT: This is probably a mistake of me observing the situation. Rechecking after writing this post, it seems SECOND_VIEW is also not updated automatically.

As a workaround, I plan to simply not use/create the SECOND_VIEW, but do it maybe with two separate mviews (actually I already did that first, what I am doing is trying is to use just one mview instead of two and using UNION ALL was the first simple try)

Comments
Post Details
Added on May 10 2024
6 comments
155 views