On Thu, Oct 18, 2001 at 11:41:54AM -0400, Yibin Dong wrote:
> It depends on which database you are using. In Oracle,
> when TO_DATE() is employed within where clause, all indexes
> will be useless. Oracle will do a range scan instead of
> index row scan.
I don't think that's accurate. The index is useless if a function is used
on the value of the column. In Javier's query:
> > >$sql = "DELETE FROM ft_t_ispc WHERE LAST_CHG_TMS <
> > >TO_DATE('$final_date','MM/DD/YYYY')";
the TO_DATE function is not being used on the column; it's being used on
the value the column is being compared to.
I tried a query like this:
SELECT count(*)
FROM mytable
WHERE mydate < TO_DATE('2000-01-01', 'YYYY-MM-DD')
and Oracle did a fast full scan of the index on mydate.
If I had done a TO_CHAR on the mydate column instead, then the index would
have been useless.
Ronald
