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
