这是对 这个问题 的后续解答，原问题展示了症状，但未解释内部机制。

前提条件：

CREATE TABLE t (x INT, name VARCHAR(100));

INSERT INTO t
SELECT level,
       CASE WHEN MOD(level,2)=0 THEN 'CDR' ELSE 'SDS' END
  FROM dual
CONNECT BY level <= 10000;

COMMIT;

这将产生10,000行。 x 是唯一的（1–10000）。 name 只有恰好两个不同的值：5000行来自 CDR（偶数 x）和5000行来自 SDS（奇数 x）。

问题

这两个查询返回完全相同的10行：

SELECT *
FROM t
ORDER BY name 
    OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT *
FROM t
ORDER BY name 
    OFFSET 11 ROWS FETCH NEXT 10 ROWS ONLY;

实际输出（来自这两个查询）：

X     NAME
-----------
1706  CDR
1704  CDR
1702  CDR
1700  CDR
1698  CDR
1696  CDR
1694  CDR
1692  CDR
1690  CDR
1688  CDR

两者查询返回相同的 x 值。OFFSET 1 应返回排名2–11，OFFSET 11 应返回排名12–21 —— 这些本应是完全不同的行。

提问

为何把 OFFSET 1 改为 OFFSET 11 会返回完全相同的实际行？

DB Fiddle: https://dbfiddle.uk/NVFLMA0e

解决方案

这是对这个问题的后续回答，展示了症状，但并未解释内部机制。
OFFSET-FETCH 是一种语法糖的示例。一个展开的查询：

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          select *
  from t
 order by name offset 1 rows fetch next 10 rows only',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

查询1：

SELECT "A1"."X" "X","A1"."NAME" "NAME" 
FROM  (
  SELECT "A2"."X" "X","A2"."NAME" "NAME","A2"."NAME" "rowlimit_$_0",
     ROW_NUMBER() OVER ( ORDER BY "A2"."NAME") "rowlimit_$$_rownumber" 
  FROM "T" "A2") "A1" 
WHERE "A1"."rowlimit_$$_rownumber"<= CASE  WHEN (1>=0) 
    THEN FLOOR(TO_NUMBER(1)) ELSE 0 END +10 
  AND "A1"."rowlimit_$$_rownumber">1 
ORDER BY "A1"."rowlimit_$_0"

查询2：

SELECT "A1"."X" "X","A1"."NAME" "NAME" 
FROM  (
    SELECT "A2"."X" "X","A2"."NAME" "NAME","A2"."NAME" "rowlimit_$_0",
      ROW_NUMBER() OVER ( ORDER BY "A2"."NAME") "rowlimit_$$_rownumber" 
    FROM "T" "A2") "A1" 
WHERE "A1"."rowlimit_$$_rownumber"<=CASE  WHEN (11>=0)  
       THEN FLOOR(TO_NUMBER(11)) ELSE 0 END +10 
 AND "A1"."rowlimit_$$_rownumber">11 
ORDER BY "A1"."rowlimit_$_0"

db<>fiddle演示

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      | 10000 |  1269K|       |   165   (1)| 00:00:01 |
|*  1 |  VIEW                    |      | 10000 |  1269K|       |   165   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|      | 10000 |   634K|   760K|   165   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T    | 10000 |   634K|       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

如果内部查询被物化，谓词下推将不会发生：

WITH "A1" AS (
   SELECT /*+ MATERIALIZE */
    "A2"."X" "X","A2"."NAME" "NAME","A2"."NAME" "rowlimit_$_0",
     ROW_NUMBER() OVER ( ORDER BY "A2"."NAME") "rowlimit_$$_rownumber" 
  FROM "T" "A2"
)
SELECT "A1"."X" "X","A1"."NAME" "NAME","rowlimit_$$_rownumber" 
FROM  "A1" 
  WHERE "A1"."rowlimit_$$_rownumber"<= CASE  WHEN (1>=0) 
    THEN FLOOR(TO_NUMBER(1)) ELSE 0 END +10 AND "A1"."rowlimit_$$_rownumber">1 
ORDER BY "A1"."rowlimit_$_0"

+-------+-------+-----------------------+
|  X    | NAME  | rowlimit_$$_rownumber |
+-------+-------+-----------------------+
| 1688  | CDR   |                     2 |
| 1690  | CDR   |                     3 |
| 1692  | CDR   |                     4 |
| 1694  | CDR   |                     5 |
| 1706  | CDR   |                    11 |
| 1698  | CDR   |                     7 |
| 1700  | CDR   |                     8 |
| 1702  | CDR   |                     9 |
| 1704  | CDR   |                    10 |
| 1696  | CDR   |                     6 |
+-------+-------+-----------------------+

+-------+-------+-----------------------+
|  X    | NAME  | rowlimit_$$_rownumber |
+-------+-------+-----------------------+
| 1708  | CDR   |                    12 |
| 1710  | CDR   |                    13 |
| 1712  | CDR   |                    14 |
| 1714  | CDR   |                    15 |
| 1726  | CDR   |                    21 |
| 1718  | CDR   |                    17 |
| 1720  | CDR   |                    18 |
| 1722  | CDR   |                    19 |
| 1724  | CDR   |                    20 |
| 1716  | CDR   |                    16 |
+-------+-------+-----------------------+

建议使用确定性排序。