It may be more that tkprof doesn't know what :x is.
What does the explain plan in the raw trace show ?
Ken
"Jan" <janik_at_pobox.sk> wrote in message
news:81511301.0410010208.6327d885_at_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 Sat Oct 02 2004 - 10:23:53 CDT
