title: Optimize Oracle Between Date Statement
body: <p>I got an oracle SQL query that selects entries of the current day like so:</p>
  
  <pre><code>SELECT   [fields] 
  FROM     MY_TABLE T 
  WHERE    T.EVT_END BETWEEN TRUNC(SYSDATE) 
                         AND TRUNC(SYSDATE) + 86399/86400
    AND    T.TYPE = 123
  </code></pre>
  
  <p>Whereas the <code>EVT_END</code> field is of type <code>DATE</code> and <code>T.TYPE</code> is a <code>NUMBER(15,0)</code>.</p>
  
  <p>Im sure with increasing size of the table data (and ongoing time), the date constraint will decrease the result set by a much larger factor than the type constraint. (Since there are a very limited number of types)</p>
  
  <p>So the basic question arising is, what's the best index to choose to make the selection on the current date faster. I especially wonder what the advantages and disadvantages of a functional index on <code>TRUNC(T.EVT_END)</code> to a normal index on <code>T.EVT_END</code> would be. When using a functional index the query would look something like that:</p>
  
  <pre><code>SELECT   [fields] 
  FROM     MY_TABLE T 
  WHERE    TRUNC(T.EVT_END) = TRUNC(SYSDATE) 
    AND    T.TYPE = 123
  </code></pre>
  
  <p>Because other queries use the mentioned date constraints without the additional type selection (or maybe with some other fields), multicolumn indexes wouldn't help me a lot.</p>
  
  <p>Thanks, I'd appreciate your hints.</p>