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.

Answer 1

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.