FBI on function with concatenated string

Question

I have an index in place (from days of old) that I'm curious about. It's on a basic person table and the function is

upper("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME")

When trying to search on this index, I wind up with a full table scan. Any idea why? And if this is just completely broken like I think it is, would you suggest a column index on these four columns?

EDIT

Sorry about not providing the query. Yes, the query is a like, and the columns are all nullable. So I have

select *
  from person p
  where UPPER("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME")
    like replace(upper('<search string here>'), '*', '%') || '%'

Any thoughts? Thanks for the quick answer.

Answer 1

Are the columns nullable ? is the query a LIKE ? Is there an NLS issue ?

I'd expect a

upper("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME") = :bind

to use a index range scan

upper("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME") LIKE :bind

may use a index fast full scan or a table scan depending on whether columns from the table were likely to be required. If it thinks 1 in 5 rows will match and that for each of those it needs a column not in the index, then it would be slower to use the index+table lookup than a straight table scan.

It could be the table is very small and it isn't worth using the index.

Answer 2

The CBO should be considering the index for query you have provided :

create table person( last_name varchar(100), 
                     first_name varchar(100), 
                     middle_name varchar(100), 
                     suffix_name varchar(100) );
insert into person(first_name, last_name) values ('Bob', 'Smith');
insert into person(first_name, last_name) values ('Bobby', 'Smith');
insert into person(first_name, last_name) values ('Bob', 'Smithson');
insert into person(first_name, last_name) values ('Bobby', 'Smithson');

insert into person(last_name, first_name)
select 'first name '||level, 'last name '||level from dual connect by level < 10000;

create index i_person 
  on person ((upper("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME")));

select *
  from person p
  where UPPER("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME") 
        like replace(upper('Smith*Bob*'), '*', '%') || '%';

 --explain plan shows: INDEX    I_PERSON    RANGE SCAN

At this point I would be double checking everything carefully - like the DDL for the index