5 Replies Latest reply: Feb 28, 2013 9:26 AM by TheOtherGuy RSS

    timestamp to date conversion....

    TheOtherGuy
      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
          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
            >
            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
              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
                >
                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
                  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