Parallel Execution - Expert Oracle Database Architecture - page 690

This commit released our jobs for processing. We have JOB_QUEUE_PROCESSES set to 1000 in the parameter file, so
all eight started running and shortly finished. After they all completed, the results are as follows:
EODA@ORA12CR1> select session_id, count(*)
2 from t2
3 group by session_id;
SESSION_ID COUNT(*)
---------- ----------
1 127651
6 124431
2 147606
5 124590
4 72961
8 147544
3 127621
7 127596
8 rows selected.
Suppose, however, that you do not want to use the rowid processing—perhaps the query is not as simple as
SELECT * FROM T and involves joins and other constructs that make using the rowid impractical. You can use the
primary key of some table instead. For example, say you want to break that same BIG_TABLE into ten pieces to be
processed concurrently by primary key. You can do that easily using the NTILE built-in analytic function. The process
is rather straightforward:
EODA@ORA12CR1> select nt, min(id), max(id), count(*)
2 from (
3 select id, ntile(10) over (order by id) nt
4 from big_table
5 )
6 group by nt;
NT MIN(ID) MAX(ID) COUNT(*)
---------- ---------- ---------- ----------
1 1 100000 100000
6 500001 600000 100000
2 100001 200000 100000
5 400001 500000 100000
4 300001 400000 100000
8 700001 800000 100000
3 200001 300000 100000
7 600001 700000 100000
9 800001 900000 100000
10 900001 1000000 100000
10 rows selected.
Now you have ten nonoverlapping primary key ranges (all of nice equal size) that you can use to implement the
same DBMS_JOB technique as shown earlier to parallelize your process.