Database Reference
In-Depth Information
This will allow us to just pass the job ID into our
SERIAL
procedure, so it can query this table to get the rowid
range it is to process. Now, for our procedure (the code in
bold
is the new code we'll be adding):
EODA@ORA12CR1> create or replace
2 procedure serial( p_job in number )
3 is
4 l_rec job_parms%rowtype;
5 begin
6 select * into l_rec
7 from job_parms
8 where job = p_job;
9
10 for x in ( select object_id id, object_name text
11 from big_table
12 where rowid between l_rec.lo_rid
13 and l_rec.hi_rid )
14 loop
15 -- complex process here
16 insert into t2 (id, text, session_id )
17 values ( x.id, x.text, p_job );
18 end loop;
19
20 delete from job_parms where job = p_job;
21 commit;
22 end;
23 /
Procedure created.
As you can see, it is not a significant change. Most of the added code was simply to get our inputs and the rowid
range to process. The only change to our
logic
was the change in the predicate on lines 12 and 13.
Now let's schedule our job. We'll use a rather complex query using analytics to divide the table. The innermost
query on lines 19 through 26 breaks the data into eight groups in this case. The first sum on line 22 is computing a
running total of the sum of blocks; the second sum on line 23 is the total number of blocks. If we integer divide the
running total by the desired chunk size (the total size divided by 8 in this case), we can create groups of files/blocks
that cover about the same amount of data. The query on lines 8 through 28 finds the high and low file numbers and
block numbers by
GRP
and returns the distinct entries. It builds the inputs we can then send to
DBMS_ROWID
to create
the rowids Oracle wants. We take that output and, using
DBMS_JOB
, submit a job to process the rowid range:
EODA@ORA12CR1> declare
2 l_job number;
3 begin
4 for x in (
5 select dbms_rowid.rowid_create
( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,
6 dbms_rowid.rowid_create
( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid
7 from (
8 select distinct grp,
9 first_value(relative_fno)
over (partition by grp order by relative_fno, block_id
10 rows between unbounded preceding and unbounded following) lo_fno,
