Oracle-L: RE: Refresh option for Materialized view , want to use it during refresh

From: David Boyd <davidb158_at_hotmail.com>
Date: Fri, 24 Oct 2003 08:34:25 -0800
Message-ID: <F001.005D4393.20031024083425@fatcity.com>

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

Received on Fri Oct 24 2003 - 11:34:25 CDT

RE: Refresh option for Materialized view , want to use it during refresh - for