Edit Question - KnowledgeHub

Title Body

I'm analysing Oracle execution plans and found an astonishing fact. Check out this query. The hint is just to display that I have an index and I'd expect Oracle to use it for range scans: // execute_at is of type DATE. PreparedStatement stmt = connection.prepareStatement( "SELECT /*+ index(my_table my_index) */ * " + "FROM my_table " + "WHERE execute_at > ? AND execute_at < ?"); These two bindings result in entirely different behaviour (to exclude bind variable peeking issues, I actually enforced two hard-parses): // 1. with timestamps stmt.setTimestamp(1, start); stmt.setTimestamp(2, end); // 2. with dates stmt.setDate(1, start); stmt.setDate(2, end); 1) With timestamps, I get an INDEX FULL SCAN and thus a filter predicate -------------------------------------------------------------- | Id | Operation | Name | -------------------------------------------------------------- | 0 | SELECT STATEMENT | | |* 1 | FILTER | | | 2 | TABLE ACCESS BY INDEX ROWID| my_table | |* 3 | INDEX FULL SCAN | my_index | -------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(:1<:2)" 3 - filter((INTERNAL_FUNCTION(""EXECUTE_AT"")>:1 AND INTERNAL_FUNCTION(""EXECUTE_AT"")<:2)) 2) With dates, I get the much better INDEX RANGE SCAN and an access predicate -------------------------------------------------------------- | Id | Operation | Name | -------------------------------------------------------------- |

Tags (comma-separated) Cancel