title: Why is Oracle so slow when I pass a java.sql.Timestamp for a DATE column?
body: <p>I have a table with a <code>DATE</code> column with time (as usual in Oracle since there isn't a <code>TIME</code> type). When I query that column from JDBC, I have two options:</p>
  
  <ul>
  <li>Manually convert the values with Oracle's <code>to_date()</code></li>
  <li>Use a <code>java.sql.Timestamp</code></li>
  </ul>
  
  <p>Both approaches work and have exclusive areas of hideousness. My problem is when I'm <code>SELECT</code>ing data. Here are two sample queries:</p>
  
  <pre><code>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'})
  </code></pre>
  
  <p>Both queries work, return the same results and produce the exact same output in <code>EXPLAIN PLAN</code>. This right indexes are used.</p>
  
  <p>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 <code>trunc()</code> is in there? How do I fix this issue when I need to select down to a certain second?</p>
  
  <p>[EDIT] The table is partitioned and I'm on Oracle 10.2.0.</p>