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).
