Oracle-L: RE: SQL giving problem over the database link

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Mon, 22 Apr 2002 08:59:58 -0800
Message-ID: <F001.0044B011.20020422085958@fatcity.com>

Ashoke - Sympathy, but no firm answers here. I have seen this type of behavior before. Someone mentioned that the underlying problem is that the Oracle optimizer doesn't have enough information to make an intelligent decision in some database link situations. Sometimes I've given up in frustration and just pulled the entire table to the other side of the link. I haven't tried this, but I wonder if a hint might help its behavior. Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Monday, April 22, 2002 10:48 AM
To: Multiple recipients of list ORACLE-L

Greetings,

Here is the scenario.

We are trying to insert records into a remote table via database link after selecting the data locally.

This query hangs for ever:

INSERT INTO FWVERID.fwlot_pn2m_at_TFWVERID.PACING.MEDTRONIC.COM SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid =

(select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '2000005956');

select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '2000005956' : This query returns only one row.

SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (.....) : This select returns 15 rows.

If I replace the subquery as follows then it works great :

INSERT INTO FWVERID.fwlot_pn2m_at_TFWVERID.PACING.MEDTRONIC.COM SELECT * FROM FWLOT_PN2M WHERE fromid =
'00000e31.900fb406.37bc1803.00003074.2026';

I tried to do the same insert locally and it works great :

INSERT INTO fwlot_pn2m_gene
SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '2000005956');

For some reason the combination of database link and the subquery in the select clause is going for full table scan for each occurence of formid. I am wondering if this is a bug or something against the rule. WHERE FW.FWLOT_PN2M.fromid =

(select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '2000005956');

Any explanation or help is appreciated.

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------