| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Dates
Paul,
I should have been more clear - I was not suggesting that it should be coded that way, only that this was a clearer way to understand what the LIKE condition really says, by making sure that no implicit conversions happen. Personally, though, I have no objection to functions on columns, as long as you are aware that they should only be used where they do not (even potentially) disable use of an index that you'd want to use. The advantage of
TRUNC(NC_DATE_CLS) = TO_DATE('25-JUN-04') over
NC_DATE_CLS LIKE '25-JUN-04' is that while they *both* apply functions to NC_DATE_CLS (an *implicit* conversion function, in the case of the second condition), only the first condition makes it obvious that this potentially index-use-disabling thing has happened, and that a still better, functionally equivalent condition would be
NC_DATE_CLS >= TO_DATE('25-JUN-04') AND NC_DATE_CLS < TO_DATE('26-JUN-04')
if you want to limit an index range scan using the NC_DATE_CLS column.
Dan Tow
650-858-1557
www.singingsql.com
Quoting Paul Drake <discgolfdba_at_yahoo.com>:
> --- Dan Tow <dantow_at_singingsql.com> wrote:,
> > as is good practice) "WHERE
> > TRUNC(NC_DATE_CLS) = TO_DATE('25-JUN-04')".
>
> Dan,
>
> trunc(date_var) on the left hand side of a where
> clause is so evil, it pains me to see it anywhere in
> print.
>
> it makes me want to wield the hammer of a function
> based index until the developer fixes their mistake
> and recodes it properly.
>
> sorry to rip your head off on this one (I liked your
> book quite a bit) and this only matters if the date
> column was indexed - but - this is one of the most
> irritating pieces of code that I've ever run across,
> and I see it very frequently at the top of a statspack
> report.
>
> proper filtering against dates is covered in depth at
> the ask tom site.
>
> Paul
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Jun 25 2004 - 12:47:32 CDT
 |
 |