Re: inlist iterator algorithm

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Thu, 7 Jul 2011 10:58:03 -0400
Message-ID: <CAE-dsOLtS_YCYG-RXQ3gkztcASWEZXs2d=EDDSr3ujqnaw9YqA_at_mail.gmail.com>



so if i have an inlist that has 500 values in it, oracle will execute the query 500 times?

On Wed, Jul 6, 2011 at 4:12 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>wrote:

> ----- Original Message ----- From: "Dba DBA" <oracledbaquestions_at_gmail.com
> >
> To: <oracle-l_at_freelists.org>
> Sent: Wednesday, July 06, 2011 8:46 PM
> Subject: inlist iterator algorithm
>
>
>
> I am not sure if this is documented. I am just curious.
>>
>> When Oracle does an inlist iterator, what is it doing under the cover.
>> When
>> I think "iterator", I think
>>
>> for i in array.start.. array.end
>> run query(i)
>> end loop;
>>
>> so if i have 5 values in the inlist, the query runs 5 times. once for each
>> value in the inlist.
>>
>>
>> My understanding
>> 1. In, is a group of "or" statements
>> 2. Or is the samething as a union all
>>
>>
>> So when you do an or or a union all, you are running 2 queries.
>>
>> Is the inlist smarter than that?
>>
>>
>>
>
>
> Yes, but your image is reasonably correct.
>
> One benefit of the IN-list iterator is that Oracle only evaluates one
> execution plan, then runs it many times, whereas a manual UNION-ALL would
> require Oracle to optimize each query block separately. (And sometimes
> that's what you want to use a UNION ALL, of course).
>
> Another benefit applies to things like partitions where you could have (for
> example):
>
> list_partition_column in (1, 10, 99, 3000) and the optimizer could infer
> that 1 and 10 were in partition 1, 99 was in partition 10, and 3000 was in
> partition 499 - so it's iteration would be through 3 partitions, not a
> nominal 4 - which is what would happen if you wrote a UNION ALL.
>
> Regards
> Jonathan Lewis
>
>
>
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.**wordpress.com <http://jonathanlewis.wordpress.com/>
>
>
> --
> http://www.freelists.org/**webpage/oracle-l<http://www.freelists.org/webpage/oracle-l>
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jul 07 2011 - 09:58:03 CDT

Original text of this message