Materialized View Refresh

[ponic]

Hi Tim

I have a question regarding the use materialized view, would like to have your opinion.

I have a transaction tables master and detail where records are written to these tables.
For user to view the records in UI we have created a view based on base tables.

Is it a good idea to have materialized view instead of the view and refresh materialized view when users are recording data to transaction tables?
If multiple users are entering records to base table, refreshing materialized view would be a bad idea?

Regards

[Tim...]

Hi.

The purpose of matierialized views is to take the results of complex queries and "materialize" them into a table segment, making access to the results of the conplext data manipulations quicker.

Materialized views have a sweet spot in warehousing and reporting systems where the same complex queries are required multiple times with relative few updates to the base tables. I have used them in OLTP systems, but only when they are capable of fast refresh (never full) and only when a small lag in the consistency of the data is allowed.

If your data is consistently changing and your query is only accessing a small number of rows in the base tables, it is unlikely you will get much benefit from using a materialized view over queries directly to the base tables (or a regular view over them). If the data is not changing too much and your view definition is sufficient complext, then it may benefit. Of course, the real answer is you must test both mechanisms and see which works best in each case. Remember though, any measure must include the overhead on your system performing the refreshes, because these will impact on your system performance.

Cheers

Tim...