主页 / dba / 问题 / 5059
Accepted
Andy
Asked: 2011-08-26 13:54:49 +0800 CST

FBI 函数与连接字符串

  • 772

我有一个我很好奇的索引（从很久以前开始）。它在基本人员表上，功能是

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>'), '*', '%') || '%'

有什么想法吗？感谢你及时的答复。

oracle index

2 个回答

  1. Best Answer

    列可以为空吗？查询是 LIKE 吗？有 NLS 问题吗？

    我期待一个

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

    使用索引范围扫描

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

    可能会使用索引快速全扫描或表扫描，具体取决于是否可能需要表中的列。如果它认为 5 行中有 1 行会匹配，并且对于每一行它都需要一个不在索引中的列，那么使用索引+表查找会比直接表扫描慢。

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

    • 5

  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

    • 2

相关问题