StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>So the thing is, Oracle timestamps and Oracle dates are two different datatypes. In order to compare a timestamp to a date Oracle has to run a conversion - that INTERNAL_FUNCTION(). The interesting design decision is that Oracle converts the table column rather than the passed value, which means the query no longer uses the index. </p> <p>I have been able to reproduce your scenario in SQL*Plus, so it's not a problem with using <code>java.sql.Timestamp</code>. Casting the passed timestamps to dates does resolve the problem...</p> <pre><code>SQL> explain plan for 2 select * from test1 3 where d1 > cast(to_timestamp('01-MAY-2011 00:00:00.000', 'DD-MON-YYYY Hh24:MI:SS.FF') as date) 4 and d2 > cast(to_timestamp('01-JUN-2011 23:59:59.999', 'DD-MON-YYYY Hh24:MI:SS.FF') as date) 5 / Explained. SQL> select * from table(dbms_xplan.display) 2 / PLAN_TABLE_OUTPUT ----------------------------------------------------------- Plan hash value: 1531258174 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 25 | 500 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 25 | 500 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_I | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- 2 - access("D1">CAST(TO_TIMESTAMP('01-MAY-2011 00:00:00.000','DD-MON-YYYY Hh24:MI:SS.FF') AS date) AND "D2">CAST(TO_TIMESTAMP('01-JUN-2011 23:59:59.999','DD-MON-YYYY Hh24:MI:SS.FF') AS date) AND "D1" IS NOT NULL) filter("D2">CAST(TO_TIMESTAMP('01-JUN-2011 23:59:59.999','DD-MON-YYYY Hh24:MI:SS.FF') AS date)) 18 rows selected. SQL> </code></pre> <p>But I don't think that helps you any: it would be easier to just pass dates instead. </p> <hr> <p>Interestingly, building a function-based index casting the date columns to timestamps doesn't help. The <code>INTERNAL_FUNCTION()</code> call isn't recognised as a <code>CAST()</code> and the index is ignored. Trying to build an index using <code>INTERNAL_FUNCTION()</code> hurls an ORA-00904.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload