I really appreciate your answer in great details. I got "on prebuilt table" work. Thanks a lot for your help. Here is another question:
Do you see any advantage to use materialized view on prebuilt table for my data loading over just simple renaming tables as steps below:
1. create table t that is always accessed by applications 2. create table t1 that is a temp table for loading 3. load data into table t1 4. rename table t to table t2 5. rename table t1 to t 6. rename table t2 to t1 7. truncate table t1 for next day loading
David
From: "Arup Nanda" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: Re: Refresh option for Materialized view , want to use it during refresh - for
Date: Fri, 24 Oct 2003 18:04:33 -0800
David,
Answers to your questions:
(1) Without knowing your exact needs, I wil offer a few different scenarios.
I am assuming that you are doing a complete refresh every time. The
following pertain to that.
Say, your name of the MV is MV1. Here are the steps the first time.
1. Create table MV1 2. Create MV MV1 on that table.
When you want to refresh complete:
1. Create table MV1_TEMP. Choose a suitable method: CTAS across dblink (with
NOLOGGING), SQL*Loader, Direct Load Insert, or simpley exp/imp.
2. Drop MV MV1. This drops the MV but doesn't drop the table.
3. Drop table MV1.
4. Rename table MV1_TEMP to MV1.
5. Recreate MV MV1.
6. Allow users to proceed as usual.
Note the time consumed between Steps 2 and 6 are in the order of a few
seconds. And it's the only time the users will not have access to the MV, as
opposed to a full refresh using dbms_mview.refresh approach., which will
lock the MV for the entire duration and generate tons of redo and rollback.
Even if you do a incremental refresh, this is still a better approach. In that case, you don't drop the table during the refresh.
(2) Yes, the option is available in 8i, at least in 8.1.7. Make sure the syntax is correct.
create materialized view MV1 on prebuilt table refresh fast as select ... from ....
In the article I mentioned, you can find the complete syntax. www.proligence.com/downloads.html is the site. It also dscribes a step by step solution to the issue and compares the common solution with this new one.
Hope this helps.
Arup Nanda
----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] mcs,
> > [EMAIL PROTECTED] mc,
> > [EMAIL PROTECTED] mic,
> > [EMAIL PROTECTED] 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
> >[EMAIL PROTECTED]
> >[EMAIL PROTECTED]
> >
>
> _________________________________________________________________
> 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
_________________________________________________________________
Add MSN 8 Internet Software to your current Internet access and enjoy patented spam control and more. Get two months FREE! http://join.msn.com/?page=dept/byoa
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
