巧用rowid批量操作数据

1、rowid简介

索引组织表：有且仅有一个聚簇索引键，数据按照聚簇索引键排序，所以数据是有序的，插入也是有序的。项目中一般情况下主键是非聚集索引，因此rowid是有序且是聚集索引，因此有些大表dml操作可以借用rowid去批量做来提升性能。

2、案例

项目中有些表经常查询，并且已增量很多数据，需要对比过去时间的数据进行归档，利用rowid可以加快数据归档的速度

2.1 测试数据准备

create table base1(idvarchar2(20)primary key,project_idvarchar2(20),ptypevarchar2(20),dflag int,ctime timestamp)partition bylist(ptype)(partition p_1values('1'),partition p_2values('2'),partition p_3values('3'),partition p_4values('4'),partition p_5values('5'),partition p_6values('6'),partition p_7values('7'),partition p_8values('8'),partition p_9values('9'));insert into base1 select'A'||level,'A'||to_char(round(dbms_random.value(1,10000),0)),to_char(round(dbms_random.value(1,9),0)),round(dbms_random.value(1,1),0),sysdate-round(dbms_random.value(1,360),0)from dual connect by level<=1000000;commit;insert into base1 select'A'||level+1000000,'A'||to_char(round(dbms_random.value(1,10000),0)),to_char(round(dbms_random.value(1,9),0)),round(dbms_random.value(1,1),0),sysdate-round(dbms_random.value(1,360),0)from dual connect by level<=1000000;commit;insert into base1 select'A'||level+2000000,'A'||to_char(round(dbms_random.value(1,10000),0)),to_char(round(dbms_random.value(1,9),0)),round(dbms_random.value(1,1),0),sysdate-round(dbms_random.value(1,360),0)from dual connect by level<=1000000;commit;备份表 create table BAK_base1(idvarchar2(20)primary key,project_idvarchar2(20),ptypevarchar2(20),dflag int,ctime timestamp)partition bylist(ptype)(partition p_1values('1'),partition p_2values('2'),partition p_3values('3'),partition p_4values('4'),partition p_5values('5'),partition p_6values('6'),partition p_7values('7'),partition p_8values('8'),partition p_9values('9'));create table BAK_base2(idvarchar2(20)primary key,project_idvarchar2(20),ptypevarchar2(20),dflag int,ctime timestamp)partition bylist(ptype)(partition p_1values('1'),partition p_2values('2'),partition p_3values('3'),partition p_4values('4'),partition p_5values('5'),partition p_6values('6'),partition p_7values('7'),partition p_8values('8'),partition p_9values('9'));

2.2 使用rowid方式/直接插入方式性能对比

将小于2026-01-01的数据插入新的备份表中。

2.2.1 Rowid方式

declare v_sqlvarchar(2000);v_pageno bigint;v_operate_num_max bigint;v_operate_num_min bigint;v_size bigint;cursor cur is select partition_name from dba_tab_partitions where table_name='BASE1';BEGINv_pageno:= 100000;-- 10万条数据循环一次 DBMS_OUTPUT.PUT_LINE(v_pageno);for c in cur loop execute immediate'select SF_GET_REAL_ROWID(max(rowid)), SF_GET_REAL_ROWID(min(rowid)) from BASE1_'||c.partition_name into v_operate_num_max,v_operate_num_min;v_size:=ceil((v_operate_num_max - v_operate_num_min)/(v_pageno-1))+1;for i in 1..v_size loopv_sql:='insert into bak_base1 select * from base1_'||c.partition_name||' where ctime<to_date(''2026-01-01'',''yyyy-mm-dd'') and rowid >= '||v_operate_num_min+(i-1)*v_pageno||' and rowid < '||v_operate_num_min+i*v_pageno;execute immediate v_sql;commit;end loop;end loop;END;执行时间： 执行成功,执行耗时47秒 532毫秒.执行号:4545 100000 影响了0条记录 1条语句执行成功

2.2.2 直接插入

create index IDX_DM_BASE1 onBASE1(CTIME)global;insert into bak_base2 select * from base1 where ctime<to_date('2026-01-01','yyyy-mm-dd');commit;[执行语句1]:insert into bak_base2 select * from base1 where ctime<to_date('2026-01-01','yyyy-mm-dd');执行成功,执行耗时9分 2秒 93毫秒.执行号:4633 影响了2,745,322条记录

对比，rowid方式完胜

3、小结

（1）rowid是聚集索引，通过rowid方式获取数据批量操作可以提升性能。目前也在实际项目中使用。
（2）一次性操作大量数据，也会产生很多undo记录，此时发生宕机，重启就要重做redo，可能要回滚很久。因此化整为零，并且每次用到rowid的聚集索引特性，能够快速达到目的。