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
您可能感兴趣的文章:
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。