Hello,

All of our MV's are built as completely refresh-able, on-demand, with nologging, as shown below:-

CREATE MATERIALIZED VIEW mv_name
NOLOGGING
TABLESPACE "DATA_SPACE"
USING INDEX TABLESPACE "INDEX_SPACE"
REFRESH ON DEMAND
COMPLETE
AS
--SELECT Text as an example only (this SQL query Text gets populated dynamically from either a CLOB column or from a CLOB procedure's IN parameter with SELECT always having compiler hints as shown:

SELECT /*+ PARALLEL(8) NOLOGGING */ *
FROM tb_name_1
UNION
SELECT /*+ PARALLEL(8) NOLOGGING */ *
FROM tb_name_2
;


Once the MV is created and stats gathered, the Analysts begin using the MV and sometimes, depending on the performance and reporting needs, we may have to add BITMAP, UNIQUE and NON-UNIQUE indexes on multiple columns on the MV. Each MV may have a different refresh schedule depending on the data-source's own refresh schedule.

The main objective is to build a "lean" refresh operation, as much as possible, which will hopefully, result into refreshes that will take the least amount of time (as much as we can even though some refreshes will take longer due to the nature of the SQL query, etc.).


Upon reading the 11.2 documentation for DBMS_MVIEW.REFRESH, it mentions that setting the parameter atomic_refresh => FALSE will take care of (a) disabling, (b) re-building, and (c) computing stats for UNIQUE indexes ONLY.

Does this mean that for all "other" index types present on the MV, like BITMAP and NON-UNIQUE, I have to manually:-

(a) Disable all BITMAP and NON-UNIQUE indexes (ignoring UNIQUE indexes) before running DBMS_MVIEW.REFRESH,

(b) Run the DBMS_MVIEW.REFRESH with parameters method => 'C' and atomic_refresh => FALSE,

(c) Rebuild all BITMAP and NON-UNIQUE indexes (hoping the UNIQUE indexes will be re-built in the step above),

(d) Gather Table Stats that will compute stats for both the Table as well as all the Indexes.


Appreciate any advise :)

create table t2 ( x , y ) as
  select rownum x, mod(rownum, 5) y from dual connect by level <= 1000;
create table t1 ( x , y ) as
  select rownum x, mod(rownum, 3) y from dual connect by level <= 1000;
  
create materialized view mv_name
refresh on demand 
as
  select t1.* from t1
  union 
  select t2.* from t2;
  
create index iy on mv_name(y);

select status, num_rows from user_indexes
where  index_name = 'IY';

STATUS  NUM_ROWS  
VALID   1,800 
  
insert into t1
  select rownum+1000 x, mod(rownum, 3) y from dual connect by level <= 1000;
insert into t2
  select rownum+1000 x, mod(rownum, 5) y from dual connect by level <= 1000;
commit;

exec dbms_mview.refresh('mv_name', atomic_refresh => false);

select status, num_rows from user_indexes
where  index_name = 'IY';

STATUS  NUM_ROWS  
VALID   3,600   

select count(*) from mv_name;

COUNT(*)  
3,600