Re: Making A "Hash" of it. Why are indexes not used?

From: srivenu <srivenu_at_hotmail.com>
Date: 27 Feb 2004 04:40:33 -0800
Message-ID: <1a68177.0402270440.4f2812cb@posting.google.com>

-In this case it may be O.K. but note that converting an IN clause to
a
-JOIN may return wrong results.
-regards
-Srivenu

--how's that, Srivenu?

create table x(A number);
create table y(B number);

insert into x values(1);
insert into x values(2);
insert into x values(3);
insert into y values(1);
insert into y values(1);

select * from x,y where x.a=y.b;

         A B
---------- ----------

         1          1
         1          1

select * from x where x.a in(select b from y);

         A

---

         1

You get right results only if the is a UNIQUE constraint on the column B in table Y.
In fact Oracle unnests and rewrites the IN Sub query into a join if the join is guaranteed to return the same data. Please refer to this URL.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/opt_ops.htm#1005654

regards
Srivenu Received on Fri Feb 27 2004 - 06:40:33 CST