Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: EXISTS and join

Re: EXISTS and join

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Mon, 29 Jul 2002 20:36:09 GMT
Message-ID: <3D45A6ED.12D3C3B5@exesolutions.com>


Carlos Alberto wrote:

> Hi all,
>
> I have a very bad performance query using EXISTS, and I was
> wondering to change it to a join. Itīs like this :
>
> select clie.unite_commercialisation,
> clie.type_client, clie.code_devise
> from pi_client clie,
> pi_contrat_v_3 cont
> where nvl(cont.reference_client_benef,' ') = clie.reference_client
> and cont.etat_contrat = 'FIN'
> and exists
> (select null
> from pi_facture fact
> where fact.numero_contrat like
> substr(cont.numero_contrat,1,9)||'%'
> and nvl(fact.date_annulation,0) = 0
> and nvl(fact.date_reglement,0) = 0)
>
> and it would be like this :
>
> select clie.unite_commercialisation,
> clie.type_client, clie.code_devise
> from pi_client clie,
> pi_contrat_v_3 cont,
> pi_facture fact
> where nvl(cont.reference_client_benef,' ') = clie.reference_client
> and cont.etat_contrat || '' = 'FIN'
> and fact.numero_contrat like substr(cont.numero_contrat,1,9)||'%'
> and nvl(fact.date_annulation,0) = 0
> and nvl(fact.date_reglement,0) = 0
>
> The performance of the join query was better than with EXISTS
> clause. But I read in some books that sometimes I cannot make this
> change because the result set would be different. Itīs true or not?
> Must I compare both result sets?
>
> Thanks in advanced,
> Carlos

You must compare the results
You must run explain plan on your queries You must determine whether indexes will help

That is what you are being paid for. ;-)

But I do find this puzzling: nvl(cont.reference_client_benef,' ') and have you considered restructuring your data to eliminate the need for: substr(cont.numero_contrat,1,9)?

Daniel Morgan Received on Mon Jul 29 2002 - 15:36:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US