Re: Function based index not used with like operator

From: Hassi <hassi_at_nospam.com>
Date: Mon, 1 Dec 2008 16:48:07 +0100
Message-ID: <gh1102$g5g$1@aioe.org>

>Please provide DDL, sample data and your query plans. Simply because
>both tables contain approximately the same number of rows doesn't mean
>they contain the same data in terms of key distribution.

Hi David!

You are right about the data in my table B is more "uniform" in the column where I have the upper case index. When I say uniform they are pretty similar like
1000001
1000002
1000003 and so on (no records have the exact same value)

I discovered right now that Oracle is sometimes using the index and sometimes not depending on the number of records where the column they search for is starting with the characters the user search for. It could be that using
 like 'ABC%' /* Not using index */
but
like 'ABCD%' /* Using index now when a forth character is added*/

When I did a query like (just to see how many rows have the same three starting characters)
Select substr(col_with_ucase_index,1,3), count(*) from B group by substr(col_with_ucase_index,1,3) order by 2 desc

it only returns 66 rows so I guess that is my problem. If I have two few characters in my search criteria Oracle decides to do a full table scan instead of using the index (and then of course on top of that we have an order by....:-)).

Not sure if there are other index types that might help in this situation (standard edition) when you have a "low" number of discrete values if you only consider the first 1-3 characters of the column value?

I guess the users must learn to type in more characters into the search dialog to receive a fast response. :-)

Regards
/Hans Received on Mon Dec 01 2008 - 09:48:07 CST

Original text of this message