方法1 | 方法2 | 方法3 | |
分区名称 | P20151110 | P20151111 | P20151112 |
数据量 | 10503249 | 11835671 | 9248433 |
耗时 | 1:29:37 | 0:14:38 | 0:02:17 |
使用方法2和方法3删除分区时,都会堵塞当前分区的写操作,对其他分区无影响。
SQL> select count(*) from tab_a partition(P20151110);
COUNT(*)
----------
10503249
SQL> select count(*) from tab_a partition(P20151111);
COUNT(*)
----------
11835671
SQL> select count(*) from tab_a partition(P20151112);
COUNT(*)
----------
9248433
SQL> set timing on
SQL> declare
2 l_sql varchar2(400);
3 l_rc number;
4 begin
5 l_sql := 'delete from tab_a partition(P20151110) where rownum<=10000';
6 loop
7 execute immediate l_sql;
8 l_rc := sql%rowcount;
9 exit when (l_rc<=0 or l_rc is null);
10 commit;
11 end loop;
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
Elapsed: 01:29:36.92
SQL> alter table tab_a truncate partition(P20151111) update global indexes;
Table truncated.
Elapsed: 00:14:37.66
SQL> alter table tab_a drop partition(P20151112) update global indexes;
Table altered.
Elapsed: 00:02:16.59