没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
对一个大表进行UPDATE,DELETE,如果在一个SESSION里面运行SQL,很容易引发undo不够,
或者由于一些原因,导致回滚,这个是灾难
我们可以对表按照ROWID分片,然后开启多个进程并行的运行,
这样既能提升处理速度,还能减少undo,还能防止死事物恢复太慢。
下面脚本(不支持分区表)要求输入3个参数
1.ROWID分片个数
2.表名字
3.OWNER
select 'where rowid between ''' ||sys.dbms_rowid.rowid_create(1, d.oid, c.fid1, c.bid1, 0) ||''' and ''' ||sys.dbms_rowid.rowid_create(1, d.oid, c.fid2, c.bid2, 9999) || '''' ||';'
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,
或者由于一些原因,导致回滚,这个是灾难
我们可以对表按照ROWID分片,然后开启多个进程并行的运行,
这样既能提升处理速度,还能减少undo,还能防止死事物恢复太慢。
下面脚本(不支持分区表)要求输入3个参数
1.ROWID分片个数
2.表名字
3.OWNER
select 'where rowid between ''' ||sys.dbms_rowid.rowid_create(1, d.oid, c.fid1, c.bid1, 0) ||''' and ''' ||sys.dbms_rowid.rowid_create(1, d.oid, c.fid2, c.bid2, 9999) || '''' ||';'
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,
资源评论
三朝看客
- 粉丝: 177
- 资源: 109
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功