Re: materialized views
- From: Mark Bole <makbo@xxxxxxxxxxx>
- Date: Mon, 11 Apr 2005 08:01:21 -0700
Paula_Stankus@xxxxxxxxxxxxxxx wrote:
> Guys,
>
> We are concerned that if we use materialized views on our OLTP system to
> just capture changed or new rows that it will lead to performance
> degradation. Any suggestions on setting up materialized views to
> prevent this? =20
> --
> //www.freelists.org/webpage/oracle-l
>
Why do you want to use MV's? If the performance benefit of using the
MV's is greater than the cost of maintaining them, then what's the
problem? Obviously you need to do some testing to find out if this is
true in your case. Also, if you literally mean "just capture changed or
new rows", then consider Change Data Capture, since an MV will include
all the rows, not just the changed ones.
Set up your MV's to use fast refresh, and refresh them only during a
period of light OLTP usage. Also consider using Resource Manager to
prevent the refresh process from consuming too much CPU.
Another approach I have used successfully is to set up a Data Guard
logical standby on a different server, create the MV logs in the
standby, and then under a different schema, create the MVs. This
completely removes any extra load from your primary, except for the
supplemental logging required for the logical standby, which is minimal.
--
Mark Bole
http://www.bincomputing.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
