Problem with Index on Date Field

From: Chris <stranger_v_at_hotmail.com>
Date: Tue, 21 Mar 2000 22:23:01 -0600
Message-ID: <38D84AA5.7038D0B5_at_hotmail.com>

Hi guys,
[Quoted] [Quoted]   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

[Quoted] [Quoted] We have a index on the creation_date field. Iam trying to run a report that [Quoted] [Quoted] shows me a count of number of users for every date. However when I do a select [Quoted] [Quoted] count(*) from user where to_date(creation_date) = to_date(&p_date) the query [Quoted] [Quoted] does not use the index(It takes about 17-18 sec to return the count for each date.

[Quoted] [Quoted] . If I do not use to_date(creation_date), the query returns 0 rows. This is [Quoted] [Quoted] becayse when I insert sysdate into creation_date when the row is created,it is [Quoted] timestamping also. I don't want to get rid of the time stamp. Is there any way [Quoted] to tune the query othyer then creating another date column without the time [Quoted] stamp component.

Any ideas/suggestions Received on Wed Mar 22 2000 - 05:23:01 CET