Re: Problem with Index on Date Field

A copy of this was sent to Chris <stranger_v_at_hotmail.com> (if that email address didn't require changing) On Tue, 21 Mar 2000 22:23:01 -0600, you wrote:

>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

select count(*)
  from T
 where creation_date between to_date('&p_date')

              and to_date( '&p_date. 23:59:59', 'dd-mon-rrrr hh24:mi:ss' )

has the opportunity to use an index.

-- 
http://osi.oracle.com/~tkyte/
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation