Re: query across 3 Oracle DBs and MS-SQL
Date: Thu, 12 Mar 2009 16:55:12 -0500
Message-ID: <qsful.3398$Lr6.1766_at_flpi143.ffdc.sbc.com>
sydneypuente_at_yahoo.com 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