title: Oracle Date index is slow. Query is 300 times faster without it
body: <p>I had an Oracle query as below that took 10 minutes or longer to run:</p>
  
  <pre><code>  select
        r.range_text as duration_range,
        nvl(count(c.call_duration),0) as calls,
        nvl(SUM(call_duration),0) as total_duration
        from
        call_duration_ranges r
        left join
        big_table c
        on c.call_duration BETWEEN r.range_lbound AND r.range_ubound
   and c.aaep_src = 'MAIN_SOURCE'
   and c.calltimestamp_local  &gt;= to_date('01-02-2014 00:00:00' ,'dd-MM-yyyy HH24:mi:ss')
   AND c.calltimestamp_local &lt;=  to_date('28-02-2014 23:59:59','dd-MM-yyyy HH24:mi:ss')
   and          c.destinationnumber LIKE substr( 'abc:1301@company.com:5060;user=phone',1,8) || '%'    
   group by
        r.range_text
   order by
        r.range_text
  </code></pre>
  
  <p>If I changed the date part of the query to:</p>
  
  <pre><code> (c.calltimestamp_local+0)  &gt;= to_date('01-02-2014 00:00:00' ,'dd-MM-yyyy HH24:mi:ss')
   (AND c.calltimestamp_local+0) &lt;=  to_date('28-02-2014 23:59:59','dd-MM-yyyy HH24:mi:ss')
  </code></pre>
  
  <p>It runs in 2 seconds. I did this based on another post to avoid using the date index. Seems counter intuitive though--the index slowing things down so much.</p>
  
  <p>Ran the explain plan and it seems identical between the new and updated query. Only difference is the the MERGE JOIN operation is 16,269 bytes in the old query and 1,218 bytes in the new query. Actually cardinality is higher in the old query as well. And I actually don't see an "INDEX" operation on the old or new query in the explain plan, just for the index on the destinationnumber field.</p>
  
  <p>So why is the index slowing down the query so much? What can I do to the index--don't think using the "+0" is the best solution going forward...</p>
  
  <p>Querying for two days of data, suppressing use of destinationnumber index:</p>
  
  <pre><code>0   SELECT STATEMENT            ALL_ROWS    329382  1218    14
  1   SORT    GROUP BY            329382  1218    14
  2   MERGE JOIN  OUTER           329381  1218    14
  3   SORT    JOIN            4   308 14
  4   TABLE ACCESS    FULL    CALL_DURATION_RANGES    ANALYZED    3   308 14
  5   FILTER                      
  6   SORT    JOIN            329377  65  1
  7   TABLE ACCESS    BY GLOBAL INDEX ROWID   BIG_TABLE   ANALYZED    329376  65  1
  8   INDEX   RANGE SCAN  IDX_CDR_CALLTIMESTAMP_LOCAL ANALYZED    1104        342104
  </code></pre>
  
  <p>Querying for 2 days using destinationnumber index:</p>
  
  <pre><code>0   SELECT STATEMENT            ALL_ROWS    11  1218    14
  1   SORT    GROUP BY            11  1218    14
  2   MERGE JOIN  OUTER           10  1218    14
  3   SORT    JOIN            4   308 14
  4   TABLE ACCESS    FULL    CALL_DURATION_RANGES    ANALYZED    3   308 14
  5   FILTER                      
  6   SORT    JOIN            6   65  1
  7   TABLE ACCESS    BY GLOBAL INDEX ROWID   BIG_TABLE   ANALYZED    5   65  1
  8   INDEX   RANGE SCAN  IDX_DESTINATIONNUMBER_PART  ANALYZED    4       4
  </code></pre>
  
  <p>Querying for one month, suppressing destinationnumber index--full scan:</p>
  
  <pre><code>0   SELECT STATEMENT            ALL_ROWS    824174  1218    14
  1   SORT    GROUP BY            824174  1218    14
  2   MERGE JOIN  OUTER           824173  1218    14
  3   SORT    JOIN            4   308 14
  4   TABLE ACCESS    FULL    CALL_DURATION_RANGES    ANALYZED    3   308 14
  5   FILTER                      
  6   SORT    JOIN            824169  65  1
  7   PARTITION RANGE ALL         824168  65  1
  8   TABLE ACCESS    FULL    BIG_TABLE   ANALYZED    824168  65  1
  </code></pre>