如何删除大表中的数据

通常我们认为truncate和drop都是ddl语句,都会释放表占用的空间,且不可回退;而他们的之间的区别在于我们平时忽略的reuse/drop storage子句.

reuse storage不会立即释放表的extent,我们可以先使用truncate table tableName reuse storage,然后分批释放表的extent.这在删除大表时非常有用,避免大量的io操作,影响整体性能.如果使用默认的drop storage就会立即释放extent,删除的表如果非常大,这对系统有时候这可能是灾难性的.

版本：10.2.0.1 

说明：在9i下如此操作为报错(后续说明)。

实验过程如下:

SQL> insert into yanga select * from yanga;

50474 rows created.

SQL> /

100948 rows created.

SQL> /

201896 rows created.

SQL> /

403792 rows created.

SQL> /

807584 rows created.

SQL> /

1615168 rows created.

SQL>  select sum(bytes)/1024/1024 MB from dba_segments where segment_name=UPPER('yanga');

        MB

----------

       349

SQL>  truncate table yanga reuse storage;

Table truncated.

SQL>  select sum(bytes)/1024/1024 MB from dba_segments where segment_name=UPPER('yanga');

        MB

----------

       349

SQL> alter table yanga deallocate unused keep 50M;

Table altered.

SQL>  select sum(bytes)/1024/1024 MB from dba_segments where segment_name=UPPER('yanga');

        MB

----------

   50.0625

SQL> alter table yanga deallocate unused keep 10M;

Table altered.

SQL>  select sum(bytes)/1024/1024 MB from dba_segments where segment_name=UPPER('yanga');

        MB

----------

   10.0625

SQL> alter table yanga deallocate unused keep 0 M;

Table altered.

SQL>  select sum(bytes)/1024/1024 MB from dba_segments where segment_name=UPPER('yanga');

        MB

----------

     .0625

SQL> drop table yanga purge;

Table dropped.

SQL>  select sum(bytes)/1024/1024 MB from dba_segments where segment_name=UPPER('yanga');

        MB

----------

以上是10g2的操作。