联调局关于串连字串的函数

Question

我有一个索引(从过去的日子起)，我很好奇。它在一个基本的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('<search string here>'), '*', '%') || '%'

有什么想法吗？谢谢你的快速回答。

Answer 1

列是空的吗？查询是否类似？有NLS问题吗？

我希望

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

使用索引范围扫描

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

可以使用索引快速全扫描或表扫描，这取决于表中的列是否可能是必需的。如果它认为每5行中就有1行与之匹配，并且每个行都需要一个列而不是索引中的列，那么使用index+table查找要比使用直表扫描慢。

可能是表很小，不值得使用索引。

Answer 2

CBO应该考虑您提供的查询索引：

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

此时，我将仔细检查所有内容，比如索引的DDL。