title: Oracle query using 'like' on indexed number column, poor performance
body: <p>On Query 1 a full table scan is being performed even though the id is an indexed column. Query 2 achieves the same result but much faster. If Query 1 is run returning an indexed column then it returns quickly but if non-indexed columns are returned or the entire row is then the query takes longer. </p>
  
  <p>In Query 3 it runs fast but the column 'code' is a VARCHAR2(10) in stead of a NUMBER(12) and is indexed the same way as 'id'.</p>
  
  <p>Why does Query 1 not pick up that it should use the index? Is there something that should be changed to allow indexed number columns to perform quicker?</p>
  
  <p>[Query 1]</p>
  
  <pre><code>select a1.*
  from people a1
  where a1.id like '119%' 
  and rownum &lt; 5
  </code></pre>
  
  <p>Explain Plan<br>
  SELECT STATEMENT  ALL_ROWS<br>
  Cost: 67  Bytes: 2,592  Cardinality: 4<br>
      2 COUNT STOPKEY<br>
  &nbsp;&nbsp;&nbsp;&nbsp;1 TABLE ACCESS FULL TABLE people<br>
  &nbsp;&nbsp;&nbsp;&nbsp;  Cost: 67  Bytes: 3,240  Cardinality: 5  </p>
  
  <p>[Query 2]</p>
  
  <pre><code>select a1.*
  from people a1, people a2
  where a1.id = a2.id
  and a2.id like '119%' 
  and rownum &lt; 5
  </code></pre>
  
  <p>Explain Plan<br>
  SELECT STATEMENT  ALL_ROWS<br>
  Cost: 11  Bytes: 2,620  Cardinality: 4<br>
  5 COUNT STOPKEY<br>
  &nbsp;&nbsp;&nbsp;&nbsp;4 TABLE ACCESS BY INDEX ROWID TABLE people<br>
  &nbsp;&nbsp;&nbsp;&nbsp;Cost: 3  Bytes: 648  Cardinality: 1<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3 NESTED LOOPS<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Cost: 11  Bytes: 2,620  Cardinality: 4<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 INDEX FAST FULL SCAN INDEX people_IDX3<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Cost: 2  Bytes: 54,796  Cardinality: 7,828<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2 INDEX RANGE SCAN INDEX people_IDX3<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Cost: 2  Cardinality: 1  </p>
  
  <p>[Query 3]</p>
  
  <pre><code>select a1.*
  from people a1
  where a1.code like '119%' 
  and rownum &lt; 5
  </code></pre>
  
  <p>Explain Plan<br>
  SELECT STATEMENT  ALL_ROWS<br>
  Cost: 6  Bytes: 1,296  Cardinality: 2<br>
  &nbsp;&nbsp;&nbsp;3 COUNT STOPKEY<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2 TABLE ACCESS BY INDEX ROWID TABLE people<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Cost: 6  Bytes: 1,296  Cardinality: 2<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 INDEX RANGE SCAN INDEX people_IDX4<br>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Cost: 3  Cardinality: 2  </p>