TKPROF shows SELECT... LIKE '%ABC' uses INDEX RANGE SCAN - is that possible???

From: Jan <janik_at_pobox.sk>
Date: 1 Oct 2004 03:08:14 -0700
Message-ID: <81511301.0410010208.6327d885@posting.google.com>

... oracle 9.2.0.1

Consider this example:

---

CREATE TABLE t AS SELECT rownum id, to_char(rownum) name FROM ALL_OBJECTS; CREATE INDEX t_idx ON t (name);
ANALYZE TABLE t COMPUTE STATISTICS;

VARIABLE x VARCHAR2(10);
ALTER SESSION SET EVENTS='10046 trace name context forever, level 12';

• Patern is 'XX%' then it could use an index EXEC :x:='10%'; SELECT * FROM t a1 WHERE name LIKE :x;
• Patern is '%XX' then it CANNOT NOT use an index range scan EXEC :x:='%10'; SELECT * FROM t a1 WHERE name LIKE :x;

EXIT;

---

Then, the tkprof output is saying:

select *
from
 t a1 where name like :x

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch       12      0.06       0.06          0        477          0         136
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.06       0.06          0        477          0         136

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 46

Rows Row Source Operation

-------  ---------------------------------------------------

    111 TABLE ACCESS BY INDEX ROWID T
    111 INDEX RANGE SCAN T_IDX (object id 39191)

How it is possible that Oracle was aible to response to the 2-nd question SELECT * FROM t a1 WHERE name LIKE '%10'

by using INDEX RANGE SCAN ?

Jan Received on Fri Oct 01 2004 - 05:08:14 CDT