Oracle查询中的分页问题

Oracle查询中的分页问题，对我来说，一直是个老大难，今天仔细梳理了一下。

网上最多的方案是这种

1.

SELECT *
FROM (
    SELECT rownum AS rn
        ,t.rowid as row_id,t.*
    FROM table_a t rownum <= 20
    ) ds
WHERE ds.rn >= 10

2.还有一种就是利用row_number() over( order by column)

SELECT *
FROM (
    SELECT  row_number() over(order by t.ROWID)  AS rn
        ,t.*
    FROM table t
    ) ds
WHERE ds.rn BETWEEN :start_row + 1
        AND :start_row + 1000

3.还有一种非常不常见的写法（这种写法sqlserver高版本的也支持）

SELECT *
FROM
   table 
ORDER BY ROWID
OFFSET 100 ROWS 
FETCH NEXT 10 ROWS ONLY

下面对这几种方法做说明，不同版本的数据库会有差异。

1. rownum的方式看似美好，实则把bug藏到了一个根本看不见的地方。

rownum是在查询执行之后再生成的虚列或者就把它理解成一个函数结果，然而，在没有指定order by 的情况下，Oracle每次排序之后导致的查询结果可能不一致。

请将查询数据做全表遍历，取出rowid，这个时候你会惊喜的发现，rowid会有重复值，也就是说，表里面有5000行，可能遍历完之后，实际的数据只有4000行（每次结果不完全相同）。

但是如果在子查询中指定了Order by 又会怎么样呢？

结论如下：1.order by  具有唯一值的列，结果不会出现重复行

                  2.order by 具有重复值的列，结果会出现重复行

2.ROW_NUMBER() 的结论其实和1差不多，也是会跟着order by 表现出不同结果。

3.OFFSET FETCH  NEXT 同样也是会跟着order by 表现出不同结果。

从语法的可读性角度来讲，我个人更倾向第三种。

我的遍历方式就是每次取出100行，将rowid放到Set中，最后打印出这个Set，结果真的是出乎我的意料。

a.关于rowid,

For each row in the database, the ROWID pseudo column returns a row’s address.
Oracle9i rowid values contain information necessary to locate a row:
n The data object number of the object
n Which data block in the datafile
n Which row in the data block (first row is 0)
n Which datafile (first file is 1). The file number is relative to the tablespace.
Usually, a rowid value uniquely identifies a row in the database. However, rows in
different tables that are stored together in the same cluster can have the same rowid.

ROWID 是一个伪列，在单表中不会重复，不同的表可能会有相同的rowid.

oracle 默认排序问题 和 order by ，rowid

b.关于order by 或者Oracle对于输出结果的默认排序处理。

Neither Oracle nor SQL Server will guarantee a sort order in your SELECT statement unless you also include an ORDER BY clause.

That the order the records were inserted into the table is not necessarily the order in which the data comes out.

Even SQL Server can show this "problem." Use the ORDER BY clause in both Oracle and SQL Server if you want to ensure the ordering of your result set

大意就是说：Oracle 和SQL Server都不会保证SELECT语句的输出顺序，除非包含了ORDER BY 条件。

如果要确保输出顺序，请直接指定ORDER BY 。

也就是说，你的输出结果看起来和id或者rowid或者creation_date一致，只不过是恰好一致而已。