My Oracle Support Banner

Performance with Different JDBC Drivers Queries with Date and TimeStamp (Doc ID 364118.1)

Last updated on FEBRUARY 14, 2019

Applies to:

JDBC - Version 8.1.7.0 to 10.2.0.5 [Release 8.1.7 to 10.2]
Information in this document applies to any platform.

Symptoms

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 9.2.0.4 :
- 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(?)).

This note was written to explain an issue related to the JDBC 9.2 and 10g drivers and does not apply to JDBC drivers 11g and up.
For information about Date and Timestamp mapping in 11g please refer to the following:  JDBC 11g: Timestamp to Date Mapping (Doc ID 1097386.1)

 

Changes

Problem does not occur in JDBC 9.2.0.4 and prior versions.

Problem occurs in JDBC 9.2.0.5 and above.

Cause

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
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.