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

Daniel,

You got me! I should have stated "might get deprecated" instead of "is going to be deprecated". I do not think Oracle has released any notice of DECODE getting deprecated.

In this thread:
http://asktom.oracle.com/pls/ask/f?p=4950:8:842498144527990155::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1243867216406,

Connor states that he has seen this mentioned somewhere, however, Tom did not verify it ...

So I might be totally wrong in my statement below.

Anurag

  "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1066665341.400049_at_yasure...   Anurag Varma wrote:

"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
  Can you point me to anything that confirms your statement about DECODE being deprecated? Thanks.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)