Strange Query using Hibernate and Oracle

Question

maybe anybody can help me out with a quite strange behaviour using hibernate and oracle. It is the first time that i use hibernate and oracle, so it might be an easy step for you :).

But lets get into detail. A simple count(*) results in:

 select * from ( select count(*) as col_0_0_ from TABLE tbl ) where rownum <= ?

That would not be a big deal, but if i have a selection with a limited amount of rows to be returned, it looks like that:

select * from ( select SOME_FIELDS from TABLE tbl order by tbl.ID desc ) where rownum <= 20

And that is not very fast. If i do it by my own like

select * from from TABLE tbl where rownum <= 20 order by tbl.ID desc

it is as fast as expected. Is there anything a can do against? Maybe my entity needs some more tags?

Thank You!

JB Nizet · Accepted Answer · 2012-05-09 10:12:24Z

1

The first query doesn't make much sense, since a select count(*) returns only 1 row.

The second is probably the one you really want.

The third one looks like the second one, but doesn't do the same thing at all. The order by clause is applied after the where clause. So the third query selects the first 20 rows from the table, without any specific order, and sorts these 20 rows by ID. The second query selects the rows from the table, sorts them by ID, and return only the 20 first rows from these sorted rows.

answered May 9, 2012 at 10:12

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

djnose Over a year ago

Hi, thank you for this response, maybe i was missunderstandig how rownum in oracle works. It is now clear to me. So hibernate seems to do it absolutly right!

Collectives™ on Stack Overflow

Strange Query using Hibernate and Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related