Re: Problem with Index on Date Field

From: Tom Zamani <tomz_at_redflex.com.au>
Date: Wed, 22 Mar 2000 15:55:17 +1100
Message-ID: <8b9k5u$aio$1_at_perki.connect.com.au>


try to use hint and force the query to use the nidex. tom
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 Wed Mar 22 2000 - 05:55:17 CET

Original text of this message