Re: Concurrent/Parallel Execution

I had another thought, I have not addressed the concurrency part of your question. If you are trying to save time by not running read 1, then 2, then 3 serially and want them read all at the same time, I don't think there is a way to do it with a straight query, if you want them all read at the same time simultaneously there are 2 ways to do this:

mviews, they can all refresh at the same time, (common datawarehouse approach)

or have 3 unix script to populate a staging table and have those 3 jobs run in parallel on the host os, then query the staging table that shows everything combined.

Mike Fullerton
Oracle DBA

mfullerton_at_gmail.com wrote:
> Well, I would have to run a test to see if the parallel hint added to
> the select would run the parallel process on the target or host. I
> guess a more important question for this would be what are you trying
> to accomplish with the distributed query? Did you want to run parallel
> processes on each server and bring the results back to the host in
> order to spread the IO and CPU? If that is the case I would say that
> RAC is what you need. However, if these other databases are different
> systems (customer support, order entry, etc.) then that wouldn't work
> since they are differnt DB's.
>
> If it is pure performance for a set of reports using all these spread
> out tables, read about materialized views like in my prior post. What
> it will do is build a "copy" table from each system on to your main
> database, then you could build a materialized view joining and/or
> filtering all those "copy" tables into one single copy table and use
> parallelism to use all the resources of the central database server to
> run as quick as possible. These mviews can be set to refresh all on a
> schedule or as records are inserted (fast refresh on commit).
>
> Using mviews is better if you will run this report several times a day,
> that way you will take network traffic out of the picture since the
> tables will be read from the same database.
>
> Mike Fullerton
> Oracle DBA
> ihb wrote:
> > <mfullerton_at_gmail.com>
> > >I agree with Robert, cross database union is what you need. Believe it
> > > or not, this is not uncommon to do, and since this is theory only
> > > (Keeping it Simple), here is what can be done:
> > >
> > > create database links to each target database. Write a view to do you
> > > union:
> > > select my_column from my_table_at_orcl1
> > > union
> > > select my_column from my_table_at_orcl2
> > > union
> > > select my_column from my_table_at_orcl3;
> > >
> > > A better way to do this is create a data warehouse environment:
> > >
> > > create database links to each system
> > > create materialized views on each target table
> > > create a materialized view on top of all of them that has your specific
> > > where clause info, summaries, etc...
> > >
> >
> > Right, but the tricky part is what I asked ORIGINALLY - Parallelism : i.e.
> > orcl1, orcl2, orcl3 run simultaneously
> > So you think, "Parallel Query" is the way to achieve this ?
> > thanks
Received on Wed Nov 22 2006 - 15:25:36 CST