分段表rowid_通过rowid分批更新数据

最新推荐文章于 2024-03-23 09:45:32 发布

彷徨彷徨333

最新推荐文章于 2024-03-23 09:45:32 发布

阅读量546

点赞数

文章标签：分段表rowid

本文链接：https://blog.csdn.net/weixin_42505503/article/details/111900560

版权

本文介绍了在Oracle环境下，如何通过ROWID将数据分段并进行批量更新。首先创建了一个名为tb_my_objects的表，然后展示了如何获取表数据的起始和结束ROWID，并按照ROWID将数据分成多个批次。最后，给出了一段示例代码，演示了如何更新特定ROWID范围内的表数据。11G版本提供了dbms_parallel_execute包来更方便地处理这种分批数据操作。

摘要由CSDN通过智能技术生成

环境:

OS:Red Hat Linux As 5

DB:10.2.0.4

通常情况下我们更新数据量比较大的表的时候,通常会使用Oracle中Bulk Collect做批量更新,但我们按照表数据行的ROWID,按照ROWID将表数据分成几批,然后通过ROWID更新表的数据.

1.创建表

Create Table tb_my_objects

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