title: How to optimize an Oracle query that has to_char in where clause for date
body: <p>I have a table that contains about <code>49403459</code> records.</p>
  
  <p>I want to query the table on a date range. say <code>04/10/2010</code> to <code>04/10/2010</code>.  However, the dates are stored in the table as format <code>10-APR-10 10.15.06.000000 AM</code> (time stamp).</p>
  
  <p>As a result when I do</p>
  
  <pre><code>SELECT bunch,of,stuff,create_date
  FROM myTable
  WHERE TO_CHAR (create_date,'MM/DD/YYYY)' &gt;= '04/10/2010'
  AND TO_CHAR (create_date, 'MM/DD/YYYY' &lt;= '04/10/2010'
  </code></pre>
  
  <p>I get <code>529</code> rows but in <code>255.59</code> seconds! Which is because I guess I am doing TO_CHAR on EACH record. </p>
  
  <p>However, when I do</p>
  
  <pre><code>SELECT bunch,of,stuff,create_date
  FROM myTable
  WHERE create_date &gt;= to_date('04/10/2010','MM/DD/YYYY')
  AND create_date &lt;= to_date('04/10/2010','MM/DD/YYYY')
  </code></pre>
  
  <p>then I get <code>0</code> results in <code>0.14</code> seconds.  </p>
  
  <p>How can I make this query fast and still get valid (<code>529</code>) results?</p>
  
  <p>At this point I can not change indexes. Right now I think index is created on <code>create_date</code> column.</p>
  
  <p>How can I convert the two date ranges so that first date range gets converted to time stamp with all 0's and the second one gets converted to time stamp that is the last time stamp of the date. If that makes sense...?</p>
  
  <p>The following where clause fetches no results either:</p>
  
  <pre><code>WHERE            
  create_date &gt;= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
  AND
  create_date &lt;= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
  </code></pre>