Convertendo IN em EXISTS, dúvida

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Avatar do usuário
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 342
Registrado em: Seg, 29 Jan 2007 7:36 am
Localização: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

galera, tenho um dúvida, preciso fazer um Select aqui mas utilizando EXISTS, já disseram que devemos evitar o uso do IN devido a performance e que ele tb é muito 'amador', seu mecanismo etc..

consegui bolar a lógica usando IN, mas ainda não absorvi 100% ainda o funcionamento do bendito EXISTS

ex: eu sou RAFAEL, chefe do DEPTO DE INFORMÁTICA, que tem os subsetores subordinados a mim (Projetos, Internet, Suporte, etc) eu preciso trazer todos os funcionários somente dos subsetores que são subordinados ao meu, quando entrar na tela do sistema:

esse primeira select será a select interna, que trará os subsetores subordinados a mim

Selecionar tudo

SELECT gehierarq.orgs_cd_subordinado
  FROM gehierarq, georgset, rhlotacao
 WHERE rhlotacao = georgset.orgs_cd
   AND georgset.orgs_cd = gehierarq.orgs_cd                                  
   AND rhlotacao.func_cd = '000654' --Meu código de funcionário/chefe
   AND rhlotacao.lota_bo_principal = 'S';

resultado que o select interno irá trazer:

Selecionar tudo

ORGS_CD_SUBORDINADO
-------------------
000028 (ASSESSORIA DE PROJETOS)
000029 (PROJETOS)
000030 (SUPORTE)
000600 (INTERNET)

eu fiz o select com IN dessa forma?

Selecionar tudo

SELECT rhlotacao.orgs_cd, rhpessoa.pess_cd, rhpessoa.pess_nm_nome
  FROM rhpessoa, rhfunc, rhlotacao, georgset
 WHERE rhpessoa.pess_cd = rhfunc.pess_cd
   AND rhfunc.func_cd = rhlotacao.func_cd
   AND rhlotacao.orgs_cd = georgset.orgs_cd
   AND rhfunc.func_dt_demissao IS NULL
   AND rhlotacao.data_dt_final IS NULL   
   AND rhlotacao.orgs_cd IN (SELECT gehie.orgs_cd_subordinado
                               FROM gehierarq gehie, georgset georg, rhlotacao rhlota
                              WHERE rhlota.orgs_cd = georg.orgs_cd
                                AND georg.orgs_cd = gehie.orgs_cd                                
                                AND rhlota.func_cd = '000654'
                                AND rhlota.lota_bo_principal = 'S')
 ORDER BY rhpessoa.pess_nm_nome;
como eu poderia utilizar o EXISTS nele ao invés do IN???

até tentei algumas coisas aqui mas meio no TA (tentativa acerto), meio sem entender mesmo, e trouxe umas coisas absurdas, zilhões de registros e gente que não tem nada a ver com meu depto.
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 365
Registrado em: Ter, 24 Mai 2005 2:24 pm
Localização: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

E ai Porva, beleza??

você poderia estar alterando a subconsulta que você utilizou dentro do IN, segue ai um exemplo..

Selecionar tudo

   AND EXISTS (SELECT 1
                 FROM gehierarq gehie, georgset georg, rhlotacao rhlota 
                WHERE rhlota.orgs_cd = georg.orgs_cd 
                  AND georg.orgs_cd = gehie.orgs_cd                                
                  AND rhlota.func_cd = '000654' 
                  AND rhlota.lota_bo_principal = 'S'
                  AND rhlotacao.orgs_cd = gehie.orgs_cd_subordinado ) 
da uma olhada na ultima linha do subselect, a amarração foi feita da tabela de fora "rhlotacao" com a tabela de dentro "gehie".
tenta mudar ai..

[]'s
Avatar do usuário
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 342
Registrado em: Seg, 29 Jan 2007 7:36 am
Localização: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

pow, ow o cara aí!

hehehe

deu certinho Cristiano, valeu cara, agora preciso entender melhor essas paradas

os caras passas as coisas pra gente fazer muito 'jogadas', fo*a!



muito obrigado, deu certinho aqui
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 365
Registrado em: Ter, 24 Mai 2005 2:24 pm
Localização: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

Então,
o exists funciona como se fosse uma verificação boolean, se o q estiver dentro do subselect retornar ao menos um registro aquele exists vai deixar a consulta rodar...

[]'s!
Avatar do usuário
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 342
Registrado em: Seg, 29 Jan 2007 7:36 am
Localização: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

por isso que não há necessidade de retornar os campos no Select interno então?....


SELECT 1...
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 365
Registrado em: Ter, 24 Mai 2005 2:24 pm
Localização: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

Sim, isso mesmo,
não dá erro se você colocar o nome de algum campo no lugar do 1, poderia até mesmo colocar o *, mas utilizando o 1 pode melhorar a performance.

[]s'!!
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Sex, 30 Mar 2007 7:26 pm
Localização: Londrina - PR
Rafael O. Genaro

Apenas para tentar limpar um pouco a reputação do pobre in... :P
galera, tenho um dúvida, preciso fazer um Select aqui mas utilizando EXISTS, já disseram que devemos evitar o uso do IN devido a performance e que ele também é muito 'amador', seu mecanismo etc..
Na verdade esse tipo de afirmação não tem fundamentos... Não existe uma regra geral dizendo que o in sempre será executado com um custo maior do que uma consulta equivalente utilizando o exists.
O resultado final depende muito da disposição dos dados na sua tabela (quantidade de registros retornados pelas querys, cardinalidade,..), dos índices existentes sobre as mesmas e também de parâmetros de banco.

Uma discussão bem interessante no site Ask Tom sobre o assunto (com alguns exemplos também) pode ser encontrada aqui.


Outro assunto que apareceu neste tópico que me chamou a atenção é a respeito das colunas utilizadas no select interno do comando exists...
Já ouvi falar muito a respeito de utilizar um "select 1", ou "select null" em conjunto com o comando exists, a fim de minimizar a alocação de memória e evitar que seja realizado um acesso extra à tabela para recuperar a lista dos campos contidos no select.

Então acabei montando o seguinte teste, apenas para tentar descobrir o que realmente acontece:

Selecionar tudo

create or replace package k_test
as
 function f_inc return number; -- Incrementa a variável x
 function f_get return number; -- Retorna o valor da variável x
 procedure p_reset;            -- Zera a variável x

 x number default 0;
end k_test;
/

create or replace  package body k_test
as
 function f_inc return number is
 begin
   x := x + 1;
   return x;
 end f_inc;

 function f_get return number is
 begin
   return x;
 end f_get;

 procedure p_reset is
 begin
  x := 0;
 end p_reset;

end k_test;
/
Basicamente, nós criamos uma package com uma variável x, que funciona como um contador, e três funções, para:
- somar 1 ao valor de x;
- zerar o valor de x;
- recuperar o valor atual de x.

Executando os seguintes comandos:

Selecionar tudo

exec k_test.p_reset;
select k_test.f_get from dual; 
-- x = 0

select k_test.f_inc from all_objects;
-- Chamamos a função f_inc para CADA linha retornada

select k_test.f_get from dual; 
-- x = <quantas linhas tiver em all_objects>, pois para cada linha retornada o argumento da cláusula SELECT foi processado uma vez.
O interessante acontece quando fazemos a mesma coisa, dentro de um exists:

Selecionar tudo

exec k_test.reset;
select k_test.f_get from dual; 
-- x = 0

select 1 from all_objects where exists (select k_test.f_inc from all_objects);
-- A função f_inc é chamada na subquery do exists.

select k_test.f_get from dual; 
-- x = 1. Isso prova que não importa quantos ou quais campos sejam colocados pelo programador como retorno do comando select interno do exists, estes campos serão sempre ignorados pelo banco no momento de executar a consulta.
Acabei testando apenas na vesão 10g, mas se alguém tiver a oportunidade de testar em outras versões do banco, poste o resultado aqui!
Responder
  • Informação