Questions on Materialized View refresh

PeaceMonger

DB versions : 19c, 12.1 

I am new to materialized views.

Question 1.

The table like structure (with rows) within the below mentioned MVs (any MVs) are created/populated only when the complete refresh is done by DBMS_MVIEW.REFRESH as shown below. I mean, I was always under the impression that the rows are populated when CREATE MATERIALIZED VIEW command is executed. Its not. Its only done when the MV is refreshed.

Last month, in production, after a DROP of MV + complete refresh, I found 2 indexes of the MV in UNUSABLE state. Still don't know the root cause of it.

So, when dropping and recreating MVs along with its indexes, is it safe to create the Indexes for a materialized view after the full refresh ?

Question 2.

When materialized views with REFRESH FAST clause are dropped and recreated, their materialized view logs need NOT have to be dropped and recreated as well. Right ? Anyone remember ?

I mean the MV Log of DEPT and EMP may be used by other Materialized views as well. Right ?

grant create materialized view to scott;

create materialized view log on dept
NOLOGGING
ENABLE ROW MOVEMENT
WITH ROWID
EXCLUDING NEW VALUES;
 
create materialized view log on emp
NOLOGGING
ENABLE ROW MOVEMENT
WITH ROWID
EXCLUDING NEW VALUES;
 

 
create materialized view mv3
BUILD DEFERRED
USING INDEX 
REFRESH FAST ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS 
ENABLE QUERY REWRITE
as
select a.rowid erowid, b.rowid drowid, b.dname, a.*
from emp a, dept b
where a.deptno=b.deptno;

--- In real life, I have around 25 indexes like below for just one MV

CREATE INDEX mv3_idx1 ON mv3 (empno) ;

begin
DBMS_MVIEW.REFRESH 
(
LIST => 'MV3',  
METHOD => 'C',   
ATOMIC_REFRESH => FALSE
);
end;
/

EdStevens

Well, my first question is why are you dropping and recreating MVs in the first place?

PeaceMonger

Hi Ed

Because of changing application requirements, new columns may be needed or changes in the filter or changes in aggregation will necessitate materialized views to be modified. Hence the MVs need to dropped and re-created with new requirements.

JohnWatson2

It all looks a bit of a mess.

First, the reason why your MV is not populated until you refresh is because you created it with the BUILD DEFERRED clause.

Second, the clause USING DEFAULT LOCAL ROLLBACK SEGMENT is ancient history. Why do you have it?

Third, 25 indexes on one MV??? One does wonder if your indexing strategy could be flawed.

PeaceMonger

Thank You very much for the info, John.

I will look up USING DEFAULT LOCAL ROLLBACK SEGMENT clause and will recommend to remove it or replace it with a better alternative.

It is a Siebel application. Siebel and its support guys just love indexes. 😑

The MV with 25 indexes which I was referring to is 21 GB in size.

Another MV which is only 900 MB in size has 32 indexes ! 

Minor correction: 

These MVs don't have aggregate (like a result of SUM, GROUP BY, MAX,...). They look like any other Siebel table.

I never actually worked in MV before. When I was preparing for 12c OCP certification, I played around with MVs and it was storing aggregation results. 

Hence I assumed all MVs store results of aggregations (SUM, GROUP BY, MIN, MAX,...) 😊

BlueSkies

Regarding your question 2 on whether materialized view logs need to be dropped and recreated when MVs are dropped and recreated

In my shop, when I drop and recreate MVs, I don't drop and recreate MV Logs. Instead, our SOP instructs to do a Shrink of the underlying MView logs. Don't ask me why 😀

alter table WMRS.MLOG$_HRT_DTL enable row movement;
alter table WMRS.MLOG$_HRT_DTL shrink space;