This discussion is archived
14 Replies Latest reply: Sep 10, 2011 8:34 AM by John Spencer RSS

creating index on datepart of datetime field

883189 Newbie
Currently Being Moderated
Hello all,

I need to create an index on date part of datetime field column. My sample datefiled column values are as follows:

10/24/2009 12:09:58 PM
10/25/2009 7:05:19 AM
10/20/2009 9:52:25 AM
10/29/2009 11:5:21 PM
10/30/2009 9:8:17 PM

Which function can be used to create index - to_date or to_char ?

I tried running the following querys :

create index tmp on table1(to_date(datecolumn,'DD-MM-YYYY'));

create index tmp on table1(to_char(datecolumn,'mm/dd/yyyy'));

the above two querys works.
But below query doesn't wrk..

create index tmp on table1(to_date(datecolumn,'mm/dd/yyyy'));

why and which to use and how?

help is really appreciated...thanks in advance
  • 1. Re: creating index on datepart of datetime field
    mtefft Journeyer
    Currently Being Moderated
    Why do you want to do this, instead of simply creating an index on the column as it is?

    That is, why not just
    create index tmp on table1(datecolumn);
    ?

    If you must, you can
    create index tmp on table1(trunc(datecolumn));
    but, without knowing your motivation, I can't say you will get from it what you are seeking.
  • 2. Re: creating index on datepart of datetime field
    John Spencer Oracle ACE
    Currently Being Moderated
    Personally, I would use create index tmp on table1(trunc(datecolumn)). Then quesry it as:
    select stuff from table1
    where trunc(datecolumn) = trunc(sysdate)
    You should never to_date a date since it requires an implicit to_char first which will use the nls_date_format as the format mask.

    John
  • 3. Re: creating index on datepart of datetime field
    John Spencer Oracle ACE
    Currently Being Moderated
    Delete duplicate

    Edited by: John Spencer on Sep 9, 2011 4:55 PM
  • 4. Re: creating index on datepart of datetime field
    John Spencer Oracle ACE
    Currently Being Moderated
    Delete triplicate

    Edited by: John Spencer on Sep 9, 2011 4:55 PM
  • 5. Re: creating index on datepart of datetime field
    883189 Newbie
    Currently Being Moderated
    I definitely need to create an index on date column as the data is in millions and without index its taking a lot of time to query..
    what do u mean by

    "You should never to_date a date since it requires an implicit to_char first which will use the nls_date_format as the format mask."

    can you explain in detail?
    why shoudn't i use to_date to create index on datetime field?
  • 6. Re: creating index on datepart of datetime field
    883189 Newbie
    Currently Being Moderated
    how does this trunc function work?
    I wanted to create index on date part only for easy querying purposes.
    because in my table it consists of months of data and i need to query to get each month data separately based on datetime filed.
    hence i need to create and index on datetime filed...please help
    thanks !
  • 7. Re: creating index on datepart of datetime field
    Justin Cave Oracle ACE
    Currently Being Moderated
    880186 wrote:
    "You should never to_date a date since it requires an implicit to_char first which will use the nls_date_format as the format mask."

    can you explain in detail?
    why shoudn't i use to_date to create index on datetime field?
    TO_DATE takes a VARCHAR2 as a parameter and converts it to a DATE. If you use the TO_DATE function on a DATE, Oracle has to implicitly cast the DATE parameter to a VARCHAR2 in order to pass it to the TO_DATE function. This will use the session's NLS_DATE_FORMAT which may be different for every session meaning that a query that works for you could easily fail for the guy in the next cube. Then you take the VARCHAR2 that results from this implicit cast and convert it back to a DATE using the explicit format mask you specify. If the format mask you specified happens to differ from the session's current NLS_DATE_FORMAT, you'll get an error or you'll get a very different date than the one you were expecting.

    TRUNC, on the other hand, takes a DATE parameter and returns a DATE, it just allows you to truncate the date to midnight on the current day (the default), midnight on the first of the month (if you TRUNC(date_column,'MM'), midnight on the first of the year (if you TRUNC(date_column, 'YYYY') ), etc.

    It sounds like you may well want to index TRUNC( date_column, 'MM' ) to make it easier to find the rows for a particular month (assuming you have data from enough months that an index would be beneficial).

    Justin
  • 8. Re: creating index on datepart of datetime field
    883189 Newbie
    Currently Being Moderated
    thank u so much for the detail answer. i really appreciate ur patience
    I understood clearly.. But I need to create index on whole date...
    for example, if I want to query for one day...then i need to query as, select * from table where date=to_date('24-05-2009', dd-mm-yyyy)....something like that..
    then how?
    how to create index then? not particularly on month or date or year...i need whole date..
    please help..

    why cant i use to_char to create index on datetime field...it seems to be fine...do u have any idea on this?

    Edited by: 880186 on Sep 9, 2011 2:28 PM

    Edited by: 880186 on Sep 9, 2011 2:31 PM
  • 9. Re: creating index on datepart of datetime field
    Justin Cave Oracle ACE
    Currently Being Moderated
    If that's the form of the query you're writing, you'd just want to index the date column
    CREATE INDEX idx_date_colum
       ON table_name( date_column )
    But is that really what you want?
    SELECT *
      FROM table_name
     WHERE date_column = to_date( '24-05-2009', 'dd-mm-yyyy' )
    will return only those rows where DATE_COLUMN is midnight on May 24, 2009. If the data in DATE_COLUMN actually contains times other than midnight, I'm guessing that you would actually want your query to be
    SELECT *
      FROM table_name
     WHERE TRUNC(date_column) = to_date( '24-05-2009', 'dd-mm-yyyy' )
    in which case the index would need to be
    CREATE INDEX idx_date_colum
       ON table_name( trunc( date_column ) )
    Justin
  • 10. Re: creating index on datepart of datetime field
    883189 Newbie
    Currently Being Moderated
    Oh i am totally confused.
    I just ran the below querys to chk the difference between what you said.
    But i am getting the same results...how?
    my querys i ran are as follows:

    select to_char(date_column,'mm/dd/yyyy') from table;

    select trunc(date_column) from table;

    select to_date(date_column,'dd/mm/yyyy') from table;
  • 11. Re: creating index on datepart of datetime field
    sb92075 Guru
    Currently Being Moderated
    select sysdate from dual;
  • 12. Re: creating index on datepart of datetime field
    Justin Cave Oracle ACE
    Currently Being Moderated
    If your session's NLS_DATE_FORMAT happens to be MM/DD/YYYY, then those three statements will all appear to return the same results. But there are important differences
    select to_char(date_column,'mm/dd/yyyy') from table;
    explicitly converts the date to a string in the specified format. You're not relying on implicit conversion so you'll get the same results regardless of the session's NLS_DATE_FORMAT. This is perfectly reasonable if you want to return a string in the specified format regardless of the session's NLS_DATE_FORMAT (remember that different parts of the world consider dd/mm/yyyy the "natural" format so they may misinterpret your string)
    select trunc(date_column) from table;
    returns a date data type, not a string. When SQL*Plus displays the data, it converts the string to a date using the session's NLS_DATE_FORMAT. Different users will get different results but the results they get will be a string with their "natural" date format (or whatever date format they've indicated they prefer).
    select to_date(date_column,'dd/mm/yyyy') from table;
    is the only one of the three that you should always avoid. It implicitly converts DATE_COLUMN to a VARCHAR2 using the session's NLS_DATE_FORMAT. Then it calls TO_DATE to explicitly convert the string to a data. Then SQL*Plus converts the date to a string using the NLS_DATE_FORMAT. That's a lot of work but it can easily return "incorrect" data or return an error.

    If you set your NLS_DATE_FORMAT to mm/dd/yyyy, this query will return "incorrect" data, the month and day will be exchanged. If you use another NLS_DATE_FORMAT, you may get an error.

    Justin
  • 13. Re: creating index on datepart of datetime field
    708636 Explorer
    Currently Being Moderated
    Hello,

    Case 1:

    Create Functional INDEX.

    create index tmp on table1(trunc(datecolumn));
    Then all your queries should be like following:
    SELECT   stuff    FROM   table1
    where trunc(datecolumn) = trunc(input_date);
    
    SELECT   stuff    FROM   table1
    where trunc(datecolumn) >= :input_date1   and trunc(datecolumn) < :input_date2;
    Case 2:
    Create regular Index.
    create index tmp on table1(datecolumn);
    Here also, oracle uses Range Scan on Index while fetching records.
    Single Day queries:
    
    SELECT   stuff    
      FROM   table1
    WHERE datecolumn >= trunc(input_date)   and   datecolumn   < trunc(input_date)   +   1;
    
    
    Date Range
    SELECT   stuff    
       FROM   table1 
     WHERE   datecolumn  >= :input_date1   
         and   datecolumn < :input_date2;
  • 14. Re: creating index on datepart of datetime field
    John Spencer Oracle ACE
    Currently Being Moderated
    880186 wrote:
    Oh i am totally confused.
    I just ran the below querys to chk the difference between what you said.
    But i am getting the same results...how?
    my querys i ran are as follows:

    select to_char(date_column,'mm/dd/yyyy') from table;

    select trunc(date_column) from table;

    select to_date(date_column,'dd/mm/yyyy') from table;
    Clearly, the default date format in your session is one of dd/mm/yyyy or mm/dd/yyyy, and whatever date you are using for date_column converts correctly either way. Also, most front-end tools actually implicitly convert an Oracle date to a string in order to display it on screen. This is usually the nls_date_format set for your session. To see the difference, you need to change the format to display time as well, like this:
    SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
    
    Session altered.
    
    SQL> select sysdate, trunc(sysdate)
      2  from dual;
    
    SYSDATE              TRUNC(SYSDATE)
    -------------------- --------------------
    10-sep-2011 11:26:12 10-sep-2011 00:00:00
    See the difference?

    John

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points