Re: Performance problem with dates in Personal Oracle 7.3.3

have you run an EXPLAIN to verify that the index is being used ? if it's not being used, have you made sure that both expressions on either side of the comparison operator is of the same data type, DATE ?

that is, if your SQL looks like this

where datecolumn >= '11-JUL-99'

then you are actually telling Oracle to compare a date expression to a character expression, and that means Oracle has to do a conversion on one side or the other... and Oracle may elect to do the conversion on either side (or perhaps even both sides), whichever makes most sense. e.g.

where TO_CHAR(datecolumn) >= '11-JUL-99'

-or-

where datecolumn >= TO_DATE('11-JUL-99')

if Oracle chooses to convert the date column to a character expression, then Oracle will NOT use the available index on datecolumn.

and in my experience, when given the choice, Oracle always seems to cast the date expression to a character expression. why? i'm not sure. but one plausible explanation is that Oracle "knows" that all (valid) DATE expressions can be converted to character expressions... but not every character expression can be converted to a valid date.

on my Orcle databases, i REQUIRE the developers to explicitly code conversion functions in the SQL so that the data types on each side of a comparison operator are of the same data type.

if Oracle still doesn't use the index when you cast the expression on the right side to a DATE expression, and you are using cost based optimization, it could be that Oracle is estimating the cost of using the index to be too high, so make sure that you have computed statistics on the table and all of the indexes.

if it still doesn't use the index, then consider using a hint in the SQL... sometimes /*+ RULE */ is all it takes.

HTH "brian" <brian.pacitti_at_btinternet.com> wrote in message news:8kg0ij$8ab$1_at_neptunium.btinternet.com...
> I have a table with a timestamp column of type DATE - the
 column has a non
> unique index defined. When I use this column in a where
 clause, e.g WHERE
> datecolumn >= startdate and datecolumn < enddate, the
 performance is dire.
>
> As a test I created another column to type varchar2 and set
 this to a string
> conversion of the date field. When I use this string column in
 the where
> clause the performance is OK. i.e. a few milliseconds compared
 to 9 secs for
> the date field. Also I can do queries with wild card searched
 on other
> string columns no problem. Is there a known issue with
 Personal Oracle 7.3.3
> with date types ???
>
> Any help would be appreciated
>
> Thanks
> Brian
>
>
>
Received on Tue Jul 11 2000 - 00:00:00 CDT