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

From: Reddy, Madhusudana <Madhusudana.Reddy_at_bestbuy.com>
Date: Fri, 21 Jun 2002 13:58:25 -0800
Message-ID: <F001.0048499F.20020621135825@fatcity.com>

Jack,
Nice picture of the whole thing .

Through out your solution , mentioned SNAPSHOT , you mean Materialized view ???

Thanks,
Madhu

-----Original Message-----
Sent: Friday, June 21, 2002 2:33 PM
To: Multiple recipients of list ORACLE-L

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

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

Received on Fri Jun 21 2002 - 16:58:25 CDT