Philippe Chaléat wrote:
> Here are two versions of the same query. The first one with
> theta join
> style, and the second one with ANSI join style. The second one is far
> faster than the first one (these are http://hibernate.org generated
> queries). Is there an explanation for this ?
>
> Thanks
>
> With theta join style
>
> select
> mouvemen0_.ik as ik,
> mouvemen0_.code as code,
> [...]
> from
> SV_Mouvement mouvemen0_,
> SV_Commande commande1_,
> SV_Transporteur transpor2_,
> SV_CdeLig cdelig3_
> where
> mouvemen0_.noCde=commande1_.ik and
> mouvemen0_.noCde=commande1_.ik and
> commande1_.noTra=transpor2_.ik and
> mouvemen0_.noCdeLig=cdelig3_.ik and
> ((mouvemen0_.ik in(? , ? , ?)))
> order by
> commande1_.heurePrep,
> transpor2_.libStd,
> commande1_.livNom,
> cdelig3_.code
>
>
>
> With ANSI join style :
>
> select
> mouvemen0_.ik as ik,
> mouvemen0_.code as code,
> from
> SV_Mouvement mouvemen0_
> inner join
> SV_Commande commande1_
> on
> mouvemen0_.noCde=commande1_.ik
> inner join
> SV_Transporteur transpor2_
> on
> commande1_.noTra=transpor2_.ik
> inner join
> SV_CdeLig cdelig3_
> on
> mouvemen0_.noCdeLig=cdelig3_.ik
> where
> (mouvemen0_.ik in(? , ? , ?))
> order by
> commande1_.heurePrep,
> transpor2_.libStd,
> commande1_.livNom,
> cdelig3_.code
There should be no performance difference between queries
written in ansi style or theta sytle.
Please post the explain plan of both queries.
BTW your first query is not exactly the same because
you doubled the first join condition or is it a typo.
Kind regards,
Holger
SAP Labs Berlin
