Splitting PLSQL result

Question

I'm trying to fetch data from a database using PLSQL Developer, the total of rows that need to be fetched is more than 1,5 million. When I tried to fetch the data all together it will really take a long time. I'm going to split it into two fetching phase, the 1st one, rows 1 - 1million and the rests go to the 2nd phase.
How could I do this in PLSQL ?

Answer 1

This select numbers each row using the analitical function so you can query by row numbers;

SELECT *
FROM
(
SELECT *,
       ROW_NUMBER() OVER(ORDER BY id_column_here) r
  FROM my_table
)
WHERE R<=100000;

You can use this with smaller row intervals to retrieve first 1000 then the next and so on :

SELECT *
FROM
(
SELECT *,
       ROW_NUMBER() OVER(ORDER BY id_column_here) r
  FROM my_table
)
WHERE R between 1000 and 2000;