Re: Fast refresh takes eternity

> Steve, has a good point. This may just be a query tuning issue. Have
> you used the dbms_mview procedures explain_mview and tune_mview to see
> if the view is fast refreshable and if Oracle has any suggestions on
> how to tune the mview?

The query is of course fast refreshable because it's already created as REFRESH FAST ON DEMAND. Tuning may concerns other MVs whose plans are very bad. Dropping indexes improves plan as it forces full scan without nested loops. But this query is simple and shouldn't take eternity despite the non optimal plan. I dont understand how tuning may explain the fact that complete refresh takes 20 mn and fast refresh is not completed after 8 hours (I always aborted it cause it never ends)?? I expect fast refresh in worst cases taking as much as complete refresh..

SQL> select CAPABILITY_NAME,possible,MSGTXT   2 from mv_capabilities_table
  3 where mvname = 'MV_LGN_DECL_1_8'
  4 and CAPABILITY_NAME like '%FAST%';

CAPABILITY_NAME                P MSGTXT

------------------------------ -
---------------------------------------------
REFRESH_FAST                   Y

REFRESH_FAST_AFTER_INSERT      Y

REFRESH_FAST_AFTER_ONETAB_DML Y

REFRESH_FAST_AFTER_ANY_DML     Y
        
REFRESH_FAST_PCT               Y