Will adding criteria .setMaxResults (1) return only one row? Or, will it return 1 million rows, but choose one of them?
Yes, it will return only one line, hibernate uses a special db function to limit the results. Hibernate will not write 1 million lines, but db will, and then select the first line (this statement refers to a request generated by a sleeper rather than ROWNUM ).
let's say you have a table called 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 fulfill 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 rows where the score is less than or equal to 500. Here he will find 6 rows.
- Order all rows by name in ascending order.
- Assign
ROWNUM to each row. - find all rows with
ROWNUM less than 3 and return them.
The result will be.
+----+-----------+-------+ | id | name | score | +----+-----------+-------+ | 7 | ASomeName | 80 | | 6 | Bsomename | 150 | +----+-----------+-------+
DB will follow the above steps no matter how many records, so when you have order and there are many rows that satisfy the condition, Query will be very slow.
I did not understand whether ROWNUM would be applied before retrieving records or after. I would expect that the “before” would be effective, while the “after” would not be for a large set of results.
ROWNUMS - an index given by rows that satisfy all the specified conditions. The database will continue to check each row, applying all the conditions provided in the WHERE clause, if each of them is in order, assign a number to this row, go to the next. According to Dock ROWNUM is evaluated AFTER records are selected from the database , all conditions are met.
BEFORE executing the ORDER BY clause.
ROWNUM differs from LIMIT in other databases (MySQL, Postgrade, etc.). ie LIMIT finds all rows, sorts them, and then returns limited results. whereas ROWNUM will be assigned to the rows as soon as it satisfies all conditions. This is why hibernate generates an internal query to get consistent sorting results.
If with the same data shown in the above table, run 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 due to the fact that the database begins to find rows that satisfy the condition (score <= 500), provide each of them with the ROWNUM index until ROWNUM <3, and then arrange the rows by name. Oracle will apply the rownum predicate once a row has been identified as part of a result set based on the rest of the query
Read this and this article, which they are really detailed.