Home » RDBMS Server » Performance Tuning » Can some review my materialized view? (Oracle 9i)
Can some review my materialized view? [message #443810] Wed, 17 February 2010 06:58 Go to next message
pdaalder
Messages: 1
Registered: February 2010
Location: n/a
Junior Member
Hi

I'm suggesting at my work to use a materialized view, instead of a process that have running daily. This process runs about 20min, yes some tuning needs to happen there. This process selects data, and inserts that into a table.

My idea is to create a materialized view that will be updated each day (or half day).

The way I've created the mview is:
create materialized view log on SOME_VIEW WITH ROWID;

create materialized view MV_TABLE
TABLESPACE <tblspace>
as SELECT a.col1,a.col2,b.col1,b.col2,a.col3
FROM SOME_TABLE A, SOME_VIEW B
WHERE A.COL1 = B.COL1
AND A.COL2 = (SELECT MAX(A1.COL2) FROM SOME_TABLE A1 WHERE A1.COl1 = A.COL1);

What i'm not sure about is, is this the way to setup this mview? Also I would like to use the refresh 'fast' mechanism, as I don't want loose the data in this view, but rather updated it with that latest changes. The goal of this to reduce processing time of refreshing the mview/table.

Any tips are much appreciated.

Peter Daalder
Re: Can some review my materialized view? [message #443815 is a reply to message #443810] Wed, 17 February 2010 07:19 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Hi,

Welcome to the forum.

Check this linnk.

http://rwijk.blogspot.com/2009/05/fast-refreshable-materialized-view.html

Rob has written nearly 7 different articles on the materialized view. Have a read and if you still have any more doubts come back to us and I am sure somebody will point you in the right directions.

cheers

Raj
Previous Topic: Replace Not Exists
Next Topic: Pls tell me, what I need to check before tuning the SQL code?
Goto Forum:
  


Current Time: Fri Sep 30 17:27:45 CDT 2016

Total time taken to generate the page: 0.29944 seconds