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!

IWETHEY Home / IWETHEY Board / Databases Forum / It's just in the SELECT clause
Post #150,386
by Meerkat
4/7/04 2:02:20 AM
Reply
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.
Post #150,411
by ben_tilly
4/7/04 10:57:43 AM
Reply
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) - April 6, 2004, 09:47:12 PM EDT
         Highly unlikely - (ben_tilly) - (4) - April 6, 2004, 10:14:19 PM EDT
             Sounds like a great book - (Meerkat) - (3) - April 6, 2004, 10:28:33 PM EDT
                 Re: Sounds like a great book - (kelzer) - (2) - April 7, 2004, 06:11:50 PM EDT
                     Heh. We've got a comedian among us. - (Another Scott) - April 7, 2004, 06:16:56 PM EDT
                     My subconscious inadvertantly told you all - (Meerkat) - April 7, 2004, 11:59:02 PM EDT
         Depends on what clause - (ChrisR) - (3) - April 6, 2004, 10:52:32 PM EDT
             Just to clarify that - (ben_tilly) - (2) - April 7, 2004, 01:14:51 AM EDT
                 It's just in the SELECT clause - (Meerkat) - (1) - April 7, 2004, 02:02:20 AM EDT
                     Functions in the WHERE can be fine - (ben_tilly) - April 7, 2004, 10:57:43 AM EDT
         Belated answer: "What they said". - (CRConrad) - (8) - April 7, 2004, 09:50:49 AM EDT
             Transposing rows to columns is a PITA - (ben_tilly) - (7) - April 7, 2004, 10:50:12 AM EDT
                 "When the only tool you have is a hammer..." - (CRConrad) - (6) - April 8, 2004, 01:52:00 AM EDT
                     But that isn't the only tool that I have - (ben_tilly) - (5) - April 8, 2004, 02:23:07 AM EDT
                         At least Perl is a good hammer to have - (Meerkat) - (3) - April 8, 2004, 03:02:39 AM EDT
                             use that one a lot - (Steve Lowe) - (2) - April 8, 2004, 03:12:58 AM EDT
                                 I don't like it - (broomberg) - (1) - April 22, 2004, 10:58:47 PM EDT
                                     No feedback? That's what top / glance is for :) -NT - (Meerkat) - April 23, 2004, 02:23:05 AM EDT
                         No no no, you misunderstand! - (CRConrad) - April 8, 2004, 03:11:51 AM EDT
         Eliminate decode wherever you can - (tuberculosis) - April 9, 2004, 03:01:43 AM EDT

He’s big and he’s in the vomit!
92 ms