title: Function-based Index using Substr and Instr
body: <p>I have created a query doing this in <strong>ORACLE</strong>:</p>
  
  <pre><code>SELECT SUBSTR(title,1,INSTR(title,' ',1,1)) AS first_word, COUNT(*) AS word_count 
  FROM FILM 
  GROUP BY SUBSTR(title,1,INSTR(title,' ',1,1)) 
  HAVING COUNT(*) &gt;= 20;    
  </code></pre>
  
  <p>Results after running: 
  <code>539 rows selected. Elapsed: 00:00:00.22</code></p>
  
  <p>I need to improve the performance of this and created a <code>function-based index</code> as so:</p>
  
  <pre><code>CREATE INDEX INDX_FIRSTWRD ON FILM(SUBSTR(title,1,INSTR(title,' ',1,1)));
  </code></pre>
  
  <p>After running the same query at the top of this post, I still get the same performance:
  <code>539 rows selected. Elapsed: 00:00:00.22</code></p>
  
  <p>Is the index not being applied or overwritten or am I doing something wrong?</p>
  
  <p>Thanks for any help you could provide. :)</p>
  
  <p>EDIT:</p>
  
  <pre><code>Execution Plan:
  ----------------------------------------------------------
  Plan hash value: 2033354507
  
  ----------------------------------------------------------------------------
  | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  ----------------------------------------------------------------------------
  |   0 | SELECT STATEMENT    |      | 20000 |  2968K|   138   (2)| 00:00:02 |
  |*  1 |  FILTER             |      |       |       |            |          |
  |   2 |   HASH GROUP BY     |      | 20000 |  2968K|   138   (2)| 00:00:02 |
  |   3 |    TABLE ACCESS FULL| FILM | 20000 |  2968K|   136   (0)| 00:00:02 |
  ----------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
  1 - filter(COUNT(*)&gt;=20)
  
  
  Statistics
  ----------------------------------------------------------
        0  recursive calls
        0  db block gets
      471  consistent gets
        0  physical reads
        0  redo size
    14030  bytes sent via SQL*Net to client
      908  bytes received via SQL*Net from client
       37  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
      539  rows processed
  </code></pre>