在Oracle中手工对任务进行分区的方法


1. 根据dba_extents中的rowid分布,来得到需要处理的表的rowid范围以进行手工分区.

01 CREATE OR REPLACE PROCEDURE  get_table_chunk_rowids2 (p_schema VARCHAR2, p_table VARCHAR2,
02    p_chunks NUMBER, p_cur_chunk NUMBER,
03    p_min_rowid OUT VARCHAR2, p_max_rowid OUT VARCHAR2
04    )
05 AS
06 BEGIN
07  SELECT q.r1, q.r2
08    INTO p_min_rowid, p_max_rowid
09  FROM
10  (
11   SELECT rownum rn
12       ,  sys.DBMS_ROWID.rowid_create (1, d.oid, c.fid1, c.bid1, 0) r1
13       ,  sys.DBMS_ROWID.rowid_create (1, d.oid, c.fid2, c.bid2, 9999) r2
14   FROM (SELECT DISTINCT
15          b.rn,
16          FIRST_VALUE (a.fid)
17           OVER ( PARTITION BY b.rn
18           ORDER BY a.fid, a.bid
19           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fid1,
20          LAST_VALUE (a.fid)
21           OVER ( PARTITION BY b.rn
22           ORDER BY a.fid, a.bid
23           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fid2,
24          FIRST_VALUE (
25      DECODE (SIGN (range2 - range1),
26       1, a.bid + ( (b.rn - a.range1) * a.chunks1),
27       a.bid) )
28           OVER (
29       PARTITION BY b.rn
30       ORDER BY a.fid, a.bid
31       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) bid1,
32          LAST_VALUE (
33      DECODE (
34         SIGN (range2 - range1),
35         1, a.bid + ( (b.rn - a.range1 + 1) * a.chunks1) - 1,
36         (a.bid + a.blocks - 1)))
37           OVER (
38       PARTITION BY b.rn
39       ORDER BY a.fid, a.bid
40       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) bid2
41     FROM (SELECT fid,
42           bid,
43           blocks,
44           chunks1,
45           TRUNC ( (sum2 - blocks + 1 - 0.1) / chunks1) range1,
46           TRUNC ( (sum2 - 0.1) / chunks1) range2
47      FROM (SELECT /*+ rule */
48            relative_fno fid,
49            block_id bid,
50            blocks,
51            SUM (blocks) OVER () sum1,
52            TRUNC ( (SUM (blocks) OVER ()) / p_chunks) chunks1,
53            SUM (blocks) OVER (ORDER BY relative_fno, block_id) sum2
54       FROM dba_extents
55      WHERE segment_name = UPPER (p_table) AND owner = UPPER(p_schema))
56     WHERE sum1 > p_chunks) a,
57          (    SELECT ROWNUM - 1 rn
58          FROM DUAL
59    CONNECT BY LEVEL <= p_chunks) b
60    WHERE b.rn BETWEEN a.range1 AND a.range2) c,
61   (SELECT MAX (data_object_id) oid
62     FROM dba_objects
63    WHERE     object_name = UPPER (p_table) AND owner = UPPER (p_schema)
64          AND data_object_id IS NOT NULL) d
65   ORDER BY d.oid, c.fid1, c.bid1
66  ) q
67  WHERE q.rn = p_cur_chunk;
68 END;

Jonathan Lewis也有类似的想法.

01 create table t1
02 pctfree 95
03 pctused  5
04 as
05 with generator as (
06     select  --+ materialize
07         rownum id
08     from dual
09     connect by
10         rownum <= 10000
11 )
12 select
13     rownum          id,
14     lpad(rownum,10,'0') small_vc,
15     rpad('x',100)       padding
16 from
17     generator   v1,
18     generator   v2
19 where
20     rownum <= 10000
21 ;
22   
23 select
24     data_object_id
25 from    user_objects
26 where   object_name = 'T1'
27 ;
28   
29 select
30     extent_id, file_id, block_id, blocks
31 from
32     dba_extents
33 where
34     owner = 'TEST_USER'
35 and segment_name = 'T1'
36 order by
37     extent_id
38 ;
39   
40 set serveroutput off
41 set linesize 180
42   
43 with rowid_range as (
44     select
45         /*+ materialize */
46         dbms_rowid.rowid_create(
47             1,
48             &m_object,
49             file_id,
50             block_id,0
51         )   low_rowid,
52         dbms_rowid.rowid_create(
53             1,
54             &m_object,
55             file_id,
56             block_id+blocks-1,
57             4095
58         )   high_rowid
59     from
60         dba_extents
61     where
62         owner       = 'TEST_USER'
63     and segment_name    = 'T1'
64     and extent_id   = 8
65 )
66 select
67     /*+
68         gather_plan_statistics
69         ordered
70         use_nl(t1)
71         rowid(t1)
72     */
73     t1.rowid,
74     t1.small_vc
75 from
76     rowid_range rr,
77     t1
78 where
79     t1.rowid between rr.low_rowid and rr.high_rowid
80 ;
81   
82 select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

2. 我自己在工作为更新做的手工分任务的方法.

 
01 create table batch_job tablespace xxx as
02 select mod(rownum,50/* batch size*/) batch_id,id pk_id
03 from target_table
04 order by rowid;
05   
06 create table batch_status tablespace xxx as
07 select batch_id,,0 flag/*imply is this batch processed or not processed*/
08 from (
09   select distinct batch_id from batch_job
10 );
11   
12 create batch_job_bid_ind on batch_job(batch_id) tablespace xxx;
13 create batch_status_pk on batch_status(batch_id) tablespace xxx;
14   
15 create or replace procedure process_batch as
16   v_batch_id number;
17 begin
18   select batch_id into v_batch_id from batch_status where flag = 0 and rownum <= 1 skip locked;
19   for rs in (select xxxx from batch_job a,target_table b where a.pk_id = b.id and a.batch_id = v_batch_id)
20   loop
21      statement processing;
22   end loop;
23   commit;
24 end;
25 /
26   
27 --对此procedure 稍作修改, 就可以实现使用此procedure 来实现多个进程并发修改数据了. 不过前提是这个数据可以并行去处理,相互之间不要有什么依赖.

3. 如果你的数据库版本为11g, 可以使用DBMS_PARALLEL_EXECUTE来拆分任务.

相关内容