Database Reference
In-Depth Information
Old School Do-It-Yourself Parallelism
Prior to Oracle Database 11
g
Release 2, you can implement a parallelization approach similar to that in the previous
section. You won't have a rich API to support you, and you'll need to do more of the tedious work yourself, but it can
be done. My approach many times has been to use rowid ranges to break the table up into some number of ranges
that don't overlap (yet completely cover the table).
■
if you are running oracle Database 11
g
release 2 or higher, refer to the previous section for an example using
the
DBMS_PARALLEL_EXECUTE
package. if it's available to you, you really should be using that package rather than the
manual approach described here.
Note
This manually intensive approach is very similar to how Oracle performs a parallel query conceptually. If you
think of a full table scan, Oracle processes it by coming up with some method to break the table into many small
tables, each of which is processed by a parallel execution server. We are going to do the same thing using rowid ranges.
In early releases, Oracle's parallel implementation actually used rowid ranges itself.
Again, we'll use a
BIG_TABLE
of 1,000,000 rows, as the technique I'm describing works best on big tables with lots
of extents, and the method I use for creating rowid ranges depends on extent boundaries. The more extents used, the
better the data distribution. So, after creating the
BIG_TABLE
with 1,000,000 rows, we'll create
T2
like this:
EODA@ORA12CR1> create table t2
2 as
3 select object_id id, object_name text, 0 session_id
4 from big_table
5 where 1=0;
Table created.
We are going to use the job queues built into the database to parallel process our procedure. We will schedule
some number of jobs. Each job is our procedure slightly modified to just process the rows in a given rowid range.
■
in oracle 10
g
and above, you could also use the scheduler for something so simple. in order to make the
example 9
i
compatible, we'll use the job queues here.
Note
To efficiently support the job queues, we'll use a parameter table to pass inputs to our jobs:
EODA@ORA12CR1> create table job_parms
2 ( job number primary key,
3 lo_rid rowid,
4 hi_rid rowid
5 )
6 /
Table created.
