Materialized View Refresh

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Materialized View Refresh

Postby ponic » Thu Apr 05, 2012 7:33 am

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
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Materialized View Refresh

Postby Tim... » Thu Apr 05, 2012 9:14 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17962
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 3 guests