Re: query across 3 Oracle DBs and MS-SQL
Date: Thu, 12 Mar 2009 06:26:18 -0700 (PDT)
Message-ID: <a7db3493-deda-4c76-aaa1-c78f7b7386f1_at_b16g2000yqb.googlegroups.com>
On Mar 12, 7:00 am, sydneypue..._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
I guess it depends on what you mean by a complex query since that is kind of vague. Many queries against tables with millions of rows can be processed in seconds depending on indexing and types of aggregation involved.
When you involved multiple oracle databases at perhaps multiple different sites one technique involves creating database links between the databases. But bottom line the query has to execute at one site and when you start joining across tables at different sites ... that often gets really ugly. ( No idea how you get a link into SQL Server but I think that's possible ).
One possible design to get better performance is get all the data into the same oracle database and have oracle able to execute a query that does not involve using database links. There are several possible approaches here like using replication ( now streams ) to get it all together ... or doing several a create table as select from link etc.
Do you have people in your organization that have already accomplished similar things in a similar environment? If so I would start by seeing what groundwork has already been setup.
If you are a pioneer for your organization then depending on expectations and time available and your relative experience with complications like this might be best to bring in someone experienced with complicatiions as your describe.
Sounds like a good learning experience if expectations can be managed appropriately ... so have fun! Received on Thu Mar 12 2009 - 08:26:18 CDT