| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re:RE: RE: SQL giving problem over the database link
Ashoke,
The solution needs to be tailored to the application, which I'm sure is not the answer you want. We do have tables that are accessed via database links here and we do a substantial amount of work just to prevent some, but not all, of this. My basic approach is that if the remote table is small then leave matters alone as we'd probably do an unindexed read anyway. If it's large then look at replicating the table, with appropriate indexing onto the local node.
Now an insert across a db link is a whole other matter. In general I find them as efficient as a local insert, most of the time. The problems you can run into is locks, particularly full table locks, on the remote node by another application, excessive number of or unbalanced indexes on the remote node, goofy integrity constraints, and believe it or not triggers, particularly triggers that may affect a table on another remote node or better yet one locally. I've seen a case where a duhveloper set up a trigger scheme that when a local table was updated, the changes were propagated to a remote node than in turn tried to re-propagate those changes back to the local node and on, and on, and on. Finally blew the open cursors parameter, but what a pile of fun to untangle!!
Dick Goulet
____________________Reply Separator____________________ Author: "Mandal; Ashoke" <ashoke.k.mandal_at_medtronic.com> Date: 4/23/2002 5:38 AM
Dick,
So what should be the solution? Does it mean that we should not try to insert multiple rows across database link?
I also tried inline view but it didn't help.
Any help is appreciated.
Ashoke
-----Original Message-----
Sent: Monday, April 22, 2002 5:13 PM
To: Multiple recipients of list ORACLE-L
If you've got a sql statement that expects more than one row back across a database link it is very likely to issue a ' select <column>,<column>,etc... from <table_name>' statement across the link & resolve things locally via a temporary table, which is not indexed of course.
Dick Goulet
____________________Reply Separator____________________ Author: "Mandal; Ashoke" <ashoke.k.mandal_at_medtronic.com> Date: 4/22/2002 11:53 AM
It is supposed to return 15 rows.
Thanks,
Ashoke
-----Original Message-----
Sent: Monday, April 22, 2002 2:34 PM
To: Multiple recipients of list ORACLE-L
How many rows does it return ... ??
Raj
QOTD: Any clod can have facts, but having an opinion is an art!
-----Original Message-----
Sent: Monday, April 22, 2002 3:14 PM
To: Multiple recipients of list ORACLE-L
Hi Raj,
I tried this and still it hangs.
Thanks,
Ashoke
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mandal, Ashoke
INET: ashoke.k.mandal_at_medtronic.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Apr 23 2002 - 09:08:46 CDT
 |
 |