Is there a way to determine the order of tables listed in the from clause in the generated SQL? In Oracle, the default behavior is for the driving table of a join to be listed last, and it should be the smallest table. What I'm seeing is that subclass tables are not being listed after the it's superclass.

For example, I have a class, Dog, which is a subclass of Animal. I'm mapping them using <joined-subclass>:



I'm doing a simple query: from Dog as d where d.breed=?

The SQL that gets generated is: select * from Dog d, Animal a where d.id=a.id and d.breed=?, which in this case turns out to be horribly slow. I'd like to be able to flip the 2 tables in the from clause so that it reads: select * from Animal a, Dog d where a.id=d.id and d.breed=?.

Is this possible? Am I missing something?

I'm using Hibernat 2.1.6.

Thanks!

P/S - Is there anyway to work with the indexes?

Is there something that could be done to the class that generates Oracle specific SQL to reverse the order of tables in the from clause for subclasses?

No, not possible

Is this a valid improvement request then? Not necessarily to make it possible via HQL, but at least to have the class generating the SQL for Oracle to be smart enough to put the parent table ahead of the child table?

It is default behaviour in very old oracle (maybe version 5 or 6) - version >= 7 have smarter behaviour
If you can database >= 7 and this behavior check parameter optimizer_mode
It is default CHOOSE, but for your behavior it is maybe RULE (old behaviour)

If your table have indexes then Oracle use it automatic

regards

Well, I'm using Oracle 9i, and I've checked and that property is set to CHOOSE, so I doubt this is the problem.

I've also taken a look at the Dialect class, and there doesn't appear to be a way to specify the order of tables in the from clause. Given that development on the 2.x line is over, is this something that could be supported in the 3.x line?

If the 3.x line doesn't already support this, is this an acceptable feature request?


Thanks...

This isn't hibernate problem - Oracle optimizier don't depend from the order of tables except You haven't analyze your schema - do analyze for tables or complete schema

regards