Menu de navegação

[isacjopParticipante]

Galera, preciso otimizar a seguinte consullta:

SEL ECT *
FROM PESSOAS_FISICAS
WHERE (((NU_FONETICO_PF = 351689588279)
AND ((DT_NASCIMENTO = to_date('28/09/86','dd/mm/yy'))
OR (to_date('28/09/86','dd/mm/yy') IS NULL)))
AND (((NU_FONETICO_MAE = 64061390693)
OR (64061390693 IS NULL))
OR (NU_FONETICO_MAE IS NULL)))
Ocore que da forma que o sql está estruturado o oracle não consegue utilizar o índice correto, que consiste na junção das três colunas(NU_FONETICO_PF, DT_NASCIMENTO, NU_FONETICO_MAE). Quero saber como reestruturar a consulta para que o oracle consiga utilizar esse índice da forma menos custosa possível.
[]’s

[leandrolbsParticipante]

viu o plano de execução desta consulta? viu o custo? coloca o HINT para usar o INDEX forçado.. e veja o plano….

Hint… http://www.adp-gmbh.ch/ora/sql/hints/index.html

pode ser statistics por não estar usando…

[rmanParticipante]

@isacjop

Neste caso, o índice não é utilizado devido o uso da função to_date. O que pode ser feito é criar o índice basedo em função.

Exemplos:


CREATE INDEX IDX_NASCIMENTO ON PESSOAS_FISICAS (TO_DATE(DT_NASCIMENTO,'dd/mm/yy'));



CREATE INDEX IDX_COMPOSTO ON PESSOAS_FISICAS (NU_FONETICO,TO_DATE(DT_NASCIMENTO,'dd/mm/yy'),NU_FONETICO_MAE);


[isacjopParticipante]

Na verdade esse é um caso de exemplo. A consulta original é via java e esse to_data não existe. Esse exemplo, com o to_date, foi para testar o custo da query no sql developer.

[]’s

[rmanParticipante]

@isacjop

Poste por favor as seguintes informações:
1- Consulta exata que o sistema gera para o Oracle
2- Definição do índice em questão.
3- Desc da tabela.

[isacjopParticipante]

A consulta gerada é a seguinte:

SEL ECT ID_PESSOA_FISICA, CS_NACIONALIDADE, IN_NIT_FAIXA_VALIDA, ID_PAIS, CS_GRAU_INSTRUCAO, IN_MORTE_PRESUMIDA, DT_COMP_OBITO_BENEFICIO, NU_UF_CADASTRAMENTO, NU_VERSAO, NM_PESSOA_FISICA, DT_INIC_ADMINISTRADOR, CS_INDIGENA, NU_FONETICO_PF, ID_FONTE_PF, IN_PAI_IGNORADO, DT_FE_DE_VIDA, DT_COMP_OBITO_GFIP, IN_INCONSISTENTE, ID_PESSOA_FISICA_DV, CS_ADMINISTRADOR, DT_NASCIMENTO, NU_FONETICO_MAE, DT_OBITO, NU_FONETICO_PAI, IN_MAE_IGNORADA, CS_SITU_ID_PF, DT_CADASTRO_PF_FONTE, CS_ESTADO_CIVIL, ID_MUNI_IBGE, DT_ULTIMA_ATUALIZACAO, DT_CHEGADA, CS_FONTE_ORIGEM_NIT, NM_PAI, DT_COMP_OBITO_SISOBI, NM_MAE, CS_SEXO, DT_COMP_OBITO_CNIS, CS_ETNIA FROM PESSOAS_FISICAS WHERE (((NU_FONETICO_PF = ?) AND ((DT_NASCIMENTO = ?) OR (? IS NULL))) AND (((NU_FONETICO_MAE = ?) OR (? IS NULL)) OR (NU_FONETICO_MAE IS NULL)))


Def. do índice

INDEX_OWNER = CNISPF
INDEX_NAME = PF_FONETICO_FON_MAE_NASC_I
STATUS =VALID
UNIQUENESS = NONUNIQUE
INDEX_TYPE =VALID
TEMPORARY = N
PARTITIONED = NO
JOIN_INDEX = N
COLUMNS = NU_FONETICO_PF,NU_FONETICO_MAE, DT_NASCIMENTO

A descrição dessa tabela é muito grande.

[]’s

[Victor ArmbrustMestre]

@isacjop

Existem alguns itens a serem verificados ai, como disse o pessoal acima.
Mas antes de mais nada, é preciso ver o plano de execução, depois verificar índices (isso tb depende da cardinalidade/granularidade do índice em questão), ai entra também estatística dessa tabela, etc.. é preciso mais informações para saber qual o consumo real e também como é possível otimizar essa consulta…por gentileza poste aqui pra gente essas informações…

abs

[rmanParticipante]

@isacjop

Verifica se as estatísticas estão atualizadas:


SELECT LAST_ANALYZED
FROM DBA_TABLES
WHERE TABLE_NAME = 'PESSOA_FISICA'


Se ainda não utilizar o índice, tente criar o índice com as colunas NU_FONETICO_PF e DT_NASCIMENTO , e um segundo índice com a coluna NU_FONETICO_MAE

Atualize as estatísticas.

[vieriParticipante]

entre no SQLPLUS e rode o comando:

set lin 400
set autot on exp stat;

em seguida rode a query e poste ai o resultado.

Sem ver o plano de execução de nada podemos ajudar.

[burgaParticipante]

Só chutando o pau da barraca, com certeza é necessário ver o que o pessoal comentou, mas um bom começo é tentar acabar com esses ORs da vida:

SELECT ID_PESSOA_FISICA, CS_NACIONALIDADE, IN_NIT_FAIXA_VALIDA, ID_PAIS,
CS_GRAU_INSTRUCAO, IN_MORTE_PRESUMIDA, DT_COMP_OBITO_BENEFICIO,
NU_UF_CADASTRAMENTO, NU_VERSAO, NM_PESSOA_FISICA, DT_INIC_ADMINISTRADOR,
CS_INDIGENA, NU_FONETICO_PF, ID_FONTE_PF, IN_PAI_IGNORADO, DT_FE_DE_VIDA,
DT_COMP_OBITO_GFIP, IN_INCONSISTENTE, ID_PESSOA_FISICA_DV, CS_ADMINISTRADOR,
DT_NASCIMENTO, NU_FONETICO_MAE, DT_OBITO, NU_FONETICO_PAI, IN_MAE_IGNORADA,
CS_SITU_ID_PF, DT_CADASTRO_PF_FONTE, CS_ESTADO_CIVIL, ID_MUNI_IBGE,
DT_ULTIMA_ATUALIZACAO, DT_CHEGADA, CS_FONTE_ORIGEM_NIT, NM_PAI,
DT_COMP_OBITO_SISOBI, NM_MAE, CS_SEXO, DT_COMP_OBITO_CNIS, CS_ETNIA
FROM PESSOAS_FISICAS
WHERE NU_FONETICO_PF = ?
AND DT_NASCIMENTO = COALESCE(?,DT_NASCIMENTO)
AND NU_FONETICO_MAE = COALESCE(?,NU_FONETICO_MAE)

UNION ALL

SELECT ID_PESSOA_FISICA, CS_NACIONALIDADE, IN_NIT_FAIXA_VALIDA, ID_PAIS,
CS_GRAU_INSTRUCAO, IN_MORTE_PRESUMIDA, DT_COMP_OBITO_BENEFICIO,
NU_UF_CADASTRAMENTO, NU_VERSAO, NM_PESSOA_FISICA, DT_INIC_ADMINISTRADOR,
CS_INDIGENA, NU_FONETICO_PF, ID_FONTE_PF, IN_PAI_IGNORADO, DT_FE_DE_VIDA,
DT_COMP_OBITO_GFIP, IN_INCONSISTENTE, ID_PESSOA_FISICA_DV, CS_ADMINISTRADOR,
DT_NASCIMENTO, NU_FONETICO_MAE, DT_OBITO, NU_FONETICO_PAI, IN_MAE_IGNORADA,
CS_SITU_ID_PF, DT_CADASTRO_PF_FONTE, CS_ESTADO_CIVIL, ID_MUNI_IBGE,
DT_ULTIMA_ATUALIZACAO, DT_CHEGADA, CS_FONTE_ORIGEM_NIT, NM_PAI,
DT_COMP_OBITO_SISOBI, NM_MAE, CS_SEXO, DT_COMP_OBITO_CNIS, CS_ETNIA
FROM PESSOAS_FISICAS
WHERE NU_FONETICO_PF = ?
AND DT_NASCIMENTO = COALESCE(?,DT_NASCIMENTO)
AND NU_FONETICO_MAE IS NULL;

Abraços,

[isacjopParticipante]

É que no caso o meu usuário de acesso ao banco é bem restrito e não consigo dá certos comandos. Eu vou tentar reescrever a query de uma outra forma. Muito obrigado pela ajuda de vcs, vlw.

[isacjopParticipante]

[quote=”burga”:drjnd0wv]Só chutando o pau da barraca, com certeza é necessário ver o que o pessoal comentou, mas um bom começo é tentar acabar com esses ORs da vida:
[/quote]

Utilizei sua a consulta original usando o COALESCE e obtive um ganho de 20% no custo da consulta. Realmente não sabia que o “OR” desabilitava os índices.

[leandrolbsParticipante]

o @Burga sempre matando de locaute…hehehe

[burgaParticipante]

[quote=”isacjop”:2e2no8rf][quote=”burga”:2e2no8rf]Só chutando o pau da barraca, com certeza é necessário ver o que o pessoal comentou, mas um bom começo é tentar acabar com esses ORs da vida:
[/quote]

Utilizei sua a consulta original usando o COALESCE e obtive um ganho de 20% no custo da consulta. Realmente não sabia que o “OR” desabilitava os índices.[/quote]

Ainda assim é bom checar o plano de execução, o trace, estatísticas, cardinalidades e tudo mais que o pessoal postou aí pra tentar obter o melhor desempenho possível.

O operador OR não desabilita os índices, isso depende muito de como o seu banco está estruturado, então não tome isso como verdade absoluta. Se não fosse um índice composto e sim um pra cada coluna, poderia ser que eles fossem utilizados, é claro que com um “tratamentozinho” a mais pra indexar também os valores nulos. O que eu falei do OR é só pro seu caso, bem específico. Mas ainda acho que está longe de ser a melhor opção. Como eu disse é só o começo, pro resto seria necessário fazer uma análise mais profunda.

Isso também porque eu estou considerando que a data de nascimento da pessoa sempre vai estar preenchida, se existir valores nulos na coluna a lógica da minha consulta como está agora não vai ser a mesma da sua consulta original… Pra isso, teriam que ser feitas mais alterações.

[quote=”leandrolbs”:2e2no8rf]o @Burga sempre matando de locaute…hehehe[/quote]

É nada, vocês todos tem a razão aqui, eu é que eu tenho o mal costume de arriscar na minha bola de cristal com um pouco de lógica. Algumas vezes dá certo mas outras vezes acabo falando besteira… 😳

[isacjopParticipante]

No meu caso , realmente, pelo menos um dos três tem que vir preenchido.

[Autor]

Posts