oracle 分区表行迁移,行迁移对跨分区update效率的影响

测试目的：

行迁移对跨分区update效率的影响。

创建测试表

create table ming.LICISITS_R

(

EID NUMBER(18) not null,

EUTIME DATE  default sysdate ,

dynamicmap VARCHAR2(200) not null

)

PARTITION BY RANGE (EUTIME) INTERVAL (numtoyminterval(1, 'month'))

(partition p1900 values less than(to_date('1900-01-01', 'yyyy-mm-dd')));

alter table ming.LICISITS_R add constraint PK_LICISITS_R primary key(EID) using index ;

alter table ming.LICISITS_R add constraint PK_LG_LICISITS_R unique(dynamicmap) using index ;

开启行迁移

alter table ming.LICISITS_R enable row movement;

每个分区插入10万数据

begin

for i in  1 .. 100000

loop

insert into ming.LICISITS_R values( i,sysdate,i);

end loop;

commit;

end;

/

begin

for i in  100001 .. 200000

loop

insert into ming.LICISITS_R values( i,sysdate-31,i);

end loop;

commit;

end;

/

开启记录时间

set timing on time on

将SYS_P3695分区数据全部update到SYS_P3696分区

17:24:15 SQL> update ming.LICISITS_R set EUTIME=sysdate-31 where eid<=100000;

commit;

100000 rows updated.

Elapsed: 00:00:06.19

时间为6秒

分区内update

17:25:21 SQL> update ming.LICISITS_R set EUTIME=sysdate-32 where eid<=100000;

100000 rows updated.

Elapsed: 00:00:00.71

只需要0.71秒

再移动回去

17:26:45 SQL> update ming.LICISITS_R set EUTIME=sysdate where eid<=100000;

100000 rows updated.

Elapsed: 00:00:04.31

需要04.31秒

#############################################################

多次测试后发现，10万数据行迁移需要4-6秒，分区内update不用1秒。

#############################################################

在此期间监控记录redo生成量

select * from v$statname where name like '%redo%'

select * from v$sesstat where statistic#=288 and sid=807

744-27306840--165164928 --

分区内update：744-->27306840，大约26M redo

分区间行迁移：27306840-->165164928大约131M redo

将数据删除再插入，生成的redo从165164928-->275735704

此过程大约需要105M redo