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