It's a bad query that could probably be resolved throuh an analytic function but I don't normally delve into things like that before having finished my 2nd coffee. You can use hints, in particular, there is a hint to force hash join. On 01/27/2004 06:44:25 AM, S.Sarkar wrote:
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).
-- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
