I'm examining Hibernate-generated SQL statements for performance tuning purposes, and would like to know whether it is possible to influence Hibernate to choose a particular ordering of tables in the FROM clause.
A known optimization for Oracle and other databases that use cost-based optimization is to manipulate the ordering of tables in the FROM clause. This can influence the query plan in cases where the optimizer doesn't know which ordering is best, resulting in much improved query times.
Hibernate-generated queries are seemingly random in the ordering of tables. For instance, Hibernate might generate this WHERE clause: SELECT ... FROM t1 INNER JOIN t2 ON t1.a=t2.a INNER JOIN t3 ON t1.b=t3.b
However, we might know that due to indexing, etc., a much better ordering would be one where t3 appears earlier than t2, e.g.: SELECT ... FROM t1 INNER JOIN t3 ON t1.b=t3.b INNER JOIN t2 ON t1.a=t2.a
Likewise, Hibernate also seems to mix the ordering of INNER JOIN and LEFT OUTER JOINs, sometimes placing LOJs in front of IJs, even in cases where it's not necessary for correctness.
Is there any way to influence Hibernate to change the table ordering? How does it decide in the first place?
|