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;
/