title: Why won't Oracle use my index unless I tell it to?
body: <p>I have an index:</p>
  
  <pre><code>CREATE INDEX BLAH ON EMPLOYEE(SUBSTR(TO_CHAR(EMPSHIRTNO), 1, 4));
  </code></pre>
  
  <p>and an SQL STATEMENT:</p>
  
  <pre><code>SELECT COUNT(*) 
    FROM (SELECT COUNT(*) 
            FROM EMPLOYEE 
           GROUP BY SUBSTR(TO_CHAR(EMPSHIRTNO), 1, 4) 
          HAVING COUNT(*) &gt; 100);
  </code></pre>
  
  <p>but it keeps doing a full table scan instead of using the index unless I add a hint.</p>
  
  <p>EMPSHIRTNO is not the primary key, EMPNO is (which isn't used here).</p>
  
  <h3>Complex query</h3>
  
  <pre><code>EXPLAIN PLAN FOR SELECT COUNT(*) FROM (SELECT COUNT(*) FROM EMPLOYEE
                                          GROUP BY SUBSTR(TO_CHAR(EMPSHIRTNO), 1, 4)
                                         HAVING COUNT(*) &gt; 100);
  
  PLAN_TABLE_OUTPUT
  --------------------------------------------------------------------------------
  Plan hash value: 1712471557
  ----------------------------------------------------------------------------------
  | Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
  ----------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT      |          |     1 |       |    24   (9)| 00:00:01 |
  |   1 |  SORT AGGREGATE       |          |     1 |       |            |          |
  |   2 |   VIEW                |          |   497 |       |    24   (9)| 00:00:01 |
  |*  3 |    FILTER             |          |       |       |            |          |
  ----------------------------------------------------------------------------------
  |   4 |     HASH GROUP BY     |          |   497 |  2485 |    24   (9)| 00:00:01 |
  |   5 |      TABLE ACCESS FULL| EMPLOYEE |  9998 | 49990 |    22   (0)| 00:00:01||
  ----------------------------------------------------------------------------------
  Predicate Information (identified by operation id):
  ---------------------------------------------------                             
     3 - filter(COUNT(*)&gt;100)                                                     
  
  17 rows selected.
  
  ANALYZE INDEX BLAH VALIDATE STRUCTURE;
  
  SELECT BTREE_SPACE, USED_SPACE FROM INDEX_STATS;
  
  BTREE_SPACE USED_SPACE
  ----------- ----------
       176032     150274
  </code></pre>
  
  <h3>Simple query:</h3>
  
  <pre><code>EXPLAIN PLAN FOR SELECT * FROM EMPLOYEE;
  
  PLAN_TABLE_OUTPUT                                                               
  --------------------------------------------------------------------------------
  Plan hash value: 2913724801                                                     
  
  ------------------------------------------------------------------------------  
  | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
  ------------------------------------------------------------------------------  
  |   0 | SELECT STATEMENT  |          |  9998 |   439K|    23   (5)| 00:00:01 |  
  |   1 |  TABLE ACCESS FULL| EMPLOYEE |  9998 |   439K|    23   (5)| 00:00:01 |  
  ------------------------------------------------------------------------------  
  
  8 rows selected.
  </code></pre>
  
  <p>Maybe it is because the NOT NULL constraint is enforced via a CHECK constraint rather than being defined originally in the table creation statement? It will use the index when I do:</p>
  
  <pre><code>SELECT * FROM EMPLOYEE WHERE SUBSTR(TO_CHAR(EMPSHIRTNO), 1, 4) = '1234';
  </code></pre>
  
  <p>For those suggesting that it needs to read all of the rows anyway (which I don't think it does as it is counting), the index is not used on this either:</p>
  
  <pre><code>SELECT SUBSTR(TO_CHAR(EMPSHIRTNO), 1, 4) FROM EMPLOYEE;
  </code></pre>
  
  <p>In fact, putting an index on EMPSHIRTNO and performing SELECT EMPSHIRTNO FROM EMPLOYEE; does not use the index either. I should point out that EMPSHIRTNO is not unique, there are duplicates in the table.</p>