Re: Information on multi threading of calls to procedures/functions i

Siva,

Not sure what you mean exactly. Let me rephrase it:

You have several source databases you want to refresh a materialized view ata single target site (your DW database). You want to update the taget MV concurrently from the sources.

If this is your requirements, here is an approach. Create the MV with ON PREBUILT TABLE option. Make the MV table partitioned so that each source occupies one partition. If you use 9i, it's pretty simple using LIST partitioning. If you need to partition on some other field, say, a date, then have a RANGE-LIST composite partitioning.

When you want to refresh the taget for a particular source, create a table at the target site, using CTAS, external tables, sql*loader, transportable tablespace or export/import; create all the indexes, constraints; analyze the table and then using ALTER TABLE <maintable> EXCHANGE PARTITION <the part> WITH TABLE <this table> INCLUDING INDEXES WITHOUT VALIDATION. You can do this for all the sources concurrently. Your target site will be unavialble for about 10 secs when the exchange takes place; available all the other times. This also reduces a lot of redo and undo generation. Your create table and create index statements can be parallelized, too.

If you can, use Matelized view logs on the source tables to capture only the changed data; but if you usea time based refresh this may not be necessary.

I designed such a datawarehouse with 25 sources and 10 years worth of data. I also gave a presentation about it. If you want, I can send it to you.

Hope this helps.

Arup Nanda
www.proligence.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, April 18, 2003 2:11 AM

> Hi DBA Gurus
>
> This is one of the unfamiliar ground for me.
>
> My clients wants to setup multi threaded environment for a sort of data
> warehouse project.
>
> data is refreshed at several remote oracle servers running on HP-UX
through
> materialized views. The tables are exported and ftped and imported into a
> centralized warehouse server running 9i on HP-UX.
>
> I am exploring possibilities to run concurrent (or is the right term multi
> thread?) processes for each of the imported table set broght over from a
> remote site.
>
> (Basically make multiple calls to same procedure/function sets in
different
> packages with different parameter sets for each of the remote sites.)
>
> I would appreciate any hint or lead to actual setup or documentation.
>
> thanks a ton in advance
>
> Regards
>
> Shiva
>
> can somebody give me a starting point?
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Baswannappa, Shiva
> INET: SXBaswan_at_dcss.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).