Function based index not used with like operator

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 Received on Mon Dec 01 2008 - 03:22:24 CST