Re: materialized view

> 10.2.0.4
> dropping and recreating a MV takes 10 minutes. but while doing a complete
> refresh it takes 8 hour.

The probably cause of the discrepancy is that the recreation of the MV uses a CTAS, but the COMPLETE refresh (starting with 10g) uses delete / insert. You may very this with 10046 trace.

> would appreciate any tips before I think of converting it to a fast refresh ..

Simple test and see if this is better option (10046 can help again to explain the bahaviour). The particular problem of FAST refresh is that it is misnamed. A better name would be DELTA refresh. In case that there are lot of changes or the cost of update is high the "FAST refresh is slower that the COMPLETE refresh" (check Metalink with this search phrase).

If the FAST refresh is not an option, you can define two MVs that are alternately dropped and recreated. You define a view on a top of them that is recreated to point to the current MV providing an uninterrupted data access for the applications. This will revert the functionality of the COMPLETE refresh to the 9i behaviour (similar) to truncate / insert append. Using the alternating rebuild of A and B MV will bridge the 9i gap between the truncate and insert where the MV was empty. You will find details for this proposal in archive of this list.

HTH Jaromir D.B. Nemec

--
http://www.freelists.org/webpage/oracle-l