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

Anurag Varma wrote:

> 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
> <mailto: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)
>

Thanks. Because I have seen nothing other than idle speculation based on CASE. And I don't consider
CASE a substitute for DECODE And even if it were a replacement ... deprecating DECODE would
break a huge number of legacy applications that depend on it.

Mr. Townsend ... any comment?

-- 
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)