IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New Depends on what clause
If the decode is in the WHERE clause, then the query plan can't use a straight up index. If it's in the SELECT clause, it shouldn't cause too much degradation.

Then again, I'd use the standard CASE construct, but it should pretty well perform the same.
New Just to clarify that
The presence of a DECODE statement in the where clause does not block indexes from working. But unless you have a function-based index, a DECODE on an indexed field will not be able to take advantage of an index on that field.

You can sometimes get around this by adding a condition which is not as precise as the DECODE (such as an IN clause) which the index can work on in addition to the DECODE.

If, that is, an index is good in your case. Sometimes not using an index is a better query plan.

Cheers,
Ben
To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act
- [link|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20|Nebraska Supreme Court]
New It's just in the SELECT clause
I've been doing my own little education campaign to try and stop folks using funcions in a WHERE :)

John. Busy lad.
New Functions in the WHERE can be fine
Depends on the context and how you do them. For instance if you want all records for a given day I might write it like this in Oracle:
\n  ... some_dm >= TRUNC(:date)
AND some_dm < TRUNC(:date) + 1\n

If there is an index on some_dm, it can now be used. And sure there is a function, but a smart optimizer should avoid having to recalculate it all of the time. I don't have proof that Oracle actually does it, but I've never had a query performance problem be tracked down to this either. And I don't have tools to test it at home. (However I'd be somewhat surprised if it both could correctly spot the optimization when it let it use indexes, which I've seen it do, and not just have logic to factor out the function in general.)

Cheers,
Ben
To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act
- [link|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20|Nebraska Supreme Court]
     Oracle: 'DECODE' performance impact? - (Meerkat) - (19)
         Highly unlikely - (ben_tilly) - (4)
             Sounds like a great book - (Meerkat) - (3)
                 Re: Sounds like a great book - (kelzer) - (2)
                     Heh. We've got a comedian among us. - (Another Scott)
                     My subconscious inadvertantly told you all - (Meerkat)
         Depends on what clause - (ChrisR) - (3)
             Just to clarify that - (ben_tilly) - (2)
                 It's just in the SELECT clause - (Meerkat) - (1)
                     Functions in the WHERE can be fine - (ben_tilly)
         Belated answer: "What they said". - (CRConrad) - (8)
             Transposing rows to columns is a PITA - (ben_tilly) - (7)
                 "When the only tool you have is a hammer..." - (CRConrad) - (6)
                     But that isn't the only tool that I have - (ben_tilly) - (5)
                         At least Perl is a good hammer to have - (Meerkat) - (3)
                             use that one a lot - (Steve Lowe) - (2)
                                 I don't like it - (broomberg) - (1)
                                     No feedback? That's what top / glance is for :) -NT - (Meerkat)
                         No no no, you misunderstand! - (CRConrad)
         Eliminate decode wherever you can - (tuberculosis)

What a bizarre 'field' ... all about 'Information' - and nobody has any you'd trust!
108 ms