This discussion is archived
5 Replies Latest reply: Feb 28, 2013 7:26 AM by TheOtherGuy RSS

timestamp to date conversion....

TheOtherGuy Journeyer
Currently Being Moderated
OK, I have I guess interesting problem with timestamp to date conversion.

Before I go to details, perhaps I should mention the query is generated by hadoop sqoop and thus I cannot manually change the WHERE clause (it is dynamically created)

I get same behavior on 10g and 11g
  CREATE TABLE MY_TABLE
   (     MY_FIELD VARCHAR2(60),
         TRANSACTION_DATE DATE NOT NULL ENABLE
   ) 
  PARTITION BY RANGE (TRANSACTION_DATE) 
 (
 PARTITION "P201212"  VALUES LESS THAN (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), 
 PARTITION "P201301"  VALUES LESS THAN (TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), 
 PARTITION "P201302"  VALUES LESS THAN (TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
) ;
 

  CREATE INDEX MY_INDEX ON MY_TABLE (TRANSACTION_DATE) LOCAL
 ( PARTITION "P201212"  PCTFREE 10 INITRANS 2 MAXTRANS 255, 
   PARTITION "P201301"  PCTFREE 10 INITRANS 2 MAXTRANS 255, 
   PARTITION "P201302"  PCTFREE 10 INITRANS 2 MAXTRANS 255  ) ;
 
any reason why the query selects FULL scan over all partitions? It makes no sense I also tried to apply INDEX hint, but its full index scan over all partitions and makes things even worse
SELECT MY_FIELD
FROM MY_TABLE
WHERE TRANSACTION_DATE > TIMESTAMP '2013-02-22 19:41:00' AND
      TRANSACTION_DATE <= TO_TIMESTAMP('2013-02-22 20:01:23.0', 'YYYY-MM-DD HH24:MI:SS.FF')
vs this query I created manually selects index over single partition? Behavior I would like to see from query above

SELECT MY_FIELD
FROM MY_TABLE
WHERE TRANSACTION_DATE > TO_CHAR(  TIMESTAMP '2013-02-22 19:41:00', 'YYYY-MM-DD HH24:MI:SS.FF') AND
      TRANSACTION_DATE <= TO_CHAR( TO_TIMESTAMP('2013-02-22 20:01:23.0', 'YYYY-MM-DD HH24:MI:SS.FF'), 'YYYY-MM-DD HH24:MI:SS.FF')
Edited by: TheOtherGuy on Feb 27, 2013 1:24 PM

Edited by: TheOtherGuy on Feb 27, 2013 1:27 PM
  • 1. Re: timestamp to date conversion....
    Bjoern Rost Oracle ACE Director
    Currently Being Moderated
    I guess this has to do with the way oracle does implicit datatype conversion or casting. You can only compare two values if they share the same datatype, so when comparing a date (transaction_date) with a timestamp, it needs to actually convert one of these into the other datatype. apparently, oracle converts the date to timestamp (maybe because that way you don't loose precision) and then it really becomes ... to_timestamp(transaction_date) > timetamp ..... which cannot use the index. (you could create a function based index on to_timestamp(transaction_date))

    In the other example it converts the string to date (which btw relies on the correct setting of nls parameters) because <> do not really make sense when comparing strings. My advice is to always make sure you compare the same datatypes. If you cannot change the query, maybe you can change the table to use timestamp instead?

    Bjoern
  • 2. Re: timestamp to date conversion....
    rp0428 Guru
    Currently Being Moderated
    >
    any reason why the query selects FULL scan over all partitions?
    >
    We can't comment on a plan that we can't see. Post the plan.
  • 3. Re: timestamp to date conversion....
    TheOtherGuy Journeyer
    Currently Being Moderated
    the sample tables are empty, the behavior is the same for very large table with fresh statistics
    SELECT MY_FIELD
    FROM MY_TABLE
    WHERE TRANSACTION_DATE > TIMESTAMP '2013-02-22 19:41:00' AND
          TRANSACTION_DATE <= TO_TIMESTAMP('2013-02-22 20:01:23.0', 'YYYY-MM-DD HH24:MI:SS.FF')
    
    ------------------------------------------------------------------------------------------------                                                                                                                                                                                                             
    | Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                                             
    ------------------------------------------------------------------------------------------------                                                                                                                                                                                                             
    |   0 | SELECT STATEMENT    |          |     1 |    41 |     2   (0)| 00:00:01 |       |       |                                                                                                                                                                                                             
    |   1 |  PARTITION RANGE ALL|          |     1 |    41 |     2   (0)| 00:00:01 |     1 |     3 |                                                                                                                                                                                                             
    |*  2 |   TABLE ACCESS FULL | MY_TABLE |     1 |    41 |     2   (0)| 00:00:01 |     1 |     3 |                                                                                                                                                                                                             
    ------------------------------------------------------------------------------------------------                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                                                                 
    Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
    ---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                 
    2 - filter(INTERNAL_FUNCTION("TRANSACTION_DATE")>TIMESTAMP' 2013-02-22                                                                                                                                                                                                                                       
    19:41:00.000000000' AND INTERNAL_FUNCTION("TRANSACTION_DATE")<=TIMESTAMP' 2013-02-22                                                                                                                                                                                                                         
    20:01:23.000000000')                                                                                                                                                                                                                                                                                         
    SELECT MY_FIELD
    FROM MY_TABLE
    WHERE TRANSACTION_DATE > TO_CHAR(  TIMESTAMP '2013-02-22 19:41:00', 'YYYY-MM-DD HH24:MI:SS.FF') AND
          TRANSACTION_DATE <= TO_CHAR( TO_TIMESTAMP('2013-02-22 20:01:23.0', 'YYYY-MM-DD HH24:MI:SS.FF'), 'YYYY-MM-DD HH24:MI:SS.FF')
           
    ----------------------------------------------------------------------------------------------------------------                                                                                                                                                                                             
    | Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                             
    ----------------------------------------------------------------------------------------------------------------                                                                                                                                                                                             
    |   0 | SELECT STATEMENT                    |          |     1 |    41 |     0   (0)| 00:00:01 |       |       |                                                                                                                                                                                             
    |*  1 |  FILTER                             |          |       |       |            |          |       |       |                                                                                                                                                                                             
    |   2 |   PARTITION RANGE ITERATOR          |          |     1 |    41 |     0   (0)| 00:00:01 |   KEY |   KEY |                                                                                                                                                                                             
    |   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| MY_TABLE |     1 |    41 |     0   (0)| 00:00:01 |   KEY |   KEY |                                                                                                                                                                                             
    |*  4 |     INDEX RANGE SCAN                | MY_INDEX |     1 |       |     0   (0)| 00:00:01 |   KEY |   KEY |                                                                                                                                                                                             
    ----------------------------------------------------------------------------------------------------------------                                                                                                                                                                                             
                                                                                                                                                                                                                                                                                                                 
    Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
    ---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                 
    1 - filter(TO_DATE('2013-02-22 19:41:00.000000000')<TO_DATE('2013-02-22 20:01:23.000000000'))                                                                                                                                                                                                                
    4 - access("TRANSACTION_DATE">'2013-02-22 19:41:00.000000000' AND "TRANSACTION_DATE"<='2013-02-22                                                                                                                                                                                                            
    20:01:23.000000000')   
  • 4. Re: timestamp to date conversion....
    rp0428 Guru
    Currently Being Moderated
    >
    2 - filter(INTERNAL_FUNCTION("TRANSACTION_DATE")>TIMESTAMP' 2013-02-22
    19:41:00.000000000' AND INTERNAL_FUNCTION("TRANSACTION_DATE")<=TIMESTAMP' 2013-02-22
    20:01:23.000000000')
    >
    As Bjoern surmised that 'INTERNAL_FUNCTION' is Oracle's implicit conversion and the use of a function like that precludes the use of the indexes.

    You can't alter the query but you could alter the datatype to TIMESTAMP or add a virtual column that is a TIMESTAMP to get around it.
  • 5. Re: timestamp to date conversion....
    TheOtherGuy Journeyer
    Currently Being Moderated
    Thank you for your responses!

    Perhaps unrelated to this forum, It turns out there is a bug in sqoop, it should use to_date if the field is date type. Sqoop bug 906 is open https://issues.apache.org/jira/browse/SQOOP-906

Legend

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