Re: why 's%' use index '%s' doesn't?

If you frequently need that %s type of search to work in 8i, then you can do the following:

1. Create a function based index on substr(<column>,-#) where -# is how many characters from the end you wish to index.
2. Analyze your schema or at least the table.
3. Re-write the query using the substr function instead of LIKE.
4. Verify with explain plan and add hints if necessary. If hints don't work, analyze your schema and compute statistics..

Couple of warnings:

*) In 8.1.6 function based indexes have some issues. For large tables, they tend to go invalid, and they can go invalid after you analyze a table or a schema. We found the a composite index combined with a function is less buggy.

*) Function based indexes *will* be ignored unless you analyze your objects.

*) Don't overuse function based indexes, but they are preferable to Like in many instances.

*) If you run into too many problems with the function based indexes use a hidden "derived" column, which can be used in a normal index. If the table is updated online, then use triggers to store and update the derived data as needed.

Good Luck,
Chris Weiss

"Ed Wong" <ewong_at_mail.com> 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'
Received on Fri Jul 27 2001 - 15:03:12 CDT