Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
0


create table t(x int, name varchar(100))
insert 
  into t
 select level,case when mod(level,2)=0 then 'CDR' else 'SDS' end as col2
   from dual
 connect by level<=10000
10000 rows affected

select *
  from t
 order by name offset 1 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


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


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 rows affected

dbms_output:
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 "FIDDLE_JMUNMOCZGNPDJJXMWIQR"."T" "A2") "A1" WHERE "A1"."rowlimit_$$_rownumber"<=GREATEST(FLOOR(TO_NUMBER(1)),0)+10 AND "A1"."rowlimit_$$_rownumber">1 ORDER BY "A1"."rowlimit_$_0"

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

  dbms_output.put_line(x);
end;
/
1 rows affected

dbms_output:
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 "FIDDLE_JMUNMOCZGNPDJJXMWIQR"."T" "A2") "A1" WHERE "A1"."rowlimit_$$_rownumber"<=GREATEST(FLOOR(TO_NUMBER(11)),0)+10 AND "A1"."rowlimit_$$_rownumber">11 ORDER BY "A1"."rowlimit_$_0"

SELECT "A1"."X" "X","A1"."NAME" "NAME","rowlimit_$$_rownumber" 
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"


X
NAME
rowlimit_$$_rownumber


1706
CDR
2

1704
CDR
3

1702
CDR
4

1700
CDR
5

1698
CDR
6

1696
CDR
7

1694
CDR
8

1692
CDR
9

1690
CDR
10

1688
CDR
11


SELECT "A1"."X" "X","A1"."NAME" "NAME","rowlimit_$$_rownumber" 
  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"


X
NAME
rowlimit_$$_rownumber


1706
CDR
12

1704
CDR
13

1702
CDR
14

1700
CDR
15

1698
CDR
16

1696
CDR
17

1694
CDR
18

1692
CDR
19

1690
CDR
20

1688
CDR
21


EXPLAIN PLAN FOR
SELECT "A1"."X" "X","A1"."NAME" "NAME","rowlimit_$$_rownumber" 
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"
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));


PLAN_TABLE_OUTPUT


Plan hash value: 3047187157

 

-----------------------------------------------------------------------------------------

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

-----------------------------------------------------------------------------------------

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - SEL$2 / "A1"@"SEL$1"

   2 - SEL$2

   3 - SEL$2 / "A2"@"SEL$2"

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("A1"."rowlimit_$$_rownumber"<=CASE  WHEN (1>=0) THEN 1 ELSE 0 END

              +10 AND "A1"."rowlimit_$$_rownumber">1)

   2 - filter(ROW_NUMBER() OVER ( ORDER BY "A2"."NAME")<=CASE  WHEN (1>=0) THEN

              1 ELSE 0 END +10)

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - (rowset=256) "A1"."X"[NUMBER,22], "A1"."NAME"[VARCHAR2,100],

       "A1"."rowlimit_$$_rownumber"[NUMBER,22]

   2 - (#keys=1; rowset=256) "A2"."NAME"[VARCHAR2,100], "A2"."X"[NUMBER,22],

       ROW_NUMBER() OVER ( ORDER BY "A2"."NAME")[22]

   3 - (rowset=256) "A2"."X"[NUMBER,22], "A2"."NAME"[VARCHAR2,100]

 

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)


EXPLAIN PLAN FOR
SELECT "A1"."X" "X","A1"."NAME" "NAME","rowlimit_$$_rownumber" 
  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"
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));


PLAN_TABLE_OUTPUT


Plan hash value: 3047187157

 

-----------------------------------------------------------------------------------------

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

-----------------------------------------------------------------------------------------

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - SEL$2 / "A1"@"SEL$1"

   2 - SEL$2

   3 - SEL$2 / "A2"@"SEL$2"

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("A1"."rowlimit_$$_rownumber"<=CASE  WHEN (11>=0) THEN 11 ELSE 0

              END +10 AND "A1"."rowlimit_$$_rownumber">11)

   2 - filter(ROW_NUMBER() OVER ( ORDER BY "A2"."NAME")<=CASE  WHEN (11>=0) THEN

              11 ELSE 0 END +10)

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - (rowset=256) "A1"."X"[NUMBER,22], "A1"."NAME"[VARCHAR2,100],

       "A1"."rowlimit_$$_rownumber"[NUMBER,22]

   2 - (#keys=1; rowset=256) "A2"."NAME"[VARCHAR2,100], "A2"."X"[NUMBER,22],

       ROW_NUMBER() OVER ( ORDER BY "A2"."NAME")[22]

   3 - (rowset=256) "A2"."X"[NUMBER,22], "A2"."NAME"[VARCHAR2,100]

 

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)


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


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 (11>=0) 
    THEN FLOOR(TO_NUMBER(11)) ELSE 0 END +10 AND "A1"."rowlimit_$$_rownumber">11 
ORDER BY "A1"."rowlimit_$_0"


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
X	NAME
1706	CDR
1704	CDR
1702	CDR
1700	CDR
1698	CDR
1696	CDR
1694	CDR
1692	CDR
1690	CDR
1688	CDR
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