通过rowid分批更新数据

环境:

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 --