| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Refresh option for Materialized view , want to use it during refresh - for
Thanks, Arup. Your advice is always good.
David
>From: "Arup Nanda" <orarup_at_hotmail.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: Refresh option for Materialized view , want to use it during
>refresh - for
>Date: Tue, 28 Oct 2003 12:09:25 -0800
>
>David,
>
>Glad to be of help.
>
>Looking at your list of steps, I see that your questions is whether
>building
>an MV on the table T is beneficial. Tables and MVs, on prebuilt table or
>not, are stored as segments in the database; so space-sise there is no
>difference, nor there is any change in the way the MV/Table is accessed.
>
>There are a few situations where you may want to convert a table to MV.
>They
>are:
>
>* Building an MV enables Query Rewrite, where Oracle smartly decides to
>rewrite a user query to select from the MV instead of the main tables. This
>is not possible on a regular table. The user must explicitly select from
>it.
>
>* If you want to refresh FAST, then MVs are required. You can do a fast
>refresh on a table, but you have to write your own procedures for that.
>DBMS_MVIEW package does it for you on MVs.
>
>* Your designer software will recognize MV as one and will report it to all
>users, who are aware of the fact that it's an MV, useful for queries. A
>mere
>table will not be clear on that regard.
>
>Converting a table to MV does not cost any resource, as the change is done
>inside the data dictionary only. So, if you are in doubt, you may just
>convert the table to MV anyway.
>
>HTH.
>
>Arup Nanda
>
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>Sent: Tuesday, October 28, 2003 12:04 PM
>refresh - for
>
>
> > Arup,
> >
> > 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" <orarup_at_hotmail.com>
> > >Reply-To: ORACLE-L_at_fatcity.com
> > >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > >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" <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: 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).
> >
> > _________________________________________________________________
> > 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: 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: 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: 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 Wed Oct 29 2003 - 08:09:36 CST
 |
 |