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: Alex Filonov <afilonov_at_yahoo.com>
Date: 30 Jul 2002 08:44:10 -0700
Message-ID: <336da121.0207300744.3c8fe49c@posting.google.com>


calberto2312_at_hotmail.com (Carlos Alberto) wrote in message news:<72954535.0207291022.340a2f42_at_posting.google.com>...
> 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?

  1. Oracle usually tries to convert subqueries into joins. But it's not trying to do that with semi-joins, i.e. exists subqueries.
  2. The performance is different because, obviously, plans are different. I'm curious, I'd use explain plan to see the difference. In some cases joins are faster, in some cases exist subqueries.
  3. Result sets can be different only if you can have duplicates in subquery. What your data model says?
  4. Sometimes hint HASH_SJ improves performance of semi-joins drastically.

>
> Thanks in advanced,
> Carlos
Received on Tue Jul 30 2002 - 10:44:10 CDT

Original text of this message

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