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

From: Arup Nanda <orarup_at_hotmail.com>
Date: Tue, 21 Oct 2003 08:04:25 -0800
Message-ID: <F001.005D3DF2.20031021080425@fatcity.com>

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

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

Received on Tue Oct 21 2003 - 11:04:25 CDT