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).
Received on Mon Jun 09 2003 - 19:04:33 CDT
