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

> i thought they might load faster because of fast refresh.

If you can do fast refresh, then MVs may be better. However, here are a few considerations before you do so:

1. Fast refresh requires creation of mv logs on the master tables, which are populated by triggers. This will affect performance on your source db, which might not be acceptable.
2. Fast refresh is transactional, and it may be slower. If the number of changes are large between two refreshes, it might be actually faster to do a full refresh.

> not sure if we can do that across a db link.

Of course you can across db links. Fast refreshable MVs are also created in replication environments; how do you suppose they work between two different databases?

HTH. Arup

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, October 24, 2003 11:49 PM refresh - for

> no we dont need query rewrite. we load data every night across a database
> link. we drop and recreate all the tables from scratch. I thought about
> using materialized views. i thought they might load faster because of fast
> refresh. not sure if we can do that across a db link.
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Friday, October 24, 2003 10:14 PM
> refresh - for
>
>
> > No, you didn't read it completely. Create table and create MV do the
same
> > thing - produce a copy of the data on a different location (or a
different
> > segment) that can be queried independently. However, I proposed a
> different
> > way of doing the MV creating and refreshing, not using the
> > dbms_mview.refresh procedure as documented, but by using prebuilt table
> and
> > using other faster methods such as CTAS and Direct Path load to do a
> > complete refresh. It offers severa advantages such as faster execution,
> much
> > less outage window and low resource utilization. As an added bonus, you
> > don't have to drop and recreate the read only MV when you add/alter a
> column
> > to the master table.
> >
> > In your case, you might want to consider converting the tables to MV if
> MVs
> > are used in such a way. One example is if you see some benefit from
Query
> > Rewrite, you may want to create the MVs on the tables using the ON
> PREBUILT
> > TABLE clause for Oracle to use QR.
> >
> > HTH.
> >
> > Arup Nanda
> > www.proligence.com
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Friday, October 24, 2003 9:24 PM
> > 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).
> > >
> > --
> > 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: 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).
>

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