1

I've created a table that has 1.8m rows and, while I can extract to CSV, it cannot be opened. This is not a query that is ran often and the end users have very low technical ability. Therefore I wanted a quick and dirty fix that had little time investment.

My solution was to split the query into chunks:

SELECT  *
  FROM  my_table 
 WHERE  ROWNUM BETWEEN 0 AND 500000

The above works fine however,

SELECT  *
  FROM  my_table
 WHERE  ROWNUM BETWEEN 500001 AND 1000000

the next iteration does NOT.

I've checked that there are definitely 1.8m rows.

What's going on here? What am I missing?

MT0
  • 86,097
  • 7
  • 42
  • 90
Sid
  • 57
  • 4

1 Answers1

3

Rownum is applied on query result, not on the table. Apply rownum to get first 1m rows and then filter out the first 0.5m.

select *
from (
  SELECT  t.*, rownum rn
  FROM  my_table t
  WHERE  ROWNUM BETWEEN 0 AND 1000000
) where rn > 500000

Also, as pointed out by @Connor in comments, rownum filter by itself may not produce deterministic results. Use an order by clause to apply appropriate ordering. In that case, the SQL changes to:

select *
from (
    SELECT  t.*, rownum rn
    FROM (
        select *
        from my_table
        order by x, y, z -- apply appropriate sort order here
    ) t
    WHERE  ROWNUM BETWEEN 0 AND 1000000
) where rn > 500000

This is becomes tedious to write. In Oracle 12c+, the new FETCH FIRST/NEXT syntax is much more concise:

select *
from my_table
order by x, y, z -- apply appropriate sort order here
offset 500000 rows
fetch next 500000 rows only;
Gurwinder Singh
  • 35,652
  • 5
  • 39
  • 62