Hi all
I'm working on a project involving an Oracle 7.3 database, and I'm using Hibernate. I know that using this database is not optimal by any means, but there's nothing i can do about it, and it seams to work very well.

The problem is when i try to use queries that are sorted, like "from Object o order by o.property"), and then put a limit on the results returned by the query. Hibernate generates a SQL query with a subselect, using the rownum function like this:



Oracle then complains about a missing right parenthesis, though the generated SQL looks fine to me. I've tried to put the "order by" cause after the "where rownum" cause, like this:



And it works fine. Is there any way I can use the



to modify the way Hibernate generates the SQL query? Session conf is MBean on Jboss 3.2, Hibernate version 2.1.1.

Help much appriciated!
milx

I've read that one, and it ends by saying that this is not supported by Oracle 8.0, and therefore not on 7.3 either, but it also says that .
My question is then how can I put the order by cause after the rownum cause, by customizing the dialect?
milx

I don't believe this gives you the same results. The second query you presentet is - give me the first 10 results and then order them, while the original one is order the results then give me the first 10. I think oracle would be seriously messed up if this works the same.

You could change the dialect to use a different limiting strategy by overriding getLimitSelectString() (read the source)

Well, running Oracle 7 in 2004 is messed up....
I see the problem with ordering the results aferwards, asking for top 10 ordered by something will return the top 10 rows in the database, and then sort these.....
Currently I let Hibernate order the results, then I only return a specific number of these to the client. This works fine with a small total number of rows in the table, but my guess is that 500.000 rows will be a performance killer.
How does Hibernate perform if you iterate over a large result? A client hardly ever asks for more than the 100 first results of a particular query. If the results returned by the Hibernate query are ordered, but not limited, I really only have to use the first 100 rows, not the other 499.900. Does Hibernate create 500.000 objects, or only those that are needed by the Iterator?
Thank you very much for the help!
milx

You could do it that way:


This will result in one query for the ids, and 10 more querys for the first 10 objects. That way you can prevent the huge memory consumption at the cost of more queries.

If there is a way to do the querying you want in direct SQL, you should be able to modify the dialect to have Hibernate behave accordingly. If not, you will have to make some tradeoffs ...

By "customize the dialect" we mean disable the dialect-specific-limit stuff *altogether*. Look at some of the other dialects to see what this would look like.

Thanks a lot for the feedback! I've been reading some in the hard to find Oracle 7 reference, and it seems like both limiting and ordering the results are impossible. If anyone knows a workaround, please let me know. I'm now going with gloeglms purposed Iterator solution, it works fine but slow.
milx

Just try overriding supportsLimit() of the oracleDialect to return false, and override all the other limit stuff to throw an UnsupportedOperationException. Then try to use setMaxResult, etc. It could be Hibernate simulates the limit efect, I just don't know how effective it is :)

As I understand it Hibernate then does a normal Select, and gets you the appropriate part of the result set in the returned List. This avoids the Object generation overhead, but still gets you a large result set. You have to decide yourself where to trade off.