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?
- Oracle usually tries to convert subqueries into joins. But it's not trying to
do that with semi-joins, i.e. exists subqueries.
- 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.
- Result sets can be different only if you can have duplicates in subquery.
What your data model says?
- Sometimes hint HASH_SJ improves performance of semi-joins drastically.
>
> Thanks in advanced,
> Carlos
Received on Tue Jul 30 2002 - 10:44:10 CDT
