Re:FW: What Happenned to this query (sloging)

From: <dgoulet_at_vicr.com>
Date: Mon, 29 Jan 2001 14:22:07 -0500
Message-Id: <10756.127735@fatcity.com>

Glenn,

I believe all of this is false. Oracle will use the index in all cases of the IN clause. NOT IN and NOT EXIST is another question since one row matching the other side will cause in a FALSE result, kind of like NOT NULL in a way. What Oracle does do with the IN clause is rewrite it in a really awful way. Let's say you have "id in (1,2,3,4,5,6)". This gets rewritten in the optimizer as "id = 1 or id = 2 or id = 3 or id=4 or id=5 or id=6". This gets really messy if the in clause is against a sub-select.

Dick Goulet

____________________Reply Separator____________________

Subject: FW: What Happenned to this query (sloging) Author: Glenn Travis <Glenn.Travis_at_wcom.com> Date: 1/29/2001 10:35 AM

Is this true? (see below).

Also, got this;

> It still holds true for Oracle 8. Oracle will not use an index on an IN
clause,
> but it will if you use EXISTS and there is a usable index on the columns
you're
> querying.

> - --
> Stephen Clouse <stephenc_at_theiqgroup.com>
> Senior Programmer, IQ Coordinator Project Lead
> The IQ Group, Inc. <http://www.theiqgroup.com/>

Wow! Is this correct???

-----Original Message-----
On Behalf Of Scott T. Hildreth
Sent: Monday, January 29, 2001 11:51 AM
To: Simon Legdon
Cc: rnayathodan_at_yahoo.com; dbi-users_at_perl.org; Jeffrey-Seger_at_forum-financial.com

And Oracle 8, NOT EXISTS is huge speed increase.

On 29-Jan-01 Simon Legdon wrote:
> I was once told by an Oracle contractor that Oracle does not optimize NOT
IN
> queries well, and that the most efficient way of doing this is to use the
NOT
> EXISTS form that I suggested. This advice was for Oracle 7, I admit.
>
> I have used it and it does make a VERY noticable difference.
>
>>Actually, I'm not sure how Oracle or PG would handle this, but in
>>DB2, the nested loop join you warn against would depend on
>>how the optimizer is set up. It should set up temp tables and
>>do a hash join, which would be a lot quicker.
>
>
>
> ------------------------------------------------------------
> --== Sent via Deja.com ==--
> http://www.deja.com/
>
>

E-Mail: Scott T. Hildreth <shildreth_at_emsphone.com> Date: 29-Jan-01
Time: 10:49:57

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Glenn Travis
  INET: Glenn.Travis_at_wcom.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Mon Jan 29 2001 - 13:22:07 CST