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.

• Jack Douglas: please post your query that does an FTS - the LHS of the where must match the function exactly to use the index

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.

• Andy: No NLS issues, query is a LIKE (see updated question), and columns are nullable. The table is 80531 rows in the test environment, but I need to make sure this query will use the index in production to save on query cost. Row count in production could be in excess of 1M rows.
• Jack Douglas: I would be good to have your test data matching production data or your tests will be much less useful. You should at least copy the 1M+ rows for this table while you are investigating

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