Re: Query Performance: Using DECODE() instead of IN()

"Paul Tomlinson" <rubberducky703_at_hotmail.com> wrote in message news:bn0s31$rs13r$1_at_ID-116287.news.uni-berlin.de...
> Guys, a question,
>
> Which is the more effecient way of running a query:
>
> select * from x where decode(y,'Y',1,'Q',1,0) = 1
>
> or
>
> select * from x where y in ('Y','Q')
>
> I think I recall somewhere that using a decode uses an index scan where
> using IN() uses a full table scan, however I can't seem to prove this.
>
> Trying to search for in and decode in google is not fun. Any ideas?
>
> PT
>
>

In the above two cases, use the IN. Any function on a column used in where clause, might disable index usage.
Also: Decode is going to be deprecated in future Oracle versions

Anurag Received on Mon Oct 20 2003 - 09:45:53 CDT