Oracle query using 'like' on indexed number column, poor performance
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
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:
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 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.
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).
Optimizer decided that it's faster to do a table scan, most probably due to low number of actual records.
Also, you should know that non-exact matching is always way worse than exact. If your where was "a1.id='123456'", it would most probably use index. But then again, even index takes two reads (first find a record in the index, then read the block from table) and for very small tables it could decide for table scan.
链接地址: http://www.djcxy.com/p/60720.html上一篇: oracle执行计划，试图理解