Hello,

I'm investigating long time of fast refresh of materialized view. I created live sql test case which simulates my situation but on lot smaller set of data.
From my observation longest running query during refresh is:

select count (*) from data1j2_tab_mv

and

select count (*) from data1_tab

Command used in refresh:

BEGIN dbms_mview.refresh('data1j2_tab_mv', method => 'F',atomic_refresh=>FALSE); END;/

Command used to manipulate data:

update data1_tab set col1='Y' where fk_key=1 and col1='B';

So all that RDBMS does is counting all rows of MVIEW and source table at end of refresh which seems unnecessary IMO..


Is this expected ? Can I speed this up somehow ?

P.S. Relation between data1_tab and data2_tab is many to many. It looks ugly but this is requirement to compare values in such fashion..


Best Regards,
Grigory





<UPDATE>

Thanks to rubber duck effect I was able to come up with solution.

It looks like RDBMS is counting rows due to new table DBA_MVREF_STATS. It populates number of rows before and after refresh.

To skip this step collection_level in DBMS_MVIEW_STATS.SET_MVREF_STATS_PARAMS must be set to null value. After that MV refresh in miliseconds insted of minutes.

Thanks for forcing me to gather all info together which led me to this conclusion :)

</UPDATE>

exec dbms_mview_stats.set_mvref_stats_params ( 'data1j2_tab_mv', 'NONE' );