Selecting a partial set

[TStriker]

Quick question: I've been tasked with exporting about 140K records in batches of 50K.

SELECT ... where ROWNUM <= 50000 seems to work fine but SELECT ... WHERE ROWNUM > 50000 doesn't return any rows.

Any ideas how to break the set up into chunks of 50,000 rows?

-Striker

 

[SantaMufasa]

Use a subquery to obtain rownumbers. First code chunk, below, is a script, STRIKER_TEST.sql:

accept rowbeg prompt "Enter the beginning row number for the set: "
accept rowend prompt "Enter the    ending row number for the set: "
select *
  from (select rownum rn, d.* from s_dept d)
 where rn between &rowbeg and & rowend
/

Following are three invocations of STRIKER_TEST.sql that illustrate the proof of concept for what you want:

SQL> @striker_test
Enter the beginning row number for the set: 1
Enter the    ending row number for the set: 3

        RN         ID NAME                  REGION_ID
---------- ---------- -------------------- ----------
         1         50 Administration                1
         2         10 Finance                       1
         3         41 Operations                    1

SQL> @striker_test
Enter the beginning row number for the set: 4
Enter the    ending row number for the set: 6

        RN         ID NAME                  REGION_ID
---------- ---------- -------------------- ----------
         4         42 Operations                    2
         5         43 Operations                    3
         6         44 Operations                    4

SQL> @striker_test
Enter the beginning row number for the set: 7
Enter the    ending row number for the set: 999

        RN         ID NAME                  REGION_ID
---------- ---------- -------------------- ----------
         7         45 Operations                    5
         8         31 Sales                         1
         9         32 Sales                         2
        10         33 Sales                         3
        11         34 Sales                         4
        12         35 Sales                         5

I recognize that the code reads the entire result set three times, but the "heavy lifting" (i.e., the limited-set processing) occurs from database-buffer-cached rows.

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Dagon]

Santa,

My only concern about your solution is that you're not putting any "order by" on the query. You can't therefore guarantee that the data is coming back in the same order each time the query is run with different begin and end points. So the same rows could theoretically appear in more than one chunk.

TStriker,

In general, I would caution against an approach where you are running different queries against the table to get each data chunk. Each query will involve a full table scan and sort of the table, so if it is a very large table and the process has to be repeated many times, it could be very inefficient. It would be OK with a table of 140K rows but for a table with, say, 50M rows, it could give dire performance.

You might be better using PL/SQL and looping through the data with a single cursor. You could use counters to keep track of the number of rows read and write them out to a set of files using utl_file. Each time you reach the limit on a counter, you close the current file and open another one. This requires only a single scan of the table and no sort.


For Oracle-related work, contact me through Linked-In.

 

[SantaMufasa]

Correct on all counts, Dagon. I would use PL/SQL to step through a single cursor of data for the "chunks" of rows.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[taupirho]

Have a look at the NTILE function. This can bean excellent way of retreiving non-overlapping sections to split up a table into.


In order to understand recursion, you must first understand recursion.