|
|
|
|
|
|
|
|
|
|
| Re: Firing query to multiple database [message #528375 is a reply to message #528256] |
Mon, 24 October 2011 09:56   |
 |
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 |
John Watson
Messages: 8929
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 #528447 is a reply to message #528383] |
Tue, 25 October 2011 00:27 |
|
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 #528454 is a reply to message #528452] |
Tue, 25 October 2011 01:46 |
John Watson
Messages: 8929
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 #528492 is a reply to message #528488] |
Tue, 25 October 2011 05:16 |
John Watson
Messages: 8929
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
