it is the same. '%TATA.COM' is not a variable.
sumant
- Wolfgang Breitling <breitliw_at_centrexcc.com> 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: ssarkar97_at_yahoo.com
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: 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).
Received on Tue Jan 27 2004 - 18:24:26 CST
