Understanding Hibernate's Criteria#setMaxResults

Question

The docs say:

Criteria setMaxResults(int maxResults)
>Set a limit upon the number of objects to be retrieved.

Let's say I have the following Criteria:

Criteria criteria = createCriteria(); // creates criteria for MY_TABLE entity
criteria.list().length; // let's say there's a million records in this table

Will adding criteria.setMaxResults(1) return only a single row? Or, will it still return 1 million rows, but pick one of them?

When running a query similar to my above code example against my Oracle DB, I saw that ... ROWNUM < 2 SQL was being generated.

But, when I looked at a ROWNUM FAQ, I didn't understand if ROWNUM will be applied before retrieving records, or after. I would expect "before" to be efficient, whereas "after" would not be for a large result set.

ROWNUM is evaluated AFTER records are selected from the database and BEFORE the execution of ORDER BY clause.

Solution

Will adding criteria.setMaxResults(1) return only a single row? Or, will it still return 1 million rows, but pick one of them?

Yes, it will return only one row, hibernate uses db specific feature to limit the results. Hibernate will not pickup 1 million rows but db will and then select the first row(This statement is with regards to the query generated by hibernate and not ROWNUM).

let's say you have a table named user, In which you have 8 rows.

+----+-----------+-------+
| id |   name    | score |
+----+-----------+-------+
|  1 | Xyz       |   500 |
|  2 | Name3     |   200 |
|  3 | Name2     |   300 |
|  4 | Name4     |   100 |
|  5 | SomeName  |   600 |
|  6 | BSomeName |   150 |
|  7 | Asomename |    80 |
|  8 | Csomename |   700 |
+----+-----------+-------+

Now, You run the following criteria.

criteria.add(Restriction.le("score", 500));
criteria.addOrder(Order.asc("name"));
criteria.setMaxResults(2);

The following query generated by Hibernate.

select * from(select * from user where score <= 500 order by name) where ROWNUM < 3; 

DB will execute it in the following order.

• Find all the rows where score is less than or equal to 500. here it will find 6 rows.
• Order all the rows by name in ascending order.
• Assign ROWNUM to each row.
• find all the rows with ROWNUM less than 3 and return them.

Result will be.

+----+-----------+-------+
| id |   name    | score |
+----+-----------+-------+
|  7 | ASomeName |   80  |
|  6 | Bsomename |   150 |
+----+-----------+-------+

DB will executes the above steps no matter how many records, so when you have order by and there are many rows which satisfies the condition, Query will be really slow.

I didn't understand if ROWNUM will be applied before retrieving records, or after. I would expect "before" to be efficient, whereas "after" would not be for a large result set.

ROWNUMS are index given to rows which satisfy all the given condition. DB will keep checking each row apply all the condition provided in WHERE clause, if everythig is fine then assign a number to that row, move on to the next one. As the doc says ROWNUM is evaluated AFTER records are selected from the database means all the conditions are satisfied.

BEFORE the execution of ORDER BY clause.

ROWNUM is different from LIMIT in other databases(MySQL, Postgrage etc). i.e LIMIT finds all the rows, sort them and then return limited results. whereas ROWNUM will be assigned to rows as soon as it satisfies all the conditions. That is why hibernate generated inner query to get consistent sorted results.

If with the same data given in the above table if you execute the following query.

select * from user where score <= 500 where row_num < 3 order by name;

You will get the following result.

+----+-----------+-------+
| id |   name    | score |
+----+-----------+-------+
|  3 | Name2     |   300 |
|  2 | Name3     |   200 |
+----+-----------+-------+

This is because DB starts finding rows which satisfy the condition(score <= 500), give each of them ROWNUM index until the ROWNUM < 3, and then order the rows by name. Oracle will apply the rownum predicate as soon as a row is identified as part of the result set based on the rest of the query

Read this and this articles they are really detailed.

Comments

[Kevin Meredith]

Could you please elaborate with respect to, but db will: Yes, it will return only one row, hibernate uses db specific feature to limit the results. Hibernate will not pickup 1 million rows but db will and then select the first row.

[Kevin Meredith]

Let't say that your last query select * from user where score <= 500 where row_num < 3 order by name; matched 1 million rows (but here we only want 2). Would all 1 million be grabbed, and then only takes 2 of those rows?

[bitkot]

No, it will select first 2 rows and then order them by name. Sorry my mistake i didn't specify that in the answer properly. I will update that.

[bitkot]

Oracle will apply the rownum predicate as soon as a row is identified as part of the result set based on the rest of the query. Run an explain plan on the query.

[Kurt Miller]

Sir, I have a problem. I think ROWNUM is messing things. There are 3 records in my DB (oracle 11g). if I say setFirstResult(0 or 1 or 2) , setMaxResult(1), pagin works as expected and on the 3 pages I get the results. But if I say setFirstResult(0 or 1 or 2) , setMaxResult(2), then paging is not working as I want. I get on page 1 and 2 the same two records. I thought I'd get on page 2 the last record since there are only 3 records but nope, it does not work that way...Help please ?