Recreate the snapshot to allow fast refresh, (you will
have to create a snapshot log on your source table)
and refresh once every 5/10/20/30 minutes.
Fast refreshes are just one commit that is either
committed or rolled back at the end. Viola, fresh data
instantaneously.
You can do a refresh immediate when you recreate the
snapshots so it will build the data right away.
To do it really fast, create a new snapshot with the
correct definition, rename the old snapshot, rename
the new snapshot to the old name, recompile your
packages and procedures, drop the old snapshot, and
viola, new snapshot.
Snapshots refresh via a job in the Oracle job queue.
You can adjust timing on this job to adjust your
refresh frequency.
Make sure you get your indexes, stats, and grants in
place on the new snap too. Check your synonyms as
well.
hth,
jack
- "Reddy, Madhusudana"
<Madhusudana.Reddy_at_bestbuy.com> wrote:
> 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).
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: jack_silvey_at_yahoo.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).
Received on Fri Jun 21 2002 - 14:33:23 CDT
