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.
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.
- Fast refreshes shouldn't have any effect (at least none that anyone would notice), but test to make sure as there is a tiny bit of overhead associated with updating the materialized view logs as DML is run. Use these if possible, especially if the parent table has a lot of data or processes a lot of DML transactions. Fast refreshes generally have the lowest and most predictable overall resource impact on your whole system.
- Complete refreshes shouldn't have any effect either, unless (like any big query) they consume too many compute resources and affect the whole system. They won't add any overhead to the actual DML. Use these if you can't use fast refreshes for some reason, but try to limit how often you refresh if there's a lot of data or high compute requirements for the refresh itself. Too many complete refreshes can drive up your overall transaction rate and redo log rollover rate more than fast refreshes will.
- On commit refreshes will absolutely have an effect on DML, as the compute and I/O required to update the MV is added directly to the DML transaction on the parent table. Don't do this unless you have very strict real-time update requirements on the MV, and your source data doesn't change much.
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.