Re: inlist iterator algorithm

• 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://www.freelists.org/webpage/oracle-l