Re: Problem with Index on Date Field

Hi

This should use the index
select count(*)
  from user
where creation_date between &p_date and &p_date+1;

HTH
Arvind Balaraman

Chris wrote in message <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 - 04:35:55 CET