测试Truncate大表和drop大表效率那个更高

jiawang

Truncate后drop测试表

SQL> create table test as select * from dba_objects;

Table created.

SQL> select  value

2   from v$mystat, v$statname

3   where v$mystat.statistic# =v$statname.statistic#

4   and v$statname.name ='redosize';

    VALUE

----------

10230904

SQL> truncate table test;

Table truncated.

SQL> select  value

2   from v$mystat, v$statname

3   where v$mystat.statistic#=v$statname.statistic#

4   and v$statname.name ='redosize';

    VALUE

----------

10258452

SQL> drop table test purge;

Table dropped.

SQL> select  value

2   from v$mystat, v$statname

3   where v$mystat.statistic#=v$statname.statistic#

4   and v$statname.name ='redosize';

    VALUE

----------

10280280

SQL> select(10280280-10230904)/1024 from dual;

(10280280-10230904)/1024

------------------------

                48.21875

直接Drop测试表

SQL> create table test as select * fromdba_objects;

Table created.

SQL> select  value

fromv$mystat, v$statname

2    3   where v$mystat.statistic#=v$statname.statistic#

4   and v$statname.name ='redosize';

    VALUE

----------

20509944

SQL> drop table test purge;

Table dropped.

SQL> select  value

2   from v$mystat, v$statname

3   where v$mystat.statistic#=v$statname.statistic#

4   and v$statname.name ='redosize';

    VALUE

----------

20539140

SQL> select(20539140-20509944)/1024 from dual;

(20539140-20509944)/1024

------------------------

              28.5117188

综上所述：清除大表的时候先truncate,然后drop，效率没有直接使用drop测试表高。