Re: doubt in sql

From: Shabble <gbarbour_at_baesystemsdotcom>
Date: Fri, 25 Mar 2005 11:17:11 -0000
Message-ID: <4243f170_1@glkas0286.greenlnk.net>

"rasukutty" <rajendranr_at_systechusa.com> wrote in message news:a9d2733e771133016fd1190a74c138d1_at_localhost.talkaboutdatabases.com...
> I want to know the difference between IN and NOT IN with regard to
> performance.If Both are going to do full scan on the table then how is one
> going to perform over the other?
>

I await with interest the guru answers to this one.

Mark Gurry (guru) in his book 'Oracle SQL Tuning Pocket Reference' states that 'each can be faster than the other under certain curcumstances'. He does refer to 'NOT IN' as dreaded though. It's such an inexpensive book for the knowledge that's in it. Check out the bottom of page 56 for the beginnings of an answer to your query.

I will check my copy of the Jonathan Lewis (guru) book Practical Oracle 8i - Building Efficient Databases, when I get home. I was recently at a Scottish Oracle Users Group meet where I listened to one of Jonathan's talks; sell your silver to get to one of these, their awsome!

Shabble. Received on Fri Mar 25 2005 - 05:17:11 CST