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

Last updated on AUGUST 13, 2014

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

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