Re: Refresh option for Materialized view , want to use it during 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).