重庆思庄Oracle、、PostgreSQL、Redhat认证学习论坛

标题: 测试Truncate大表和drop大表效率那个更高 [打印本页]

作者: jiawang 时间: 2019-3-6 17:33
标题: 测试Truncate大表和drop大表效率那个更高

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测试表高。

欢迎光临重庆思庄Oracle、、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)