You Asked

Hi,

When I have 2 materialized views (MV_1A and MV_1B) that are based on the same materialized view (MV_1) then using dbms_mview.refresh with the nested=>true option on 1 of those materialized views invalidates the other materialized view. As I understand it, the nested=>true option always refreshes the dependent materialized views even if they are already fresh. This in turn causes the other materialized view to change its staleness state from FRESH to NEEDS_COMPILE.

Example code:

SQL> create table TABLE_A (
    common_id   NUMBER(6),
    value_a     NUMBER(12)
);

Table TABLE_A created.

SQL> create table TABLE_B (
    common_id   NUMBER(6),
    value_b     NUMBER(12)
);

Table TABLE_B created.

SQL> create materialized view MV_1 as
(
    select common_id, value_a, value_b
    from table_a
    join table_b using (common_id)
);

Materialized view MV_1 created.

SQL> create materialized view MV_1A as
(
    select common_id, value_a + 1, value_b - 1
    from mv_1
);

Materialized view MV_1A created.

SQL> create materialized view MV_1B as
(
    select common_id, value_a + 2, value_b - 2
    from mv_1
);

Materialized view MV_1B created.

SQL> select mview_name, staleness from user_mviews where mview_name in ('MV_1', 'MV_1A', 'MV_1B') order by mview_name;

MVIEW_NAME
--------------------------------------------------------------------------------
STALENESS
-------------------
MV_1                                                                            
FRESH              

MV_1A                                                                           
FRESH              

MV_1B                                                                           
FRESH              


SQL> insert into table_a values (1, 100);

1 row inserted.

SQL> commit;

Commit complete.

SQL> select mview_name, staleness from user_mviews where mview_name in ('MV_1', 'MV_1A', 'MV_1B') order by mview_name;

MVIEW_NAME
--------------------------------------------------------------------------------
STALENESS
-------------------
MV_1                                                                            
NEEDS_COMPILE      

MV_1A                                                                           
FRESH              

MV_1B                                                                           
FRESH              


SQL> execute DBMS_MVIEW.REFRESH( 'MV_1A', nested => TRUE );

PL/SQL procedure successfully completed.

SQL> select mview_name, staleness from user_mviews where mview_name in ('MV_1', 'MV_1A', 'MV_1B') order by mview_name;

MVIEW_NAME
--------------------------------------------------------------------------------
STALENESS
-------------------
MV_1                                                                            
FRESH              

MV_1A                                                                           
FRESH              

MV_1B                                                                           
NEEDS_COMPILE      


SQL> execute DBMS_MVIEW.REFRESH( 'MV_1B', nested => TRUE );

PL/SQL procedure successfully completed.

SQL> select mview_name, staleness from user_mviews where mview_name in ('MV_1', 'MV_1A', 'MV_1B') order by mview_name;

MVIEW_NAME
--------------------------------------------------------------------------------
STALENESS
-------------------
MV_1                                                                            
FRESH              

MV_1A                                                                           
NEEDS_COMPILE      

MV_1B                                                                           
FRESH              


SQL> execute DBMS_MVIEW.REFRESH( 'MV_1A', nested => TRUE );

PL/SQL procedure successfully completed.

SQL> select mview_name, staleness from user_mviews where mview_name in ('MV_1', 'MV_1A', 'MV_1B') order by mview_name;

MVIEW_NAME
--------------------------------------------------------------------------------
STALENESS
-------------------
MV_1                                                                            
FRESH              

MV_1A                                                                           
FRESH              

MV_1B                                                                           
NEEDS_COMPILE

Is there any way we can hint to Oracle that he should not refresh dependent materialized views if they are already fresh?

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/file/content_EF3QP4DEWH8MV4XJDJGWY7TJY.html

and Chris said...

Thanks for providing a working LiveSQL test case! I feel like you're approaching this in the wrong way though...

MV_1A & B are dependent on just MV_1. Thus you only need to refresh these after refreshing MV_1. So you should refresh MV_1 using DBMS_MVIEW.REFRESH_DEPENDENT and nested => true.

Do this and all the MVs will stay fresh:

create table TABLE_A ( 
    common_id   NUMBER(6), 
    value_a     NUMBER(12) 
);

create table TABLE_B ( 
    common_id   NUMBER(6), 
    value_b     NUMBER(12) 
);

create materialized view MV_1 as 
( 
    select common_id, value_a, value_b 
    from table_a 
    join table_b using (common_id) 
);

create materialized view MV_1A as 
( 
    select common_id, value_a + 1, value_b - 1 
    from mv_1 
);

create materialized view MV_1B as 
( 
    select common_id, value_a + 2, value_b - 2 
    from mv_1 
);

insert into table_a values (1, 100);
insert into table_b values (1, 100);

commit;

select count(*) from mv_1;

COUNT(*)  
0  

select count(*) from mv_1a;

COUNT(*)  
0 

select count(*) from mv_1b;

COUNT(*)  
0  

declare
 errs integer;
begin
 DBMS_MVIEW.REFRESH_DEPENDENT ( errs, 'MV_1', nested => TRUE );
end;
/

select mview_name, staleness
from   user_mviews 
where  mview_name in ('MV_1', 'MV_1A', 'MV_1B') 
order by mview_name;

MVIEW_NAME  STALENESS  
MV_1        FRESH      
MV_1A       FRESH      
MV_1B       FRESH 

select count(*) from mv_1;

COUNT(*)  
1 

select count(*) from mv_1a;

COUNT(*)  
1  

select count(*) from mv_1b;

COUNT(*)  
1

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

More than 2 levels of nested mviews

Peter Marcoen, January 17, 2017 - 4:40 pm UTC

Hi Chris,

This is indeed a solution to the simplified example.
In reality though I might have more than 1 level of nesting (e.g. MV_0 between the tables and MV_1). When I want the data of a specific MV (e.g. MV_1B) I don't want to manually look up the chain of MV's to find which nested MV is not FRESH and then perform a refresh_dependent on that one.

Additionally, there might be a lot of MV's dependent on this same high-level MV and to refresh all of them would be resource and time consuming while all I need is to refresh one particular low-level MV.

January 18, 2017 - 12:25 pm UTC

I still think you're approaching this backwards.

If you have a chain of MVs A -> B -> C, refreshing A will also refresh B and C. And if A is the only one that refers to base tables, to refresh C you also need to refresh B and A anyway. So you've got the effort of refreshing them all whichever way you look at it.

So save yourself hassle and just do a refresh_dependent on A.

I don't know how to avoid the "NEEDS_COMPILE" issue. So if you insist that you must do this, contact support.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More than 2 levels of nested mviews

More to Explore

PL/SQL demos

PL/SQL docs

Refreshing nested materialized views

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More than 2 levels of nested mviews

More to Explore

PL/SQL demos

PL/SQL docs