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

The proof is in the pudding, so run EXPLAIN PLANs on your queries. However, any function on a column in the WHERE clause will negate any indexes (unless you take appropriate steps). So the DECODE function on the Y column will supress index usage, providing an index exists on this column. Your second query should use an index provided that the CBO determines that a full table scan is not more cost effective. YMMV so you'll need to test.

HTH,
Brian

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.
>
> Trying to search for in and decode in google is not fun. Any ideas?
>
> PT

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"