index on DATE column
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: index on DATE column

  1. #1
    Join Date
    Oct 2007
    Posts
    114

    index on DATE column

    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    something like

    Code:
    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');

  3. #3
    Join Date
    Oct 2007
    Posts
    114
    thanks pando

  4. #4
    Join Date
    Oct 2007
    Posts
    114
    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...

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

    Code:
    emp_date>=trunc(sysdate-30) and emp_date < trunc(sysdate-30) +1
    Last edited by Bore; 11-19-2007 at 08:52 AM.

  6. #6
    Join Date
    Oct 2007
    Posts
    114
    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width