it is the same. '%TATA.COM' is not a variable. sumant
--- Wolfgang Breitling <[EMAIL PROTECTED]> wrote: > Is the sql really "the same query is run from a stored > procedure" or is it > perhaps using in place of the '%TATA.COM' a plsql variable > (which is set > to %TATA.COM)? > > At 04:44 AM 1/27/2004, you 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).
