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@xxxxxxxxxxxxxxxxxx>wrote: > ----- Original Message ----- From: "Dba DBA" <oracledbaquestions@xxxxxxxxx > > > To: <oracle-l@xxxxxxxxxxxxx> > 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/> > > > -- > //www.freelists.org/**webpage/oracle-l<//www.freelists.org/webpage/oracle-l> > > >