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
Best Answer
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:
That happens in your case, and that is bad for 2 reasons:
A1.ID
column.To get around it, you need to do one of the following:
Create a function-based index on
A1.ID
column:CREATE INDEX people_idx5 ON people (TO_CHAR(id));
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.
Create a separate column
ID_CHAR
of typeVARCHAR2
and fill it withTO_CHAR(id)
. Index it and use instead ofID
in yourWHERE
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.