在oracle中是否必须通过分页命令才能执行分页? [英] Is order by mandatory for pagination in oracle?

问题描述

我想了解oracle中的分页.为了在sql server中进行分页，有OFFSET ROWS FETCH NEXT，其中order by是强制性的以便进行分页.

I want to know about pagination in oracle.In order to do pagination in sql server, there is OFFSET ROWS FETCH NEXT in which order by is mandatory in order to do pagination.

我想弄清楚是否要在oracle中进行分页是order by强制性的.

I am trying to figure out out if i want to do pagination in oracle is order by mandatory.

以下是我认识的在Oracle中进行分页的方法:

Following are the ways to do pagination in oracle i came to know about:

1)偏移n行仅次于m行: 强制排序是强制性的.

1) OFFSET n ROWS FETCH NEXT m ROWS ONLY : Order by is mandatory here is guess.

2)行数: 这样我不确定orderby是否对分页是强制性的 与RowNum.

2) RowNum: With this i am not sure whether orderby is mandatory or not for pagination with RowNum.

我正在尝试找出以下问题的答案，如果有人可以帮助我，我将不胜感激:

I am trying to find out answers of below questions and i will appreciate if someone could please help me with the answers :

1)是否必须通过OFFSET n ROWS FETCH NEXT m ROWS来强制订购?

1) Is order by mandatory with OFFSET n ROWS FETCH NEXT m ROWS ONLY?

2)RowNum是否必须按顺序订购?

2) Is order by mandatory with RowNum?

如果我有这样的选择查询:

If i have a select query like this:

select Id,Amount from source

3)如何使用RowNum在上述sql查询中创建分页?

3) How to create pagination in above sql query using RowNum?

推荐答案

1)是否必须通过OFFSET n ROWS FETCH NEXT m ROWS强制订购?

1) Is order by mandatory with OFFSET n ROWS FETCH NEXT m ROWS ONLY?

从语法上讲不是，在语义上是！

Syntactically not, semantically it is!

原因:如果不添加ORDER BY子句，则数据库可以按任何顺序返回订单.现在，如果您首先对第一页执行查询，则将以任何顺序获取它们.下次您执行查询以获取下一页时，可能会在其他任何行中返回订单.

Reason: if you don't add an ORDER BY clause, the database may return the orders in any order. Now if you execute the query first for the first page, you'll get them in any order. The next time you execute the query to fetch the next page may return the orders in any other row.

因此，您需要在ORDER BY子句上建立确定的行顺序(以使任何行都不能与另一行对等).实际上，为了安全起见，应始终在ORDER BY子句中包含unique/primary key. (您仍然可以在ORDER BY子句中使用非唯一性-甚至用作前导列).

Therefore you need on ORDER BY clause that establishes a definite order of rows (so that no row is a peer with another row). In practice, you should always include something unique/primary key in the ORDER BY clause to be on the safe side. (you can still use non-unique in the ORDER BY clause — even as leading columns).

例如

ORDER BY time_stamp DESC, id DESC

对于所有分页类型来说，这是合乎逻辑的要求，这些分页类型对每个页面执行单独的查询.

This is a logical requirement for all types of pagination that execute separate queries for each page.

2)RowNum是否必须按顺序订购?

2) Is order by mandatory with RowNum?

是的，请参见上文.

3)如何使用RowNum在上述sql查询中创建分页?

3) How to create pagination in above sql query using RowNum?

单独使用OFFSET和ROWNUM都不足以实现稳定的分页.

Neither OFFSET nor ROWNUM alone are good enough to implement stable pagination.

考虑一下:如果在获取第一页之后并且在获取第二页之前插入了新行，该怎么办?

Think about this: What if a new row is inserted after you have fetched the first page and before you fetch the second page?

还有另一种实现稳定分页的方法，称为键集分页.

There is another way to implement stable pagination called key-set pagination.

主要思想不是通过告诉数据库要跳过多少行并希望同时不添加行来跳过可见行"，而是要使用唯一标识来识别哪些行已经看到，哪些没有看到.

The main idea is not to skip "seen rows" by telling the database how many rows to skip and hoping no rows were added in the meanwhile, but to use a unique identification of which rows have already been seen and which not.

SELECT ...
  FROM ...
 WHERE ...
   AND id < ?last_seen_id
 ORDER BY id DESC
 FETCH FIRST 10 ROWS ONLY

请记住，无论如何，您都需要一个ORDER BY来建立一个默认命令.您可以使用这些列来精确指出直到收到数据之前的位置.

Remember that you need an ORDER BY that establishes a definitie order anyway. You can use these columns to pinpoint the place until where you have received the data before.

在我的网站上了解有关此方法的更多信息:

Read more about this method at my website:

http://use-the-index-luke.com/no-offset

这篇关于在oracle中是否必须通过分页命令才能执行分页?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！