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

Paul Tomlinson wrote:
> 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.
>

Hello Paul,

AFAIK applying a function on a column value prevents any index on that column from being used. (Unless you have a function based index which uses that function)
So in your case, the version using 'in' does at least have a chance using an index whereas the version with 'decode' does not.

Hope that hepls,
Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de