Wednesday, July 1, 2020

"Move online including rows" vs traditional "delete + move";

Setup:

create table t1 as
select rownum as id, rpad('a',20,'a') as value from 
(select level from dual connect by level <=10000),
(select level from dual connect by level <=10000);

create table t2 as
select rownum as id, rpad('a',20,'a') as value from 
(select level from dual connect by level <=10000),
(select level from dual connect by level <=10000);


Delete+Move Performance: (Total: 7 minutes 8 seconds)

SQL> delete from t1 where mod(id,10)<>0;
90000000 rows deleted.
Elapsed: 00:05:38.24

SQL> commit;
Commit complete.
Elapsed: 00:00:00.09

SQL> alter table t1 move online;
Table altered.
Elapsed: 00:01:29.39

SQL> select bytes from dba_segments where owner='DONGHUA' and segment_name='T1';
     BYTES
----------
 369098752

SQL> select * from t1 fetch first 5 rows only;
        ID VALUE
---------- --------------------
        10 aaaaaaaaaaaaaaaaaaaa
        20 aaaaaaaaaaaaaaaaaaaa
        30 aaaaaaaaaaaaaaaaaaaa
        40 aaaaaaaaaaaaaaaaaaaa
        50 aaaaaaaaaaaaaaaaaaaa

Move online + Filter clause: (Total: 1 minutes 45 seconds)

SQL> alter table t2 move online including rows where mod(id,10)=0;
Table altered.
Elapsed: 00:01:45.85

SQL> select bytes from dba_segments where owner='DONGHUA' and segment_name='T2';
     BYTES
----------
 369098752

SQL> select * from t2 fetch first 5 rows only;
        ID VALUE
---------- --------------------
        10 aaaaaaaaaaaaaaaaaaaa
        20 aaaaaaaaaaaaaaaaaaaa
        30 aaaaaaaaaaaaaaaaaaaa
        40 aaaaaaaaaaaaaaaaaaaa
        50 aaaaaaaaaaaaaaaaaaaa

No comments:

Post a Comment