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
  

Mensagemem Qua, 14 Nov 2007 1:53 pm

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..

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

Código: Selecionar todos
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:
Código: Selecionar todos
ORGS_CD_SUBORDINADO
-------------------
000028 (ASSESSORIA DE PROJETOS)
000029 (PROJETOS)
000030 (SUPORTE)
000600 (INTERNET)



eu fiz o select com IN dessa forma?

Código: Selecionar todos
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.
Porva
Localização: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Mensagemem Qua, 14 Nov 2007 2:03 pm

E ai Porva, beleza??

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

Código: Selecionar todos
   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
Tineks
Localização: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Mensagemem Qua, 14 Nov 2007 2:17 pm

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
Porva
Localização: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Mensagemem Qua, 14 Nov 2007 2:23 pm

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!
Tineks
Localização: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Mensagemem Qua, 14 Nov 2007 2:37 pm

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


SELECT 1...
Porva
Localização: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Mensagemem Qua, 14 Nov 2007 2:46 pm

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'!!
Tineks
Localização: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Mensagemem Dom, 18 Nov 2007 2:33 pm

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:

Código: Selecionar todos
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:
Código: Selecionar todos
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:
Código: Selecionar todos
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!
rogenaro
Localização: Londrina - PR

Rafael O. Genaro



Voltar para SQL

Quem está online

Usuários navegando neste fórum: Nenhum usuário registrado e 4 visitantes