Oracle-L: Re:RE: Suggestions on MV Implementation !!!!!!!

From: <dgoulet_at_vicr.com>
Date: Fri, 21 Jun 2002 12:43:22 -0800
Message-ID: <F001.004848FB.20020621124322@fatcity.com>

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" <Madhusudana.Reddy_at_bestbuy.com>
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" <Madhusudana.Reddy_at_bestbuy.com>
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: Madhusudana.Reddy_at_bestbuy.com

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: ListGuru_at_fatcity.com (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: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------