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. [EMAIL PROTECTED]
-----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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
