Performance with Different JDBC Drivers Queries with Date and TimeStamp
Last updated on AUGUST 13, 2014
Applies to:JDBC - Version 220.127.116.11 to 10.2.0.5 [Release 8.1.7 to 10.2]
Information in this document applies to any platform.
A difference in behaviour is found between 9i and 10g JDBC drivers with respect to queries with bind variables, how they are parsed on the server, which affects whether the query will use an index scan or a full table scan.
With JDBC driver 18.104.22.168 :
- when date is set by setTimestamp(1,ldate, cal), it is observed an "INDEX RANGE SCAN" in the trace.
- when date is set by setTimestamp(1,ldate) we observe a "TABLE ACCESS FULL" in the trace.
With JDBC driver 10.2.0.1:
- a "TABLE ACCESS FULL" is always noticed in the trace file in spite of setTimestamp using a calendar or not.
A workaround consists of forcing the comparison to occur using the Oracle DATE type rather than the Oracle TIMESTAMP type: DATE_FIN = to_date(to_char(?)).
For information about Date and Timestamp mapping in 11g please refer to the following: JDBC 11g: Timestamp to Date Mapping (Doc ID 1097386.1)
Problem does not occur in JDBC 22.214.171.124 and prior versions.
Problem occurs in JDBC 126.96.36.199 and above.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms