This is what the book (page 196 Oracle SQL High-Performance Tuning - Second Edition, Guy Harrison.) says about your situation.
INLIST ITERATOR step indicates that each subsequent step was executed once for each value in an IN list. A large number of elements in the IN list can be time consuming, because the cost-based optimizer is required to calculate costs for each of the concatenated subqueries. Only thing I would suggest is to remove first Order by Clause if you have any to remove sort step and see if it helps. HTH Mohammed Shakir --- gmei <[EMAIL PROTECTED]> wrote: > RE: query run time vs IN list elementsHi: > > 1. I should have mentioned that I analyzed the index after creating > the > index, also I looked at the explain table in both situations: > > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=81 Card=267 > Bytes=29 > 37) > > 1 0 SORT (GROUP BY) (Cost=81 Card=267 Bytes=2937) > 2 1 TABLE ACCESS (FULL) OF 'GENE2DISEASE2H' (Cost=28 > Card=26 > 7 Bytes=2937) > > > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=267 > Bytes=29 > 37) > > 1 0 SORT (GROUP BY) (Cost=57 Card=267 Bytes=2937) > 2 1 INLIST ITERATOR > 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'GENE2DISEASE2H' > (Cos > t=4 Card=267 Bytes=2937) > > 4 3 INDEX (RANGE SCAN) OF 'GENE2DISEASE2H_GENEID' > (NON-U > NIQUE) (Cost=2 Card=267) > > > 2. I have not tried using "exists". I was wondering why IN had such > problem > (I have not seen this situation before)? > > Guang > -----Original Message----- > From: Whittle Jerome Contr NCI [mailto:[EMAIL PROTECTED] > Sent: Friday, June 06, 2003 2:14 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: RE: query run time vs IN list elements > > > Guang, > > 1. Just because you created an index doesn't necessarily mean > Oracle is > using it especially if using CBO and you haven't analyzed the table > and > index after the index creation. Try using a hint. > > 2. If IN isn't meeting your needs, try converting it to an EXISTS > statement. > > Jerry Whittle > ASIFICS DBA > NCI Information Systems Inc. > [EMAIL PROTECTED] > 618-622-4145 > > -----Original Message----- > From: gmei [SMTP:[EMAIL PROTECTED] > > Hi: > > Today I have something I don't fully understand. I have oracle > 8173 on > Sun > Solaris. I have the following query that runs pretty fast when > the > number of > elements in the "IN" list is small. But if I kept adding more > "geneids" > in > the IN list, my query time increased dramatically. Now there is > no index > on > any columns on the table. I got very similar results even if I > created > index > on gene2disease2H.geneid. So this seem to suggest this situation > has not > to > do with index. > > So my question is: why did I see the sigificant time increase > when I > only > add one more geneid? > > TIA. > > Guang > > > > <<< cut >>> > > ===== Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __________________________________ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir 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).
