oracle11gdml

Oracle materialized view will table DMLs performance be affected?


May be a dump question. I just need a quick estimation.

If I create materialized view on TableX. Will the performance of insert into TableX , update TableX, delete from TableX etc. degrade or it won't have any impact on the performance of these DMLs?

I would like to improve a performance of some selects but it would be a deal breaker if DMLs suffer even a little as a result.


Solution

  • It will depend on how your materialized view is configured to refresh, and how often. Any of the refresh options will have some effect on the system, as you're introducing at least some new load, so its a question of which form of load affects your DML the least.

    It's a bit counter-intuitive, but if you have fast refreshes it actually makes sense to run them as often as possible. How often depends on how fast your source data changes, how close to real-time you need your MV data to be, and how resource-intensive your MV query is. A higher refresh frequency keeps the number of DML updates as small as possible with any one, which helps to keep them from spiking compute resource usage.