MIME-Version: 1.0
In-Reply-To: <CAE-dsOKkcokGkGq=pPRLHGmBYJA9tYug+2S7VKCi22S8T9CJEw@mail.gmail.com>
References: <CAE-dsOKkcokGkGq=pPRLHGmBYJA9tYug+2S7VKCi22S8T9CJEw@mail.gmail.com>
Date: Wed, 6 Jul 2011 13:03:33 -0700
Message-ID: <CACpWLjNzLh+aMYZ2JbDUr4YDqbL7fXRo1BgtTEmnk4q5iHnekg@mail.gmail.com>
Subject: Re: inlist iterator algorithm
From: Michael Moore <michaeljmoore@gmail.com>
To: oracledbaquestions@gmail.com
Cc: oracle-l@freelists.org
Content-Type: multipart/alternative; boundary=0016369201cc54989604a76c18c5
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: michaeljmoore@gmail.com
--0016369201cc54989604a76c18c5
Content-Type: text/plain; charset=ISO-8859-1

IN can be thought of as a shortcut for writing a series of ORs.
NOT IN can be thought of as  a shortcut for writing a series of ANDs.
Consider this:
select dummy from dual where dummy not in  ('z',null,'y');

Mike


On Wed, Jul 6, 2011 at 12:46 PM, Dba DBA <oracledbaquestions@gmail.com>wrote:

> 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?
>

--0016369201cc54989604a76c18c5
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

IN can be thought of as a shortcut for writing a series of ORs.<div>NOT IN =
can be thought of as=A0=A0a shortcut for writing a series of ANDs.</div><di=
v>Consider this:</div><div>select dummy from dual where dummy not in =A0(&#=
39;z&#39;,null,&#39;y&#39;);</div>
<div><br></div><div>Mike</div><div><br><br><div class=3D"gmail_quote">On We=
d, Jul 6, 2011 at 12:46 PM, Dba DBA <span dir=3D"ltr">&lt;<a href=3D"mailto=
:oracledbaquestions@gmail.com">oracledbaquestions@gmail.com</a>&gt;</span> =
wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex;"><div>I am not sure if this is documented. I=
 am just curious. </div>
<div>=A0</div>
<div>When Oracle does an inlist iterator, what is it doing under the cover.=
 When I think &quot;iterator&quot;, I think</div>
<div>=A0</div>
<div>=A0=A0 for i in array.start.. array.end</div>
<div>=A0=A0=A0=A0=A0 run query(i)</div>
<div>=A0=A0 end loop;</div>
<div>=A0</div>
<div>so if i have 5 values in the inlist, the query runs 5 times. once for =
each value in the inlist. </div>
<div>=A0</div>
<div>=A0</div>
<div>My understanding</div>
<div>1. In, is a group of &quot;or&quot; statements</div>
<div>2. Or is the samething as a union all</div>
<div>=A0</div>
<div>=A0</div>
<div>So when you do an or or a union all, you are running 2 queries. </div>
<div>=A0</div>
<div>Is the inlist smarter than that? </div>
</blockquote></div><br></div>

--0016369201cc54989604a76c18c5--
--
http://www.freelists.org/webpage/oracle-l