Re: query across 3 Oracle DBs and MS-SQL

From: Michael Austin <>
Date: Thu, 12 Mar 2009 16:55:12 -0500
Message-ID: <qsful.3398$> wrote:
> Hello Guys,
> I will shortly be tasked with running some complex queries across 3 X
> Oracle DBs and MS-SQL. Each of these db has millions of lines. I have
> run queries again a single Oracle DB with millions of lines with some
> success - that take several hours to complete.
> However I have little idea of what is best practice to query databases
> on different servers and a vendor other than Oracle in the mix too.
> hints and tips and URLs gratefully received!!
> Syd

While there is HS and gateways and database links, you need to understand how it functions before you pull that trigger. In my current job I helped fix an app issue where the app did a 5-way join via dblink to a mainframe (all tables were on the mainframe). At the point I was called in, their web-based app had almost fatally crippled a mainframe with many billions of I/O's sec in just an hour or so... It was 8 hrs before it recovered - after I stopped the "gateway".

In the Oracle db the app did a 5-way join to 5 remotely defined tables.   What the mainframe saw for EACH query was:

select * from a;
select * from b;
select * from c;
select * from d;
select * from e where a=xyz;

So Oracle pulled the 4 tables plus the one or two rows from table 5 totaling more than 450MB for EACH query and then having Oracle perform the JOIN - and the front end app server was doing thousands of these per/hr.

Due to semi-static nature of the data, the solution was to create MVIEWS on the Oracle DB and have them populated once a day. That way the JOIN occurred locally and NOT on the mainframe/network. Received on Thu Mar 12 2009 - 16:55:12 CDT

Original text of this message