kasia in a nutshell

My Oracle is a few years old, but here's what I'd guess:

The use of Timestamp is causing the JDBC layer (probably) to generate SQL with some combination of function calls that causes Oracle to skip the index. It used to have problems even with TO_DATE in the WHERE clause, but this could just be a brain-dead implementation in the JDBC driver.

There should be a way to capture the SQL that is actually being executed against the DB; can you see if you can grab that and compare the two versions?

Yah, we looked at the actual SQL being executed, the queries are identical :(

It might have something to do with the extra resolution provided by java.sql.Timestamp (nanoseconds) vs. java.sql.Date (milliseconds) vs Oracle DATE data type (seconds).

I know you mentioned that the SQL was identical, but you can try taking a look at some of Oracle's v$ performance views such as v$sql to find out what SQL has been executed. Once you get the SQL, you can run an explain plan on it even without the bind variables. If there really is only one query matching yours, it sounds like it could be a problem with the JDBC driver.

select
    ses.sid,
    ses.username,
    sql.sql_text,
    sql.hash_value,    
    sql.executions,
    sql.parse_calls,
    sql.disk_reads,
    sql.buffer_gets,
    sql.last_load_time
from
    v$sql sql,
    v$session ses
where sql.hash_value = ses.sql_hash_value
order by sql.last_load_time desc;

Nice site by the way.

Oracle has some bizarre behavior when it comes to Dates vs. Timestamps. You can only do time operations if you cast a date to a timestamp. And if you call rs.getObject() on what is supposed to be a timestamp, it returns oracle.sql.TIMESTAMP, which doesn't even implement java.sql.Timestamp. Urgh.

So what I think is that the date comparison feature is what is allowing the index to be used.

This URL has all the info:

http://www.databasejournal.com/features/oracle/article.php/2234501

We have also had sporadic problems with timestamp. The link Will pointed you to is a good one, Mr. Koopman has a way of explaining things missing in many "Expert" posts. Opinion: the optimizer as it exists today is terrific but extremely complex...it is what it is, and does exactly what it is supposed to do. 95% of the time, it makes the right decision. Sometimes, you have to tell it what to do with hints or change your code a bit.

One problem results from various versions of Oracle JDBC drivers around which behave differently when interpreting a DATE or TIMESTAMP. We find that the best practice is to have DATE really just use the date portion, and TIMESTAMP use both the date and time portion.