Re: materialized views

Paula_Stankus_at_doh.state.fl.us 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
> --
> http://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



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