1. 根据dba_extents中的rowid分布,来得到需要处理的表的rowid范围以进行手工分区.
摘自http://www.dautkhanov.com/2010/04/splitting-big-tables-for-parallel.html

CREATE OR REPLACE PROCEDURE  get_table_chunk_rowids2 (p_schema VARCHAR2, p_table VARCHAR2,
   p_chunks NUMBER, p_cur_chunk NUMBER,
   p_min_rowid OUT VARCHAR2, p_max_rowid OUT VARCHAR2
   )
AS
BEGIN
 SELECT q.r1, q.r2
   INTO p_min_rowid, p_max_rowid
 FROM
 (
  SELECT rownum rn
      ,  sys.DBMS_ROWID.rowid_create (1, d.oid, c.fid1, c.bid1, 0) r1
      ,  sys.DBMS_ROWID.rowid_create (1, d.oid, c.fid2, c.bid2, 9999) r2
  FROM (SELECT DISTINCT
         b.rn,
         FIRST_VALUE (a.fid)
          OVER ( PARTITION BY b.rn
          ORDER BY a.fid, a.bid
          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fid1,
         LAST_VALUE (a.fid)
          OVER ( PARTITION BY b.rn
          ORDER BY a.fid, a.bid
          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fid2,
         FIRST_VALUE (
     DECODE (SIGN (range2 - range1),
      1, a.bid + ( (b.rn - a.range1) * a.chunks1),
      a.bid) )
          OVER (
      PARTITION BY b.rn
      ORDER BY a.fid, a.bid
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) bid1,
         LAST_VALUE (
     DECODE (
        SIGN (range2 - range1),
        1, a.bid + ( (b.rn - a.range1 + 1) * a.chunks1) - 1,
        (a.bid + a.blocks - 1)))
          OVER (
      PARTITION BY b.rn
      ORDER BY a.fid, a.bid
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) bid2
    FROM (SELECT fid,
          bid,
          blocks,
          chunks1,
          TRUNC ( (sum2 - blocks + 1 - 0.1) / chunks1) range1,
          TRUNC ( (sum2 - 0.1) / chunks1) range2
     FROM (SELECT 
           relative_fno fid,
           block_id bid,
           blocks,
           SUM (blocks) OVER () sum1,
           TRUNC ( (SUM (blocks) OVER ()) / p_chunks) chunks1,
           SUM (blocks) OVER (ORDER BY relative_fno, block_id) sum2
      FROM dba_extents
     WHERE segment_name = UPPER (p_table) AND owner = UPPER(p_schema))
    WHERE sum1 > p_chunks) a,
         (    SELECT ROWNUM - 1 rn
         FROM DUAL
   CONNECT BY LEVEL 
Jonathan Lewis也有类似的想法.http://jonathanlewis.wordpress.com/2010/01/03/pseudo-parallel/

create table t1
pctfree 95
pctused  5
as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                rownum 
2. 我自己在工作为更新做的手工分任务的方法.

create table batch_job tablespace xxx as
select mod(rownum,50) batch_id,id pk_id
from target_table
order by rowid;

create table batch_status tablespace xxx as
select batch_id,,0 flag
from (
  select distinct batch_id from batch_job
);

create batch_job_bid_ind on batch_job(batch_id) tablespace xxx;
create batch_status_pk on batch_status(batch_id) tablespace xxx;

create or replace procedure process_batch as
  v_batch_id number;
begin
  select batch_id into v_batch_id from batch_status where flag = 0 and rownum 
3. 如果你的数据库版本为11g, 可以使用DBMS_PARALLEL_EXECUTE来拆分任务.


No related posts.