Why is Oracle so slow when I pass a java.sql.Timestamp for a DATE column?

Question

I have a table with a DATE column with time (as usual in Oracle since there isn't a TIME type). When I query that column from JDBC, I have two options:

• Manually convert the values with Oracle's to_date()
• Use a java.sql.Timestamp

Both approaches work and have exclusive areas of hideousness. My problem is when I'm SELECTing data. Here are two sample queries:

select *
from TABLE
where TS between {ts '2009-12-08 00:00:00.000'} and {ts '2009-12-09 00:00:00.000'}

select *
from TABLE
where TS between trunc({ts '2009-12-08 00:00:00.000'}) and trunc({ts '2009-12-09 00:00:00.000'})

Both queries work, return the same results and produce the exact same output in EXPLAIN PLAN. This right indexes are used.

Only query one runs 15 minutes while the second query takes 0.031s. Why is that? Is there a central place to fix this or do I have to check all my queries for this column and make utterly sure that the trunc() is in there? How do I fix this issue when I need to select down to a certain second?

[EDIT] The table is partitioned and I'm on Oracle 10.2.0.

Answer 1

I don't understand what {ts '2009-12-08 00:00:00.000'} actually mean, since this isn't Oracle SQL as far as I know. Can you show exactly what the query is you're running?

One possible problem is that you're specifying your range with milliseconds. Oracle's DATE type only goes down to seconds. (Use TIMESTAMP type if you need to store fractions of seconds). But what might be happening is that in the first query, Oracle is converting each DATE value to a TIMESTAMP in order to do the comparison to your specified TIMESTAMP. In the second case, it knows TRUNC() will effectively round your value to something that can be expressed as a DATE, so no conversion is needed.

If you want to avoid such implicit conversions, make sure you're always comparing like with like. eg

select * 
from my_table t
where t.ts between to_date('2009-12-08','YYYY-MM-DD') and to_date('2009-12-09','YYYY-MM-DD')

Answer 2

Here is Oracle's FAQ regarding the DATE vs. TIMESTAMP problem within JDBC:

http://www.oracle.com/technology/tech/java/sqlj%5Fjdbc/htdocs/jdbc%5Ffaq.html#08%5F01

They offer various options to prevent the truncating of time information.

Answer 3

I had this problem on a project a while ago and setting the connection property oracle.jdbc.V8Compatible=true fixed the problem.

Dougman's link tells you how to set it:

You set the connection property by adding it to the java.util.Properties object passed to DriverManager.getConnection or to OracleDataSource.setConnectionProperties. You set the system property by including a -D option in your java command line.

java -Doracle.jdbc.V8Compatible="true" MyApp

Note for 11g and this property is apparently not used.

From http://forums.oracle.com/forums/thread.jspa?messageID=1659839 :

One additional note for those who are using the 11gR1 (and on) JDBC thin driver: the V8Compatible connection property no longer exist, so you can't rely on that to send your java.sql.Timestamp as a SQLDATE. What you can do however is call:

setObject(i, aTimestamp, java.sql.Types.DATE) sends data as SQLDATE
setObject(i, aDate) sends data as SQLDATE
setDate(i, aDate) sends data as SQLDATE
setDATE(i, aDATE) (non standard) sends data as SQLDATE

setObject(i, aTimestamp) sends data as SQLTIMESTAMP
setTimestamp(i, aTimestamp) sends data as SQLTIMESTAMP
setObject(i, aTimestamp) sends data as SQLTIMESTAMP
setTIMESTAMP(i, aTIMESTAMP) (non standard) sends data as SQLTIMESTAMP