Hi Tom,

I have a table with 4 to 5 lakhs records. This column is having low cardinality. When I use this column in order by clause,
with Row limiting clause oracle will return same set of records for different offset values.

But I am expecting different different set of records. Can you help me to fix this issue.

Please find sample script below,

Create Sequence DummySeq Start with 1000 increment by 1;

Create Table PaginationTest (PKCol Number(10) Primary Key,StatusCol Number(1));
Declare
Type NumArray Is Table Of Number(1) Index By PLS_INTEGER;
L_NumArry NumArray;
j int;
Begin
j:=1;
Select rownum tmprownum Bulk Collect into L_NumArry from dual connect by rownum<5;
While(j<100000)
Loop
ForAll i in 1..L_NumArry.count
Insert into PaginationTest Values(DummySeq.NextVal,L_NumArry(i));
j:=j+1;
End Loop;
End;
/

SQL> select StatusCol , count(1) from PaginationTest Group By StatusCol;

STATUSCOL COUNT(1)
---------- ----------
1 99999
2 99999
4 99999
3 99999

SQL> select a.*,rowid from PaginationTest a order by StatusCol offset 1 rows fetch next 20 rows only;

PKCOL STATUSCOL ROWID
---------- ---------- ------------------
1080 1 AAAWqAAAJAAAACzABQ
1076 1 AAAWqAAAJAAAACzABM
1072 1 AAAWqAAAJAAAACzABI
1068 1 AAAWqAAAJAAAACzABE
1064 1 AAAWqAAAJAAAACzABA
1060 1 AAAWqAAAJAAAACzAA8
1056 1 AAAWqAAAJAAAACzAA4
1052 1 AAAWqAAAJAAAACzAA0
1048 1 AAAWqAAAJAAAACzAAw
1044 1 AAAWqAAAJAAAACzAAs
1040 1 AAAWqAAAJAAAACzAAo
1036 1 AAAWqAAAJAAAACzAAk
1032 1 AAAWqAAAJAAAACzAAg
1028 1 AAAWqAAAJAAAACzAAc
1024 1 AAAWqAAAJAAAACzAAY
1020 1 AAAWqAAAJAAAACzAAU
1016 1 AAAWqAAAJAAAACzAAQ
1012 1 AAAWqAAAJAAAACzAAM
1008 1 AAAWqAAAJAAAACzAAI
1004 1 AAAWqAAAJAAAACzAAE

20 rows selected.

SQL> select a.*,rowid from PaginationTest a order by StatusCol offset 20 rows fetch next 20 rows only;

PKCOL STATUSCOL ROWID
---------- ---------- ------------------
1080 1 AAAWqAAAJAAAACzABQ
1076 1 AAAWqAAAJAAAACzABM
1072 1 AAAWqAAAJAAAACzABI
1068 1 AAAWqAAAJAAAACzABE
1064 1 AAAWqAAAJAAAACzABA
1060 1 AAAWqAAAJAAAACzAA8
1056 1 AAAWqAAAJAAAACzAA4
1052 1 AAAWqAAAJAAAACzAA0
1048 1 AAAWqAAAJAAAACzAAw
1044 1 AAAWqAAAJAAAACzAAs
1040 1 AAAWqAAAJAAAACzAAo
1036 1 AAAWqAAAJAAAACzAAk
1032 1 AAAWqAAAJAAAACzAAg
1028 1 AAAWqAAAJAAAACzAAc
1024 1 AAAWqAAAJAAAACzAAY
1020 1 AAAWqAAAJAAAACzAAU
1016 1 AAAWqAAAJAAAACzAAQ
1012 1 AAAWqAAAJAAAACzAAM
1008 1 AAAWqAAAJAAAACzAAI
1004 1 AAAWqAAAJAAAACzAAE

20 rows selected.

If you see both queries return same results. But as per my understanding Row limiting clause should return different set of rows for each offset values.

I tried with "With Ties" , when I use this clause Oracle returns more than 20 records. But I am expecting always 20 records.

Thanks in Advance.
Regards,
S Ranga Prasad

SQL> -- non-determinstic ordering; can get the same rows
SQL> select a.*,rowid
  2  from   PaginationTest a
  3  order  by StatusCol
  4  offset 1 rows fetch next 5 rows only;

     PKCOL  STATUSCOL ROWID
---------- ---------- ------------------
      2836          1 AAAd9+AAJAAAJTjAAV
      2832          1 AAAd9+AAJAAAJTjAAR
      2828          1 AAAd9+AAJAAAJTjAAN
      2824          1 AAAd9+AAJAAAJTjAAJ
      2820          1 AAAd9+AAJAAAJTjAAF

SQL>
SQL> select a.*,rowid
  2  from   PaginationTest a
  3  order by StatusCol
  4  offset 6 rows fetch next 5 rows only;

     PKCOL  STATUSCOL ROWID
---------- ---------- ------------------
      2836          1 AAAd9+AAJAAAJTjAAV
      2832          1 AAAd9+AAJAAAJTjAAR
      2828          1 AAAd9+AAJAAAJTjAAN
      2824          1 AAAd9+AAJAAAJTjAAJ
      2820          1 AAAd9+AAJAAAJTjAAF

SQL>
SQL> -- with determinstic ordering will get different
SQL> select a.*,rowid
  2  from   PaginationTest a
  3  order  by StatusCol , pkcol
  4  offset 1 rows fetch next 5 rows only;

     PKCOL  STATUSCOL ROWID
---------- ---------- ------------------
      1004          1 AAAd9+AAJAAAJTlAAE
      1008          1 AAAd9+AAJAAAJTlAAI
      1012          1 AAAd9+AAJAAAJTlAAM
      1016          1 AAAd9+AAJAAAJTlAAQ
      1020          1 AAAd9+AAJAAAJTlAAU

SQL>
SQL> select a.*,rowid
  2  from   PaginationTest a
  3  order by StatusCol , pkcol
  4  offset 6 rows fetch next 5 rows only;

     PKCOL  STATUSCOL ROWID
---------- ---------- ------------------
      1024          1 AAAd9+AAJAAAJTlAAY
      1028          1 AAAd9+AAJAAAJTlAAc
      1032          1 AAAd9+AAJAAAJTlAAg
      1036          1 AAAd9+AAJAAAJTlAAk
      1040          1 AAAd9+AAJAAAJTlAAo