performance - 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

This is because TIMESTAMP datatype is more accurate than DATE so when you supply TIMESTAMP parameter value into DATE column condition, Oracle has to convert all DATE values into TIMESTAMP to make a comparison (this is the INTERNAL_FUNCTION usage above) and therefore index has to be full scanned.