Chinaunix首页 | 论坛 | 博客
  • 博客访问: 5175655
  • 博文数量: 1004
  • 博客积分: 8199
  • 博客等级: 中将
  • 技术积分: 13042
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-25 20:19
个人简介

脚踏实地、勇往直前!

文章分类

全部博文(1004)

文章存档

2020年(1)

2019年(93)

2018年(208)

2017年(81)

2016年(49)

2015年(50)

2014年(170)

2013年(52)

2012年(177)

2011年(93)

2010年(30)

分类: Oracle

2012-06-11 15:37:58

环境:
OS:Red Hat Linux As 5
DB:10.2.0.4
 
通常情况下我们更新数据量比较大的表的时候,通常会使用Oracle中Bulk Collect做批量更新,但我们按照表数据行的ROWID,按照ROWID将表数据分成几批,然后通过ROWID更新表的数据.
 
1.创建表
Create Table tb_my_objects
As
Select * From dba_objects;
 
2.获取表数据的起始ROWID和截止ROWID
Declare
  l_Job Number;
Begin
  dbms_output.put_line('Begin_Rowid'||'------------'||'End_Rowid');
  For x In (Select Dbms_Rowid.Rowid_Create(1,
                                           Data_Object_Id,
                                           Lo_Fno,
                                           Lo_Block,
                                           0) Min_Rid,
                   Dbms_Rowid.Rowid_Create(1,
                                           Data_Object_Id,
                                           Hi_Fno,
                                           Hi_Block,
                                           10000) Max_Rid
              From (Select Distinct Grp,
                                    First_Value(Relative_Fno) Over(Partition By Grp Order By Relative_Fno, Block_Id Rows Between Unbounded Preceding And Unbounded Following) Lo_Fno,
                                    First_Value(Block_Id) Over(Partition By Grp Order By Relative_Fno, Block_Id Rows Between Unbounded Preceding And Unbounded Following) Lo_Block,
                                    Last_Value(Relative_Fno) Over(Partition By Grp Order By Relative_Fno, Block_Id Rows Between Unbounded Preceding And Unbounded Following) Hi_Fno,
                                    Last_Value(Block_Id + Blocks - 1) Over(Partition By Grp Order By Relative_Fno, Block_Id Rows Between Unbounded Preceding And Unbounded Following) Hi_Block,
                                    Sum(Blocks) Over(Partition By Grp) Sum_Blocks
                      From (Select Relative_Fno,
                                   Block_Id,
                                   Blocks,
                                   Trunc((Sum(Blocks)
                                          Over(Order By Relative_Fno, Block_Id) - 0.01) /
                                         (Sum(Blocks) Over() / 8)) Grp
                              From Dba_Extents
                             Where Segment_Name = Upper('TB_MY_OBJECTS')
                               And Owner = User
                             Order By Block_Id)),
                   (Select Data_Object_Id
                      From User_Objects
                     Where Object_Name = Upper('TB_MY_OBJECTS'))) Loop
  
   dbms_output.put_line(x.min_rid||'----'||x.max_rid);
  End Loop;
End;
Begin_Rowid------------End_Rowid
AAAZWuAABAAAX8AAAA----AAAZWuAABAAAX9/CcQ
AAAZWuAABAAAYAAAAA----AAAZWuAABAAAYD/CcQ
AAAZWuAABAAAX0AAAA----AAAZWuAABAAAX1/CcQ
AAAZWuAABAAAX2AAAA----AAAZWuAABAAAX3/CcQ
AAAZWuAABAAAXuIAAA----AAAZWuAABAAAXyHCcQ
AAAZWuAABAAAX4AAAA----AAAZWuAABAAAX5/CcQ
AAAZWuAABAAAX+AAAA----AAAZWuAABAAAX//CcQ
AAAZWuAABAAAX6AAAA----AAAZWuAABAAAX7/CcQ
 
3.更新某个rowid范围的表数据(其他的做同样的操作)
Update tb_my_objects tt
  Set tt.last_ddl_time=Sysdate
Where Rowid Between 'AAAZWuAABAAAX8AAAA' and 'AAAZWuAABAAAX9/CcQ';
 
说明:
11G已经提供了dbms_parallel_execute分批处理数据的过程.
 
-- The End --
阅读(5839) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~