Hibernate, Oracle, 'Union' and 'Or' - Is it time to see other people?

I had some trouble with poor performance on an Oracle Database with a query generated by Hibernate. It was being generated using Hibernate Criteria.

Here is the SQL that Hibernate generated:

(this is just pseudo sql code I invented to represent my actual sql queries, I hope you get the general idea!)

select id from table1

  where

    (id in (select statement on table2))

  or

    (condition on table1)


I went and talked to the DBA, and he fiddled around a bit with the query. He came up with an equivalent query by converting it to this:


select id from table1

  where id in

    (select statement on table2)

  union

    (select statement on table1)


It ran about a million times faster! Hyperbolically speaking.

I found at least one reference to validate this observed behaviour where it says:

Consider using IN or UNION in place of OR on indexed columns. ORs on indexed columns causes the optimizer to perform a full table scan.

So it seems like all I had to do was to make Hibernate spit out an SQL statement using UNION instead of using OR!

Then I found out that Hibernate doesn't support UNION.

:(

So Oracle has trouble with OR, and Hibernate doesn't support UNION.


This isn't the first time I've had issues with Hibernate and Oracle playing nicely together. I just don't think Hibernate and Oracle are right for each other - I've always been indifferent when it comes to choosing ORM Tools but maybe Toplink is the better choice when an Oracle database is involved.