Oracle下删除大表应该用drop还是truncate?

Oracle下删除大表应该用drop还是truncate?

今天在一DBA群中,一兄弟问到删除大表应该是先使用truncate再drop,还是直接drop？经过大家的讨论,加上高手解惑才知道了答案.

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

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

  实验过程如下:

SQL> connect zlhis/his;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as zlhis

SQL> create table test_truncate as select * from dba_objects;

Table created

SQL> insert into test_truncate select * from test_truncate;

55157 rows inserted

SQL> insert into test_truncate select * from test_truncate;

110314 rows inserted

SQL> insert into test_truncate select * from test_truncate;

220628 rows inserted

SQL> insert into test_truncate select * from test_truncate;

441256 rows inserted

SQL> insert into test_truncate select * from test_truncate;

882512 rows inserted

SQL> commit;

Commit complete

SQL> select Extents,blocks from dba_segments where segment_name='TEST_TRUNCATE';

   EXTENTS     BLOCKS
---------- ----------
        96      24576

SQL> truncate table test_truncate reuse storage;

Table truncated

SQL> select Extents,blocks from dba_segments where segment_name='TEST_TRUNCATE';

   EXTENTS     BLOCKS
---------- ----------
        96      24576

SQL> select bytes/1024/1024 as space_m  from dba_segments where segment_name='TEST_TRUNCATE';

   SPACE_M
----------
       192

SQL> ALTER table test_truncate DEALLOCATE UNUSED KEEP 100M;

Table altered

SQL> select Extents,blocks from dba_segments where segment_name='TEST_TRUNCATE';

   EXTENTS     BLOCKS
---------- ----------
        84      12808

SQL> select bytes/1024/1024 as space_m  from dba_segments where segment_name='TEST_TRUNCATE';

   SPACE_M
----------
  100.0625

SQL> ALTER table test_truncate DEALLOCATE UNUSED KEEP 0M;

Table altered

SQL> select bytes/1024/1024 as space_m  from dba_segments where segment_name='TEST_TRUNCATE';

   SPACE_M
----------
    0.0625