index on DATE column

[reeshe]

Hi,

I got and index on EMP_DATE of table EMP;

How do I rewrite and make use of the index in the following commnad?

select count(*) from EMP where trunc(EMP_DATE)='25-DEC-85';

since the trunc function disables the index.

Thanks a lot

[pando]

something like

select count(*) from EMP
where EMP_DATE >= to_date('25-DEC-85', 'DD-MON-YY');
and  EMP_DATE < to_date('26-DEC-85', 'DD-MON-YY');

[reeshe]

thanks pando

[reeshe]

Hi,

Why is that I got different results for my queries?
Note that emp_date is indexed, so i dont want to use
a function to it.

QUERY1(full table scan)
-------
SELECT COUNT(*) FROM EMP WHERE TRUNC(EMP_DATE)=TRUNC(SYSDATE-30); (RESULT=8,842)

QUERY2
--------
SELECT COUNT(*) FROM EMP WHERE
EMP_DATE >=(SYSDATE-30) and EMP_DATE < (SYSDATE-30); (RESULT=0)

QUERY3
--------
SELECT COUNT(*) FROM EMP WHERE
EMP_DATE BETWEEN TRUNC(SYSDATE-30) and TRUNC(SYSDATE-30); (RESULT=2,100)


Please help me how to use indexed date column...

[Bore]

EMP_DATE >=(SYSDATE-30) and EMP_DATE < (SYSDATE-30); (RESULT=0)
is obviously FALSE. the date cannot be greater or equal than something and at the same time less than the same, so count is always 0
The first query returns all the rows, which have year, month and date of the month exactly equal to the sysate - 30 , ignoring the hour, min and sec component (8842 rows in your case)
The third query, for easier interpretation can be translated to
emp_date >= TRUNC(SYSDATE-30) and emp_date <=TRUNC(SYSDATE-30)

Obviously that should mean emp_date=TRUNC(SYSDATE-30). Here u see the difference.
In the first TRUNC(EMP_DATE)=TRUNC(SYSDATE-30)
in the third case EMP_DATE=TRUNC(SYSDATE-30)
Hence the third query returns only the records,which have in their emp_date
day,month and year the same as the date 30 days before sysdate, but they should have 00 hours 00 min 00 sec in their hour min, sec part, because the left hand side have not been truncated, so you'll not get any record, having the same year,month, day, but hour min and sec different from 00:00:00 (which you get in the first query due to the left hand side truncating)

To summarize, PANDO gave you the query. If you want to make it using SYSDATE, go for

emp_date>=trunc(sysdate-30) and emp_date < trunc(sysdate-30) +1

[reeshe]

thanks