Fetching records in buckets

This is not a pagination but fetching records in buckets form. Meaning, if you have a table that has huge number of rows and wants to process them in buckets with multiple sessions simultaneously by dividing the rows.

Using analytic function NTAIL:

create table san_bucket_test (col1 number, col2 varchar2(30), statusvarchar2(20));

begin

  for i in 1..20 loop

    insert into san_bucket_test values (i, 'Record # '||i, 'PENDING');

  end loop;

end;

/

select *

  from (

        select sbt.*, (ntile(4) over (order by sbt.col1)) bucket

          from san_bucket_test sbt

         where status = 'PENDING'

       )

 where bucket =3;

As you see above, the above NTILE function with argument 4 divides the rows into 4 buckets and the outer join with bucket=3 will only gets the data for 3rd bucket.