Re: materialized view
- From: "Jaromir D.B. Nemec" <jaromir@xxxxxxxxxxxx>
- To: <kp0773@xxxxxxxxx>, "Thomas Roach" <troach@xxxxxxxxx>
- Date: Fri, 4 Dec 2009 08:54:53 +0100
Hi Kar,
> 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
Other related posts:
