Re: query across 3 Oracle DBs and MS-SQL
Date: Thu, 12 Mar 2009 16:55:12 -0500
> 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!!
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