CODEWITHSUNDEEP
javasqloracle-databasehibernatejpa
djnose
djnose

Reputation: 945

Strange Query using Hibernate and Oracle

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!

Upvotes: 1

Views: 503

Answers (1)

JB Nizet
JB Nizet

Reputation: 691635

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.

Upvotes: 1

Related Questions