All, i have this query:
SELECT count(1) FROM ats.emktg_members t1 WHERE NOT EXISTS ( SELECT 'x' FROM gcd_data_source_details t2 WHERE t2.universal_id = t1.universal_id AND t2.data_source_id = 13 ) AND upper(t1.email) NOT LIKE '%TATA.COM'; This query finishes in about 5 minutes. The plan is: Operation Object Name Rows Bytes Cost Object Node SELECT STATEMENT Hint=CHOOSE 1 14919 SORT AGGREGATE 1 75 HASH JOIN ANTI 272 K 19 M 14919 TABLE ACCESS FULL EMKTG_MEMBERS 274 K 14 M 14444 TABLE ACCESS BY INDEX ROWID GCD_DATA_SOURCE_DETAILS 1 K 21 K 391 INDEX RANGE SCAN DSD_DSRC_FKI 23 K 27 However, when the same query is run from a stored procedure, it picks up a bad plan (with nested loops join) and does not complete even after 6 hours ! Giving HASH_AJ hint did not change the plan. Any ideas how we can fix this (without using stored outlines) ? The database is 9204 on sun solaris. regards, Sumant __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: S.Sarkar INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
