Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
please post your query that does an FTS - the LHS of the where must match the function exactly to use the index –  Jack Douglas Aug 25 '11 at 22:07
add comment

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
    
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. –  Andy Aug 26 '11 at 11:18
1  
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 –  Jack Douglas Aug 26 '11 at 12:44
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.