Início / dba / Perguntas / 5059
Accepted
Andy
Asked: 2011-08-26 13:54:49 +0800 CST

FBI em função com string concatenada

  • 772

Eu tenho um índice instalado (desde os dias anteriores) sobre o qual estou curioso. Está em uma mesa de pessoa básica e a função é

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

Ao tentar pesquisar neste índice, acabo com uma varredura completa da tabela. Alguma ideia do porquê? E se isso estiver completamente quebrado como eu acho que é, você sugeriria um índice de coluna nessas quatro colunas?

EDITAR

Desculpe por não fornecer a consulta. Sim, a consulta é semelhante e as colunas são todas anuláveis. Então eu tenho

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

Alguma ideia? Obrigado pela resposta rápida.

oracle index

2 respostas

  1. Best Answer

    As colunas são anuláveis? a consulta é um LIKE? Existe um problema de NLS?

    eu esperaria um

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

    para usar uma varredura de intervalo de índice

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

    pode usar uma varredura completa rápida de índice ou uma varredura de tabela, dependendo se as colunas da tabela provavelmente seriam necessárias. Se achar que 1 em 5 linhas corresponderá e que para cada uma delas precisa de uma coluna que não esteja no índice, seria mais lento usar a pesquisa de índice+tabela do que uma varredura de tabela direta.

    Pode ser que a tabela seja muito pequena e não valha a pena usar o índice.

    • 5

  2. O CBO deve estar considerando o índice para consulta que você forneceu:

    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

    Neste ponto, eu estaria verificando tudo com cuidado - como o DDL para o índice

    • 2

relate perguntas