Início / dba / Perguntas / 168694
Accepted
RBarryYoung
Asked: 2017-03-31 13:29:51 +0800 CST

Outer Join suprimindo o uso do índice?

  • 772

Eu tenho um programa cliente que está executando uma consulta em uma exibição que une uma tabela a outra externa. O desempenho é ruim e tenho tentado ajustá-lo adicionando o índice correto. A consulta em questão está apenas usando a segunda tabela, então eu tenho testado diretamente nessa tabela.

Encontrei (vários) índices que funcionaram bem para a consulta na tabela, mas quando mudei para usar o modo de exibição, eles pararam de usar quaisquer índices e apenas fizeram verificações completas em ambas as tabelas. Como essas tabelas são grandes (2-3 milhões de linhas cada), isso é muito lento.

Para simplesmente testar, alterei a consulta para ignorar o e apenas incorporar a junção externa na própria consulta. Isso reproduziu com sucesso o problema, mas deixou o mistério de por que a junção externa não usaria os índices.

Aqui está a tabela, com todos os índices que adicionei durante o teste:

  CREATE TABLE TEST_DATA 
   (ID NUMBER(11,0)  PRIMARY KEY, 
    FORMATTED_RESULT VARCHAR2(255 BYTE), 
    F_RESULT NUMBER, 
    IDNUM NUMBER(11,0), 
    IDNUM_DESCRIPTION VARCHAR2(128 BYTE), 
    LAB_NUMBER NUMBER(11,0), 
    SEQ_NUMBER NUMBER(11,0),
    ORDERNO NUMBER(11,0),
    SUPPL_FORMATTED_RESULT VARCHAR2(255 BYTE), 
    SUPPL_IDNUM NUMBER(11,0), 
    SUPPL_IDNUM_DESCRIPTION VARCHAR2(128 BYTE), 
    SUPPL_UNIT VARCHAR2(16 BYTE)
   ) ;

  CREATE UNIQUE INDEX TEST_LN_SQN_ORDER ON TEST_DATA (LAB_NUMBER, SEQ_NUMBER, ORDERNO) ;
  CREATE INDEX TEST_LN_SQN ON TEST_DATA (LAB_NUMBER, SEQ_NUMBER) ;
  CREATE INDEX TD_CUIDD_CUFR ON TEST_DATA (UPPER(COALESCE(SUPPL_IDNUM_DESCRIPTION,IDNUM_DESCRIPTION)), UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT))) ;
  CREATE INDEX TD_UFR_IDN ON TEST_DATA (UPPER(FORMATTED_RESULT), IDNUM) ;
  CREATE INDEX TD_UIDD_UFR ON TEST_DATA (UPPER(IDNUM_DESCRIPTION), UPPER(FORMATTED_RESULT)) ;
  CREATE INDEX TD_CUFR_CIDN_SN_LN ON TEST_DATA (UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT)), COALESCE(SUPPL_IDNUM,IDNUM), SEQ_NUMBER, LAB_NUMBER) ;
  CREATE INDEX TD_SN_LN_CUFR_CIDN ON TEST_DATA (SEQ_NUMBER, LAB_NUMBER, UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT)), COALESCE(SUPPL_IDNUM,IDNUM)) ;
  CREATE INDEX TD_CUFR_CIDN ON TEST_DATA (UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT)), COALESCE(SUPPL_IDNUM,IDNUM)) ;

Aqui está a outra tabela (aquela que realmente não usamos para esta consulta)

  CREATE TABLE REQUEST_INFO 
   (NUMBER(11,0) PRIMARY KEY, 
    CHARGE_CODE VARCHAR2(32 BYTE), 
    LAB_NUMBER NUMBER(11,0), 
    SEQ_NUMBER NUMBER(11,0)
   ) ;

  CREATE INDEX RI_LN_SN ON REQUEST_INFO (LAB_NUMBER, SEQ_NUMBER) ;
  CREATE INDEX RI_SN_LN ON REQUEST_INFO (SEQ_NUMBER, LAB_NUMBER) ;

Então, primeiro, aqui está a consulta diretamente na tabela única, que usa com sucesso um dos índices.

-- GOOD, Uses index : TD_CUFR_CIDN_SN_LN
select td.LAB_NUMBER 
from test_DATA td 
where UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT))='491(10)376'
  and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549 
;

Agora aqui está a consulta usando ambas as tabelas com uma junção interna . Isso também usa os índices e é executado rapidamente.

-- GOOD, Uses indexes : TD_CUFR_CIDN_SN_LN AND RI_SN_LN
select TD.LAB_NUMBER  
from REQUEST_INFO RI 
JOIN TEST_DATA TD ON  TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
  and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549

E aqui está a mesma consulta com um Left Outer Join, como está escrito na exibição. Isso NÃO usa nenhum dos índices e é executado muito lentamente.

-- BAD, does not use indexes
select TD.LAB_NUMBER 
from REQUEST_INFO RI 
LEFT JOIN TEST_DATA TD ON  TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
  and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549 
;

Agora, antes que alguém diga: essa consulta é logicamente idêntica à anterior. Isso ocorre porque a cláusula WHERE está filtrando as colunas da tabela externa (TD), que efetivamente/logicamente transforma uma junção externa em uma junção interna (é por isso que importa se as condições ocorrem na cláusula ON versus a cláusula WHERE).

Agora, só para aumentar a estranheza, decidi ver o que aconteceria se eu deixasse a coerção externa para interna mais explícita:

-- GOOD, Uses indexes : TD_CUFR_CIDN_SN_LN AND RI_SN_LN
select TD.LAB_NUMBER 
from REQUEST_INFO RI 
LEFT JOIN TEST_DATA TD ON  TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
  and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549 
and TD.LAB_NUMBER IS NOT NULL
;

Incrivelmente, isso funcionou!

Portanto, a questão aqui é: 1) POR QUE a Oracle não descobre isso sozinha?

E 2) Existe alguma configuração ou índice, etc. que eu possa criar que fará com que o Oracle descubra isso corretamente e use os índices?

Considerações adicionais:

  • A exibição é usada por uma variedade de outras consultas e clientes, portanto, não posso simplesmente alterá-la para uma junção interna para essa consulta.

  • O cliente está gerando a consulta, então é difícil/quase impossível alterar a consulta com condições de casos especiais peculiares como: " Use esta visualização para esses dados, a menos que você precise apenas dessas colunas desta tabela, use view ", ou " quando você precisar dessas colunas e apenas essas colunas desta tabela, adicione um 'IS NOT NULL' à cláusula WHERE "

Quaisquer sugestões ou insights serão bem-vindos.

ATUALIZAÇÃO: Acabei de testar no Oracle 11g também, obtive exatamente os mesmos resultados lá.

Por solicitação, aqui está a saída do Plano de Explicação, primeiro a versão boa, onde ele usa índices:

Rows      Plan                                       COST    Predicates
        3 SELECT STATEMENT                                 8 
        3  HASH JOIN                                       8 Access:TD.LAB_NUMBER=RI.LAB_NUMBER AND TD.SEQ_NUMBER=RI.SEQ_NUMBER
        3   NESTED LOOPS                                   8 
             STATISTICS COLLECTOR
        3     INDEX RANGE SCAN TD_CUFR_CIDN_SN_LN          4 Access:UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT))='491(10)376' AND COALESCE(SUPPL_IDNUM,IDNUM)=40549, Filter:TD.LAB_NUMBER IS NOT NULL
        1    INDEX RANGE SCAN RI_SN_LN                     2 Access:TD.SEQ_NUMBER=RI.SEQ_NUMBER AND TD.LAB_NUMBER=RI.LAB_NUMBER
        1   INDEX FAST FULL SCAN RI_SN_LN                  2

E agora a versão ruim:

Rows      Plan                                       COST    Predicates
 31939030 SELECT STATEMENT                            910972
           FILTER                                             Filter:UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT))='491(10)376' AND COALESCE(SUPPL_IDNUM,IDNUM)=40549
 31939030   HASH JOIN OUTER                           910972 Access:TD.LAB_NUMBER(+)=RI.LAB_NUMBER AND TD.SEQ_NUMBER(+)=RI.SEQ_NUMBER
  6213479    TABLE ACCESS FULL REQUEST_INFO            58276
 56276228    TABLE ACCESS FULL TEST_DATA              409612
oracle performance

2 respostas

  1. Best Answer

    Esta é principalmente uma resposta parcial à parte 1 com algumas especulações. Você e eu sabemos que a seguinte consulta:

    select TD.LAB_NUMBER 
from REQUEST_INFO RI 
LEFT JOIN TEST_DATA TD ON  TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
  and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549;

    É equivalente a esta consulta:

    select TD.LAB_NUMBER 
from REQUEST_INFO RI 
INNER JOIN TEST_DATA TD ON 
TD.LAB_NUMBER = RI.LAB_NUMBER 
AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
AND UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549;

    No entanto, isso não significa que a Oracle saiba que as duas consultas são equivalentes. A equivalência das duas consultas é necessária para que o Oracle possa usar o TD_CUFR_CIDN_SN_LNíndice. O que esperamos aqui é uma OUTER JOINconversão INNER JOIN. Eu não tive muita sorte em encontrar boas informações sobre isso , então vamos ver os planos de explicação:

    LAB_NUMBER

    Adicionar TD.LAB_NUMBER IS NOT NULLà WHEREcláusula é uma maneira muito direta de informar ao Oracle que OUTER JOINa INNER JOINconversão é possível. Podemos ver que isso ocorreu olhando para a linha destacada. Acho que praticamente qualquer coluna permitirá a conversão, embora escolher a coluna errada possa alterar os resultados da consulta.

    Se tentarmos um filtro um pouco mais complicado, como (TD.LAB_NUMBER IS NOT NULL OR TD.SEQ_NUMBER IS NOT NULL)então a conversão de junção não acontece:

    sem conversão de junção

    Podemos raciocinar que o OUTER JOINé realmente um INNER JOIN, mas o otimizador de consulta pode não ter sido programado para fazer isso. Na consulta original, você tem uma COALESCE()expressão que provavelmente é muito complexa para o otimizador de consulta aplicar a transformação de consulta.

    Aqui está um violino db para alguns dos exemplos.

    Para a segunda pergunta, não consigo pensar em uma maneira de contornar isso. Você pode tentar tirar vantagem da eliminação da mesa . Como você disse, essa consulta nem exige a REQUEST_INFOtabela. No entanto, existem algumas restrições:

    Atualmente, existem algumas limitações de eliminação de mesa:

    • Não há suporte para restrições de chave primária primária de várias colunas.

    • A referência à chave de junção em outro lugar na consulta impedirá a eliminação da tabela. Para uma junção interna, as chaves de junção em cada lado da junção são equivalentes, mas se a consulta contiver outras referências à chave de junção da tabela que poderiam ser eliminadas, isso impedirá a eliminação. Uma solução alternativa é reescrever a consulta para se referir à chave de junção da outra tabela (percebemos que isso nem sempre é possível).

    Talvez haja uma maneira de usar isso para esse problema, mas não consigo contornar as restrições.

    • 2

  2. Substitua a declaração Coalesce por uma ORdeclaração porque Adicionar uma função no lado esquerdo da cláusula Where não usará um índice, a menos que a função do lado esquerdo seja indexada usando um índice baseado em função, portanto, altere a consulta conforme abaixo. O índice Separar no SUPPL_FORMATTED_RESULTe FORMATTED_RESULTdeve ter uma upperfunção para a consulta abaixo usar o acesso ao índice.

    Nota: se houver um desvio nos dados e se o número de registros com os valores '491(10)376' e 40549 forem mais oracle irá pular o índice e usar uma varredura completa da tabela.

    select TD.LAB_NUMBER 
from REQUEST_INFO RI 
LEFT JOIN TEST_DATA TD ON  TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
where (UPPER(TD.SUPPL_FORMATTED_RESULT) ='491(10)376' or   
       UPPER(TD.FORMATTED_RESULT)='491(10)376')
  and  (TD.SUPPL_IDNUM =40549 or TD.IDNUM=40549);
    • -1

relate perguntas