Organizational Research By

Surprising Reserch Topic

non negligible execution plan difference with oracle when using jdbc timestamp o


non negligible execution plan difference with oracle when using jdbc timestamp o  using -'oracle,jdbc,timestamp,query-execution-plans'

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
    
asked Sep 24, 2015 by devkumargupta
0 votes
5 views



Related Hot Questions



Government Jobs Opening


...