Performance with Different JDBC Drivers Queries with Date and TimeStamp
(Doc ID 364118.1)
Last updated on FEBRUARY 14, 2019
Applies to:JDBC - Version 18.104.22.168 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 22.214.171.124 :
- 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 126.96.36.199 and prior versions.
Problem occurs in JDBC 188.8.131.52 and above.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document