在Oracle中手工对任务进行分区的方法
在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来拆分任务.
评论暂时关闭