ewong_at_mail.com (Ed Wong) wrote in message news:<a5ae1554.0107262110.6f8f83a8_at_posting.google.com>...
> When I use like operator, I found that search by 's%' is so much
> different from '%s'. Why is that and how to resolve this? Any help
> is appreciated(no CBO please). Thanks.
>
> SQL> select * from table_name where name like 'tran%';
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_NAME'
> 2 1 INDEX (RANGE SCAN) OF 'IN_SEQ_NAME' (NON-UNIQUE)
>
> SQL> select * from t where name like '%ion';
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 TABLE ACCESS (FULL) OF 'TABLE_NAME'
Your problem is caused by limitations of indexes. Index has sorted
values and ROWIDs, ROWID identifies a ROW, which contains value in
index column.
You can't start search by second or any other position.
Example from real word can be a dictionary. You can quickly search all
words beginning with 'S' (like SEARCH is) in dictionary, but not words
with 'E' (also SEARCH) on the second place.
--
_________________________________________
Dusan Bolek, Ing.
Oracle team leader
Received on Fri Jul 27 2001 - 05:22:56 CDT
