Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the impact of row migration on the efficiency of cross-partition update". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Purpose of the test:

The impact of row migration on cross-partition update efficiency. Create a test table

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

Enable row migration

Alter table ming.LICISITS_R enable row movement

Insert 100000 data per partition

Begin

For i in 1.. 100000

Loop

Insert into ming.LICISITS_R values (iMagazine sysdatejie I)

End loop

Commit

End

/ begin

For i in 100001.. 200000

Loop

Insert into ming.LICISITS_R values (iMagazine sysdately31pr I)

End loop

Commit

End

/

Open recording time

Set timing on time on

Update all SYS_P3695 partition data to SYS_P3696 partition

17:24:15 SQL > update ming.LICISITS_R set EUTIME=sysdate-31 where eid update ming.LICISITS_R set EUTIME=sysdate-32 where eid update ming.LICISITS_R set EUTIME=sysdate where eid27306840, about 26m redo

Inter-partition row migration: 27306840 color-> 165164928 about 131m redo

Delete and insert the data, and the generated redo is 165164928-> 275735704.

This process takes about 105m redo

This is the end of the analysis of the impact of row migration on the efficiency of cross-partition update. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.