Re: Refresh option for Materialized view , want to use it during

Whether or not you can do it across a dblink depends on the following, in no particular order:

• the number of transactions occurring during the refresh period, and the amount of data resulting from them.
• the usable bandwidth of the network connection
• whether or not it is acceptable to have triggers on the production tables of the source system
• the period, or frequency of the refresh, which, along with the network bandwidth and amount of data generated during the refresh period will determine if a fast refresh will work in your environment. That is, apart from the other technical requirements imposed on a fast refresh as outlined in TFM.
• probably some other stuff I'm not thinking of at the moment, but I believe these are the primary technical considerations.

The refresh period can be determined by simply polling your users on what an acceptable lag is.

We have a 'reporting' database here that is based on materialized views from 2 different production systems. There are currently 53 MV's, all of which are fast refresh. 40 or so are refreshed every 2 minutes, and the rest are refreshed every 5 minutes.

It all works very well, and keeps the Crystal Reports users out of the production systems. :)

HTH Jared

On Fri, 2003-10-24 at 20:49, Ryan wrote:
> no we dont need query rewrite. we load data every night across a database
> link. we drop and recreate all the tables from scratch. I thought about
> using materialized views. i thought they might load faster because of fast
> refresh. not sure if we can do that across a db link.
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Friday, October 24, 2003 10:14 PM
> refresh - for
>
>
> > No, you didn't read it completely. Create table and create MV do the same
> > thing - produce a copy of the data on a different location (or a different
> > segment) that can be queried independently. However, I proposed a
> different
> > way of doing the MV creating and refreshing, not using the
> > dbms_mview.refresh procedure as documented, but by using prebuilt table
> and
> > using other faster methods such as CTAS and Direct Path load to do a
> > complete refresh. It offers severa advantages such as faster execution,
> much
> > less outage window and low resource utilization. As an added bonus, you
> > don't have to drop and recreate the read only MV when you add/alter a
> column
> > to the master table.
> >
> > In your case, you might want to consider converting the tables to MV if
> MVs
> > are used in such a way. One example is if you see some benefit from Query
> > Rewrite, you may want to create the MVs on the tables using the ON
> PREBUILT
> > TABLE clause for Oracle to use QR.
> >
> > HTH.
> >
> > Arup Nanda
> > www.proligence.com
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Friday, October 24, 2003 9:24 PM
> > refresh - for
> >
> >
> > > did i read that correctly that create table as is superior to a
> > materialized
> > > view for nightly loads? We drop all the tables in some of our schemas
> and
> > > rebuild them with create table as statements. I was going to try out
> > > materialized views to see if they were faster.
> > >
> > > guess they are not?
> > > ----- Original Message -----
> > > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > > Sent: Friday, October 24, 2003 12:34 PM
> > > refresh - for
> > >
> > >
> > > > Hi Arup,
> > > >
> > > > This is a very good method. I would like to use it to modify some of
> my
> > > > data loading procedures. Here are my questions:
> > > > 1. Do I need to create the table on the step 1 every time when I
> refresh
> > > the
> > > > data If I refresh data once per day?
> > > > 2. Is "ON PREBUILT TABLE" available on Oracle 8i? When I was trying
> the
> > > > method on Oracle 8i, I got missing keyword error on "PREBUILT".
> > > >
> > > > Dave
> > > >
> > > > >
> > > > >Siddharth,
> > > > >
> > > > >I will offer a slightly out-of-the-box solution. Please read it
> through
> > > > >till the end to determine its applicability in your case.
> > > > >
> > > > >It seems yours refresh interval is once a day and you don't mind
> stale
> > > > >data for a max of 24 hours. You also refresh is complete, not
> > > > >incremental. So, I would suggest the follwoing approach.
> > > > >
> > > > >(1) Create a table first
> > > > >CREATE TABLE CT_PRODUCTID_VW
> > > > >TABLESPACE ....
> > > > >NOLOGGING
> > > > >AS
> > > > >SELECT .....
> > > > >
> > > > >(2) When you are ready to "refresh", drop the MV
> > > > >DROP MATERIALIZED VIEW CT_PRODUCTID_VW;
> > > > >
> > > > >(3) Create the MV with the PREBUILT TABLE option.
> > > > >CREATE
> > > > >MATERIALIZED VIEW CT_PRODUCTID_VW
> > > > >BUILD IMMEDIATE
> > > > >REFRESH START WITH SYSDATE
> > > > >NEXT (SYSDATE + 1)
> > > > >ON PREBUILT TABLE
> > > > >AS
> > > > >SELECT
> > > > > msi.segment1 productid,
> > > > >...
> > > > >
> > > > >Your MV is not accessible between STEP 2 and STEP3, which is really a
> > > > >dictionary update and takes about a second or so. So the "outage" is
> > > > >really 1 second, not 1/2 hr.
> > > > >
> > > > >A few explanations are in order here.
> > > > >
> > > > >(1) Creating an MV on a Prebuilt Table does not consume more space.
> The
> > > > >segment that used to be a table simply becomes an MV.
> > > > >(2) When you drop the MV, the MV is gone, but the table remains
> > instact.
> > > > >(3) The table can be create by any means - export/import, SQL*Loader,
> > > > >INSERT APPEND, etc.
> > > > >(4) IT places less strain on the system comapred to the MV refresh
> > > > >option, simply because the MV refresh truncates the segment and then
> > > > >builds it.
> > > > >
> > > > >I presented a paper to the same effect at IOUG Live 2003. You can
> > > > >download a modified version of the same from my website
> > > > >www.proligence.com/downlaods.html, titled "Painless Master Table
> Alter"
> > > > >from the Presentations Section.
> > > > >
> > > > >HTH.
> > > > >
> > > > >Arup Nanda
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >----- Original Message -----
> > > > >To: Multiple recipients of list ORACLE-L
> > > > >Sent: Tuesday, October 21, 2003 3:59 AM
> > > > >refresh
> > > > >
> > > > >
> > > > >Hi Gurus,
> > > > >
> > > > >I have a materialized view, which is based on Oracle Apps tables and
> on
> > > > >remote database. The view refresh takes around ½ hour, during this
> time
> > > > >period I cannot see any records in the materialized view and
> therefore
> > > > >my application faces errors.
> > > > >The following is the view definition
> > > > >
> > > > >CREATE
> > > > >MATERIALIZED VIEW CT_PRODUCTID_VW
> > > > >BUILD IMMEDIATE
> > > > >REFRESH START WITH SYSDATE
> > > > >NEXT (SYSDATE + 1)
> > > > >AS
> > > > >SELECT
> > > > > msi.segment1 productid,
> > > > > msi.description description,
> > > > > msi.inventory_item_id inventory_item_id,
> > > > > mc.segment1 product_family,
> > > > > mc.segment2 product_type
> > > > >FROM MTL_CATEGORY_SETS_at_CME_ODSPROD mcs,
> > > > > MTL_CATEGORIES_at_CME_ODSPROD mc,
> > > > > MTL_ITEM_CATEGORIES_at_CME_ODSPROD mic,
> > > > > MTL_SYSTEM_ITEMS_at_CME_ODSPROD msi
> > > > >where 1=1
> > > > >and mc.structure_id = 50112
> > > > >and mc.segment3 != 'SPARE'
> > > > >and mc.global_name = 'US'
> > > > >and mc.enabled_flag = 'Y'
> > > > >and mcs.global_name = mc.global_name
> > > > >and mcs.category_set_name = 'PROD GROUP'
> > > > >and mic.category_set_id = mcs.category_set_id
> > > > >and mic.category_id = mc.category_id
> > > > >and mic.global_name = mc.global_name
> > > > >and mic.organization_id = 1
> > > > >and mic.inventory_item_id = msi.inventory_item_id
> > > > >and msi.organization_id = mic.organization_id
> > > > >and msi.global_name = mc.global_name
> > > > >AND msi.auto_created_config_flag = 'N'
> > > > >AND msi.item_type IN ('ATO MODEL','CONFIG SPARE','CONFIG
> > > > >SUB','FEATURE PACK','PRODUCT LIST>$0','PTO MODEL','SPARE')
> > > > >and msi.inventory_item_status_code IN
> > > > >('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
> > > > >
> > > > >Please note that the tables referenced are remote tables and Oracle
> > Apps
> > > > >tables and not logging on it is possible.
> > > > >Please suggest an appropriate refresh mechanism to see the records
> even
> > > > >during refresh period.
> > > > >
> > > > >Thanks in advance.
> > > > >
> > > > >With Warm Regards
> > > > >
> > > > >
> > > > >
> > > > >Siddharth Haldankar
> > > > >Zensar Technologies Ltd.
> > > > >Cisco Systems Inc.
> > > > >(Offshore Development Center)
> > > > ># : 091 020 4128394
> > > > >shaldank_at_cisco.com
> > > > >s.haldankar_at_zensar.com
> > > > >
> > > >
> > > > _________________________________________________________________
> > > > Cheer a special someone with a fun Halloween eCard from American
> > > Greetings!
> > > > Go to
> http://www.msn.americangreetings.com/index_msn.pd?source=msne134
> > > >
> > > > --
> > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > > --
> > > > Author: David Boyd
> > > > INET: davidb158_at_hotmail.com
> > > >
> > > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > > San Diego, California -- Mailing list and web hosting services
> > > > ---------------------------------------------------------------------
> > > > 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.net
> > > --
> > > Author: Ryan
> > > INET: rgaffuri_at_cox.net
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting services
> > > ---------------------------------------------------------------------
> > > 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.net
> > --
> > Author: Arup Nanda
> > INET: orarup_at_hotmail.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > 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.net
> --
> Author: Ryan
> INET: rgaffuri_at_cox.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).