Re: Concurrent/Parallel Execution

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 - 14:57:19 CST