Re: Function based index not used with like operator
Date: Mon, 1 Dec 2008 16:48:07 +0100
>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.
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
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
like 'ABC%' /* Not using index */
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
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. :-)
/Hans Received on Mon Dec 01 2008 - 09:48:07 CST