Non-negligible execution plan difference with Oracle when using jdbc Timestamp or Date

Question

I'm analysing Oracle execution plans and found an astonishing fact. Check out this query. The hint is just to display that I have an index and I'd expect Oracle to use it for range scans:

// execute_at is of type DATE.
PreparedStatement stmt = connection.prepareStatement(
    "SELECT /*+ index(my_table my_index) */ * " + 
    "FROM my_table " +
    "WHERE execute_at > ? AND execute_at < ?");

These two bindings result in entirely different behaviour (to exclude bind variable peeking issues, I actually enforced two hard-parses):

// 1. with timestamps
stmt.setTimestamp(1, start);
stmt.setTimestamp(2, end);

// 2. with dates
stmt.setDate(1, start);
stmt.setDate(2, end);

1) With timestamps, I get an INDEX FULL SCAN and thus a filter predicate

--------------------------------------------------------------
| Id  | Operation                    | Name                  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |
|*  1 |  FILTER                      |                       |
|   2 |   TABLE ACCESS BY INDEX ROWID| my_table              |
|*  3 |    INDEX FULL SCAN           | my_index              |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:1<:2)"
   3 - filter((INTERNAL_FUNCTION(""EXECUTE_AT"")>:1 AND 
               INTERNAL_FUNCTION(""EXECUTE_AT"")<:2))

2) With dates, I get the much better INDEX RANGE SCAN and an access predicate

--------------------------------------------------------------
| Id  | Operation                    | Name                  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |
|*  1 |  FILTER                      |                       |
|   2 |   TABLE ACCESS BY INDEX ROWID| my_table              |
|*  3 |    INDEX RANGE SCAN          | my_index              |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:1<:2)"
   3 - access(""EXECUTE_AT"">:1 AND ""EXECUTE_AT""<:2)

Now my example is just an example. The real query is much more complex, where it is essential to have RANGE SCANS or UNIQUE SCANS (depending on the predicate) rather than FULL SCANS.

Is there something I'm misunderstanding here? Can someone point me to the best solution/practice? Because in the Java world, I think that java.sql.Timestamp is much more suitable but most of our columns are of Oracle's DATE type. We're using Java 6 and Oracle 11g

Answer 1

While APC's answer already sufficiently explains why this is happening, the following blog posts are interesting in case you're trying to solve this problem with JPA and Hibernate:

• http://blog.jooq.org/2014/12/29/leaky-abstractions-or-how-to-bind-oracle-date-correctly-with-hibernate/

Or with JDBC or jOOQ:

• http://blog.jooq.org/2014/12/22/are-you-binding-your-oracle-dates-correctly-i-bet-you-arent/

In particular, a possible solution is to simply pass oracle.sql.DATE instead of any java.sql type to the PreparedStatement

Answer 2

So the thing is, Oracle timestamps and Oracle dates are two different datatypes. In order to compare a timestamp to a date Oracle has to run a conversion - that INTERNAL_FUNCTION(). The interesting design decision is that Oracle converts the table column rather than the passed value, which means the query no longer uses the index.

I have been able to reproduce your scenario in SQL*Plus, so it's not a problem with using java.sql.Timestamp. Casting the passed timestamps to dates does resolve the problem...

SQL> explain plan for
  2      select * from test1
  3      where d1 > cast(to_timestamp('01-MAY-2011 00:00:00.000', 'DD-MON-YYYY Hh24:MI:SS.FF') as date)
  4       and d2 > cast(to_timestamp('01-JUN-2011 23:59:59.999', 'DD-MON-YYYY Hh24:MI:SS.FF') as date)
  5  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------
Plan hash value: 1531258174

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    25 |   500 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1 |    25 |   500 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_I  |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------    
   2 - access("D1">CAST(TO_TIMESTAMP('01-MAY-2011 00:00:00.000','DD-MON-YYYY
              Hh24:MI:SS.FF') AS date) AND "D2">CAST(TO_TIMESTAMP('01-JUN-2011
              23:59:59.999','DD-MON-YYYY Hh24:MI:SS.FF') AS date) AND "D1" IS NOT NULL)
       filter("D2">CAST(TO_TIMESTAMP('01-JUN-2011 23:59:59.999','DD-MON-YYYY
              Hh24:MI:SS.FF') AS date))

18 rows selected.

SQL>

But I don't think that helps you any: it would be easier to just pass dates instead.

---

Interestingly, building a function-based index casting the date columns to timestamps doesn't help. The INTERNAL_FUNCTION() call isn't recognised as a CAST() and the index is ignored. Trying to build an index using INTERNAL_FUNCTION() hurls an ORA-00904.