Home » RDBMS Server » Networking and Gateways » Firing query to multiple database (Oracle 11g)
icon5.gif  Firing query to multiple database [message #528140] Sat, 22 October 2011 02:20 Go to next message
catchy87gmailcom
Messages: 9
Registered: October 2011
Location: Mumbai,India
Junior Member
Hi all,

Our Team is planning to find a new architecture for our new project.

In which we have to fire query to multiple database and then we have to collect all responses from them.(Suppose there are 10databases on which we have to fire query)

I searched a lot,the only thing I got is...It could be possible only through Database link(DbLink),Is there any other way to fire query on distributed databases...?

Thanks in advance.

Sanjeev
Re: Firing query to multiple database [message #528145 is a reply to message #528140] Sat, 22 October 2011 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Connect to each one.

Regards
Michel
Re: Firing query to multiple database [message #528146 is a reply to message #528145] Sat, 22 October 2011 03:04 Go to previous messageGo to next message
catchy87gmailcom
Messages: 9
Registered: October 2011
Location: Mumbai,India
Junior Member
Thanks for reply Michel.

But can you please elaborate yourself...?
Re: Firing query to multiple database [message #528171 is a reply to message #528146] Sat, 22 October 2011 09:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have 2 ways to access multiple databases:
- either you connect one and use db links to query others
- either you use one connection to each database

The former may guarantee you the transactional properties across the databases; the latter one, no.
It all depends on what you need it do and how (with which tool, which language...)

Regards
Michel
Re: Firing query to multiple database [message #528256 is a reply to message #528171] Sun, 23 October 2011 23:00 Go to previous messageGo to next message
catchy87gmailcom
Messages: 9
Registered: October 2011
Location: Mumbai,India
Junior Member
Thanks for all these help Michel.

Since First way also gives Transactional properties across the databases I think our team will go with that.

Thanks & Regards.
Sanjeev.
Re: Firing query to multiple database [message #528375 is a reply to message #528256] Mon, 24 October 2011 09:56 Go to previous messageGo to next message
catchy87gmailcom
Messages: 9
Registered: October 2011
Location: Mumbai,India
Junior Member
Hi Michel,

Can you please help me in these scenario(regarding dblink).

Scenario:
"Suppose i got connected to one database say 'db_0' and want to fire select query to 10 different databases say db_1,db_2,db_3,db_4,db_5,db_6,db_7,db_8,db_9 and db_10.

Each databases(db_1,db_2,db_3,db_4,db_5,db_6,db_7,db_8,db_9 and db_10) returning millions of rows.

Question :
Suppose I fire select query like this:

select * from emp@dblink1,emp@dblink2,...,emp@dblink10.(database links are stored in data dictionary of db_0)

Will this work,n what will be the flow...?

Or ANY MORE EFFECTIVE WAY TO EXECUTE THIS DBLINK QUERY...?

Thanks in advance.

Regards
Sanjeev
Re: Firing query to multiple database [message #528380 is a reply to message #528375] Mon, 24 October 2011 10:13 Go to previous messageGo to next message
John Watson
Messages: 4553
Registered: January 2010
Location: Global Village
Senior Member
Quote:
select * from emp@dblink1,emp@dblink2,...,emp@dblink10.(database links are stored in data dictionary of db_0)

Will this work,n what will be the flow...?


No, it won't work:
orcl>  select * from dept@l1,dept@l2;
 select * from dept@l1,dept@l2
        *
ERROR at line 1:
ORA-00918: column ambiguously defined


orcl>
Re: Firing query to multiple database [message #528383 is a reply to message #528375] Mon, 24 October 2011 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a real scenario, I don't see any practical case to cartesian join 10 tables from 10 different database and returning all columns.

Regards
Michel
Re: Firing query to multiple database [message #528447 is a reply to message #528383] Tue, 25 October 2011 00:27 Go to previous messageGo to next message
catchy87gmailcom
Messages: 9
Registered: October 2011
Location: Mumbai,India
Junior Member
Thanks Michel & John,

Actually I have written wrong query it was not Cartesian join it should be with UNION operation.

I'm reposting my rectified query :-
Scenario:
"Suppose i got connected to one database say 'db_0' and want to fire select query to 10 different databases db_1,db_2,db_3,db_4,db_5,db_6,db_7,db_8,db_9 and db_10.

All database having same table say emp(nId,nName)

Each databases(db_1,db_2,db_3,db_4,db_5,db_6,db_7,db_8,db_9 and db_10) returning millions of rows.

Question :
Suppose I fire select query like this:

select * from emp@dblink1
UNION
select * from emp@dblink2
.
.
.
UNION
select * from emp@dblink9
UNION
select * from emp@dblink10
.
(database links are stored in data dictionary of db_0)

Now Will this work,n what will be Efficiency?

Or ANY MORE EFFECTIVE WAY TO EXECUTE THIS DBLINK QUERY...?

Thanks in advance.

Regards
Sanjeev
Re: Firing query to multiple database [message #528451 is a reply to message #528447] Tue, 25 October 2011 01:24 Go to previous messageGo to next message
John Watson
Messages: 4553
Registered: January 2010
Location: Global Village
Senior Member
Have you considered using UNION ALL instead of UNION?
Re: Firing query to multiple database [message #528452 is a reply to message #528447] Tue, 25 October 2011 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A step further UNION ALL, I think firing 10 queries each one on a single database will be more efficient.

And a step even further, a connection on each database would even more performant.

Regards
Michel
Re: Firing query to multiple database [message #528454 is a reply to message #528452] Tue, 25 October 2011 01:46 Go to previous messageGo to next message
John Watson
Messages: 4553
Registered: January 2010
Location: Global Village
Senior Member
What are you actually trying to achieve? For example, if this is a once-only operation, you could use transportable tablespaces to bring the tables into one database, and partition exchange (if you have the appropriate licence) to consolidate all the tables into one. That would bring the whole process down to a few minutes.
Re: Firing query to multiple database [message #528461 is a reply to message #528451] Tue, 25 October 2011 02:16 Go to previous messageGo to next message
catchy87gmailcom
Messages: 9
Registered: October 2011
Location: Mumbai,India
Junior Member
I haven't considered for UNION ALL but if it is possible with that also then please let me explain detail.If you have any doubt regarding my scenario just let me know i will explain you again.

Regards
Sanjeev

Re: Firing query to multiple database [message #528463 is a reply to message #528461] Tue, 25 October 2011 02:38 Go to previous messageGo to next message
catchy87gmailcom
Messages: 9
Registered: October 2011
Location: Mumbai,India
Junior Member
Hi All,

We are looking for the scenario like suppose we wanted to fire 1 query to 10 different databases.At time we just want 100 records from each of the database should return.Is it possible to accumulate all the records from each of the database?

We wanted to show all the accumulated records[1000] in single application
Re: Firing query to multiple database [message #528465 is a reply to message #528463] Tue, 25 October 2011 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it is possible.

Regards
Michel
Re: Firing query to multiple database [message #528466 is a reply to message #528465] Tue, 25 October 2011 03:01 Go to previous messageGo to next message
alokdubeymail
Messages: 1
Registered: October 2011
Location: INDIA
Junior Member
I want to know how many maximum and minimum user logged at database between two days ,how to get it?
Re: Firing query to multiple database [message #528471 is a reply to message #528465] Tue, 25 October 2011 03:49 Go to previous messageGo to next message
catchy87gmailcom
Messages: 9
Registered: October 2011
Location: Mumbai,India
Junior Member
Hi Michel,

We haven't done these type of query before,can you help us by giving the exact way how we will fire such a single query to 10 databases(having same table name) and then how all the records get collected in single applications.

Suppose 100 records are coming from each databases and at application it becomes 1000 records.

Provide us what query we have to fire.

Thanks in advance

Regards
Sanjeev
Re: Firing query to multiple database [message #528472 is a reply to message #528471] Tue, 25 October 2011 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We already answered you on this point, which point is stucking you?
Do you know how to get the result of a query into an array?

Regards
Michel
Re: Firing query to multiple database [message #528488 is a reply to message #528171] Tue, 25 October 2011 05:07 Go to previous messageGo to next message
catchy87gmailcom
Messages: 9
Registered: October 2011
Location: Mumbai,India
Junior Member
Hi Michel,

I know fetching query into array.Can you just confirm below mentioned things :-

1)
According to you this query will work fine with UNION ALL:-

select * from emp@dblink1
UNION ALL
select * from emp@dblink2
.
.
.
UNION ALL
select * from emp@dblink9
UNION ALL
select * from emp@dblink10.

(database links are stored in data dictionary of db_0)

2)
And you also suggested me that it would be more performant if we make connection to each databases.How we can connect from a single application to multiple databases...?


thanks in advance
Sanjeev



Re: Firing query to multiple database [message #528492 is a reply to message #528488] Tue, 25 October 2011 05:16 Go to previous messageGo to next message
John Watson
Messages: 4553
Registered: January 2010
Location: Global Village
Senior Member
Sanjeev, I notice that you ignored my previous replies completely (not even a thank you) so you'll probably ignore this one, too. But none-the-less:
Have you considered using the Scheduler, to execute the remote queries concurrently? Create ten lightweight jobs, to retrieve the rows from each database and insert them into a local table. This might reduce your run time by a factor of ten.
Re: Firing query to multiple database [message #528497 is a reply to message #528488] Tue, 25 October 2011 05:30 Go to previous message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
we can connect from a single application to multiple databases...?

Just like you do it for a single connection but multiple times.

Regards
Michel
Previous Topic: listener.ora , tnsnames.ora
Next Topic: Oracle disconnect
Goto Forum:
  


Current Time: Tue Sep 16 20:51:33 CDT 2014

Total time taken to generate the page: 0.08958 seconds