The following EJB-QL query
SELECT OBJECT(o) FROM Weight o
WHERE
( o.sale.reference = ?1 )
OR ( o.purchase.reference = ?2 )
OR ( o.declaration.reference = ?3 )
OR ( o.externalSale.reference = ?4 )
OR ( o.externalPurchase.reference = ?5 )
Is compiled to the following SQL query :
SELECT t0_o.UNID_UOID
FROM T_WEIGHT t0_o, T_WEIGHTGROUP t7_o_externalSale, T_WEIGHTGROUP t3_o_purchase, T_WEIGHTGROUP t1_o_sale, T_WEIGHTGROUP t5_o_declaration, T_WEIGHTGROUP t9_o_externalPurchase
WHERE
((t1_o_sale.f_Reference = ?) AND t0_o.f_Sale_UOID=t1_o_sale.UNID_UOID)
OR ((t3_o_purchase.f_Reference = ?) AND t0_o.f_Purchase_UOID=t3_o_purchase.UNID_UOID)
OR ((t5_o_declaration.f_Reference = ?) AND t0_o.f_Declaration_UOID=t5_o_declaration.UNID_UOID)
OR ((t7_o_externalSale.f_Reference = ?) AND t0_o.f_ExternalSale_UOID=t7_o_externalSale.UNID_UOID)
OR ((t9_o_externalPurchase.f_Reference = ?) AND t0_o.f_ExternalPurchase_UOID=t9_o_externalPurchase.UNID_UOID)
Which is very suboptimal (and actually makes the DB search for a very long time), while the simple query
SELECT t0_o.UNID_UOID
FROM T_WEIGHT t0_o, T_WEIGHTGROUP t1_o
WHERE
((t1_o.f_Reference = ?) AND t0_o.f_Sale_UOID=t1_o.UNID_UOID)
OR ((t1_o.f_Reference = ?) AND t0_o.f_Purchase_UOID=t1_o.UNID_UOID)
OR ((t1_o.f_Reference = ?) AND t0_o.f_Declaration_UOID=t1_o.UNID_UOID)
OR ((t1_o.f_Reference = ?) AND t0_o.f_ExternalSale_UOID=t1_o.UNID_UOID)
OR ((t1_o.f_Reference = ?) AND t0_o.f_ExternalPurchase_UOID=t1_o.UNID_UOID)
is equivalent and much easier/faster to evaluate for any DB.
Joachim
