Re: Performance problem with dates in Personal Oracle 7.3.3

Thanks for the reponse.

I had carried out various tests such as converting the date strings to dates using to_date etc..

I will check the optimiser configuration but the thing I don't understand is- even if the optimiser decides not to use the index why does the query take 9 seconds when various wildcard queries on another NON-indexed string field is effectively instantaneous.

Thanks
Brian

spencer <spencerp_at_swbell.net> wrote in message news:LiRa5.162$Kx6.68495_at_nnrp1.sbc.net...
> 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 Wed Jul 12 2000 - 00:00:00 CDT