Oracle query using 'like' on indexed number column, poor performance

Question

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.

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'.

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?

[Query 1]

select a1.*
from people a1
where a1.id like '119%' 
and rownum < 5

Explain Plan
SELECT STATEMENT ALL_ROWS
Cost: 67 Bytes: 2,592 Cardinality: 4
2 COUNT STOPKEY
    1 TABLE ACCESS FULL TABLE people
     Cost: 67 Bytes: 3,240 Cardinality: 5

[Query 2]

select a1.*
from people a1, people a2
where a1.id = a2.id
and a2.id like '119%' 
and rownum < 5

Explain Plan
SELECT STATEMENT ALL_ROWS
Cost: 11 Bytes: 2,620 Cardinality: 4
5 COUNT STOPKEY
    4 TABLE ACCESS BY INDEX ROWID TABLE people
    Cost: 3 Bytes: 648 Cardinality: 1
        3 NESTED LOOPS
        Cost: 11 Bytes: 2,620 Cardinality: 4
            1 INDEX FAST FULL SCAN INDEX people_IDX3
            Cost: 2 Bytes: 54,796 Cardinality: 7,828
            2 INDEX RANGE SCAN INDEX people_IDX3
            Cost: 2 Cardinality: 1

[Query 3]

select a1.*
from people a1
where a1.code like '119%' 
and rownum < 5

Explain Plan
SELECT STATEMENT ALL_ROWS
Cost: 6 Bytes: 1,296 Cardinality: 2
   3 COUNT STOPKEY
      2 TABLE ACCESS BY INDEX ROWID TABLE people
      Cost: 6 Bytes: 1,296 Cardinality: 2
         1 INDEX RANGE SCAN INDEX people_IDX4
         Cost: 3 Cardinality: 2

Answer 1

LIKE pattern-matching condition expects to see character types as both left-side and right-side operands. When it encounters a NUMBER, it implicitly converts it to char. Your Query 1 is basically silently rewritten to this:

SELECT a1.*
  FROM people a1
 WHERE TO_CHAR(a1.id) LIKE '119%'
   AND ROWNUM < 5

That happens in your case, and that is bad for 2 reasons:

1. The conversion is executed for every row, which is slow;
2. Because of a function (though implicit) in a WHERE predicate, Oracle is unable to use the index on A1.ID column.

To get around it, you need to do one of the following:

1. Create a function-based index on A1.ID column:

   CREATE INDEX people_idx5 ON people (TO_CHAR(id));

2. If you need to match records on first 3 characters of ID column, create another column of type NUMBER containing just these 3 characters and use a plain = operator on it.

3. Create a separate column ID_CHAR of type VARCHAR2 and fill it with TO_CHAR(id). Index it and use instead of ID in your WHERE condition.

   Of course if you choose to create an additional column based on existing ID column, you need to keep those 2 synchronized.You can do that in batch as a single UPDATE, or in an ON-UPDATE trigger, or add that column to the appropriate INSERT and UPDATE statements in your code.

Answer 2

LIKE is a string function, so a numeric index can't be used as easily. In numeric index, you'll have 119,120,130,..,1191,1192,1193...,11921,11922... etc. That is all the rows starting with the '119' won't be in the same place, so the whole index has to be read (hence the FAST FULL SCAN). In a character based index they will be together (eg '119','1191','11911','120',...) so a better RANGE SCAN can be used.

If you were looking for id values in a particular range (eg 119000 to 119999) then specify that as the predicate (id between 119000 and 119999).