Re: Function based index not used with like operator

On Dec 1, 3:22 am, "Hassi" <ha..._at_nospam.com> wrote:
> Hi!
>
> I have two tables A and B where I in both tables have a function index
> (upper case) on a column. When I do a query like
>
> Select col1, col2 from A where upper(col_with_ucase_index) like 'ABC%'
>
> and I verify the execution plan it uses the upper case index on
> col_with_ucase_index when I run a query against table A.
> Now I have added a similar function index in table B but when I do a similar
> query on that table it does not use the index (full table scan).
>
> Select col1, col2 from B where upper(col_with_ucase_index) like 'ABC%'
>
> If I change the "like" operator to "=" it starts using the index again also
> for table B.
> Select col1, col2 from B where upper(col_with_ucase_index) = 'ABC%'
>
> I could understand that Oracle is not using an index if I had a wildcard
> character in the beginning of the string (like = '%ABC%') but I don't
> undestand why it does not use the index in my case.
>
> Table B contains approx 400000 records. I executed
> dbms_stats_gather_table_stats (with cascade=>TRUE) on both tables. I'm not a
> DBA and I guess I have missed something but I would appreciate if someone
> could shed some light on what I have missed or if Oracle in some cases
> (maybe depending on how the data looks like in the table) is deciding to do
> a full table scan instead of using an index scan. Table A is about the same
> size as table B.
>
> Oracle Database 10g Release 10.2.0.4.0
> Windows server 2003
>
> Regards
> /Hans

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.

David Fitzjarrell Received on Mon Dec 01 2008 - 08:35:13 CST