Re: Problem with Index on Date Field

The reason the index is not used is because of the function (to_date) on the creation_date column. In general, if you want to use and index on a column, do not apply a function to it on the left side of the operator.

You can intentionally avoid an index access with a function or by adding 0 (zero) to a numeric column or concatenating null to a varchar.

Chris <stranger_v_at_hotmail.com> wrote in message news:38D84AA5.7038D0B5_at_hotmail.com...
> Hi guys,
> We have a User table that has 200,000 + rows in it. This table has the
> following columns
> signon,
> user_id,
> password,
> email,
> creation_date,
> status
>
> We have a index on the creation_date field. Iam trying to run a report
that
> shows me a count of number of users for every date. However when I do a
select
> count(*) from user where to_date(creation_date) = to_date(&p_date) the
query
> does not use the index(It takes about 17-18 sec to return the count for
each
> date.
>
> . If I do not use to_date(creation_date), the query returns 0 rows. This
is
> becayse when I insert sysdate into creation_date when the row is
created,it is
> timestamping also. I don't want to get rid of the time stamp. Is there any
way
> to tune the query othyer then creating another date column without the
time
> stamp component.
>
> Any ideas/suggestions
>
Received on Thu Mar 23 2000 - 16:41:32 CET