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