oracle 了解 Hibernate 的标准#setMaxResults

Understanding Hibernate's Criteria#setMaxResults

提问by Kevin Meredith

The docssay:

该文件说：

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 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?

添加criteria.setMaxResults(1)只会返回一行吗？或者，它仍然会返回 100 万行，但选择其中之一？

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

当对我的 Oracle DB 运行类似于我上面的代码示例的查询时，我看到... ROWNUM < 2正在生成 SQL。

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

但是，当我查看ROWNUM FAQ 时，我不明白ROWNUM是在检索记录之前还是之后应用。我希望“之前”是有效的，而“之后”则不适用于大型结果集。

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

ROWNUM 在从数据库中选择记录之后和执行 ORDER BY 子句之前进行评估。

回答by bitkot

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

添加criteria.setMaxResults(1) 会只返回一行吗？或者，它仍然会返回 100 万行，但选择其中之一？

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).

是的，它只会返回一行，hibernate 使用 db 特定功能来限制结果。Hibernate 不会选择 100 万行，但 db 会然后选择第一行（此语句是关于 hibernate 生成的查询而不是ROWNUM）。

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

假设您有一个名为 的表user，其中有 8 行。

+----+-----------+-------+
| 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.

以下查询由 Hibernate 生成。

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

DB will execute it in the following order.

DB 将按以下顺序执行它。

• 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 ROWNUMto each row.
• find all the rows with ROWNUMless than 3 and return them.

• 找到所有分数小于或等于 500 的行。这里会找到 6 行。
• 按名称升序排列所有行。
• 分配ROWNUM给每一行。
• 找到所有ROWNUM小于 3的行并返回它们。

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.

DB不管有多少条记录都会执行上面的步骤，所以当你有order by并且有很多满足条件的行时，Query会很慢。

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。我希望“之前”是有效的，而“之后”则不适用于大型结果集。

ROWNUMSare 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 databasemeans all the conditions are satisfied.

ROWNUMS是给满足所有给定条件的行的索引。DB 将继续检查每一行是否应用 WHERE 子句中提供的所有条件，如果一切正常，则为该行分配一个数字，然后转到下一行。正如文档所说，这ROWNUM is evaluated AFTER records are selected from the database意味着所有条件都已满足。

BEFORE the execution of ORDER BY clause.

在执行 ORDER BY 子句之前。

ROWNUMis 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.

ROWNUM与其他数据库（MySQL、Postgrage 等）中的 LIMIT 不同。即 LIMIT 查找所有行，对它们进行排序，然后返回有限的结果。而 ROWNUM 将在满足所有条件后立即分配给行。这就是为什么 hibernate 生成内部查询以获得一致的排序结果。

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

这是因为 DB 开始查找满足条件（分数 <= 500）的行，为每个行提供 ROWNUM 索引，直到 ROWNUM < 3，然后按名称对行进行排序。Oracle 将根据查询的其余部分将行识别为结果集的一部分后立即应用 rownum 谓词

Read thisand thisarticles they are really detailed.

阅读这篇和这篇文章，他们真的很详细。