Madhu, From the Oracle 8i Concepts manual:
"Refreshing Materialized Views Oracle maintains the data in materialized views by refreshing them after changes are made to their master tables. The refresh method can be incremental (fast refresh) or complete. For materialized views that use the fast refresh method, a materialized view log or direct loader log keeps a record of changes to the master tables. Materialized views can be refreshed either on demand or at regular time intervals. Alternatively, materialized views in the same database as their master tables can be refreshed whenever a transaction commits its changes to the master tables. " The syntax you need in the definition is "refresh on commit". Dick Goulet ____________________Reply Separator____________________ Author: "Reddy; Madhusudana" <[EMAIL PROTECTED]> Date: 6/21/2002 2:13 PM Thanks DG, To my understanding from your reply, Can I have a fast refresh on a MV, which is based on multiple tables , just by having log tables on base tables of the MV ?? And u also said " You can also set it up so that when anyone makes a change to the base tables the MV gets updated as part of their transaction" ... could you please shed some more light on it ??? Thanks again Madhu NB: version of DB is 8.1.7.2 -----Original Message----- Sent: Friday, June 21, 2002 1:24 PM To: Reddy; Madhusudana; Multiple recipients of list ORACLE-L Madhu, Go tell that SrDBA to go read up on MV's. They do support a fast refresh, but you have to have a log table associated with the base tables in the view. You can also set it up so that when anyone makes a change to the base tables the MV gets updated as part of their transaction. Dick Goulet Senior Oracle DBA OCP 8i ____________________Reply Separator____________________ Author: "Reddy; Madhusudana" <[EMAIL PROTECTED]> Date: 6/21/2002 10:58 AM Hello All, I have a set of Materialized views in my DB . we refresh ( COMPLETE) these MVs, couple of times a day. Web server ( application ) will hit these MVs to show the data on web pages. But the complete Refresh of MVs are consuming much time and , at this point of time , Application is not able to show right data on web pages. This is like a down time. I need some suggestions from you all, in order to minimize or zeroing this down time. The first thing I can think of is , FAST refresh , but one of my Sr.DBA told me that the MV definition will not allow us for a FAST refresh( Are there any limitations for FAST refresh ???? ). Here is a sample MV Definition : CREATE MATERIALIZED VIEW GENRELOB NOLOGGING BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND DISABLE QUERY REWRITE AS SELECT DISTINCT '1' AS CLIP, LOB.LOB_ID, LOB.LOB_CD, GENRE.GENRE_ID, GENRE.GENRE_DESC, GENRE.GENRE_DESC AS INSTANCENAME FROM GENRE, GENRE_LOB_XREF, LOB, GENRE_PRODUCT_XREF WHERE GENRE.GENRE_ID = GENRE_LOB_XREF.GENRE_ID AND GENRE_LOB_XREF.LOB_ID = LOB.LOB_ID AND GENRE_PRODUCT_XREF.genre_id = GENRE.genre_id AND GENRE.DSPLY_IND = 'Y' ORDER BY LOB_CD, GENRE_DESC ; My Goal is to view the FRESH data on web pages all the time , irrespective of MV Refresh. Would anybody suggest me some bright ideas , to have no or less down time ??? Thanks in advance Madhu V Reddy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).