Oracle truncate 和drop 的区别测试

baixy5r583

设置autotrace功能
1.创建plan_table表
sqlplus /nolog
conn sys /as sysdba
@?/rdbms/admin/utlxplan.sql;
create public synonym plan_table for plan_table;
grant all on plan_table to public;
2.创建plustrace角色和权限
@?/sqlplus/admin/plustrce.sql;
DBA用户首先被授予了plustrace角色,然后我们可以把plustrace授予public，这样所有用户都将拥有plustrace角色的权限.
grant plustrace to public ;
已可用autotrace功能
3.测试:
SQL> connect center/Dongyun123
SQL> set autotrace on;
设置时间提示
SQL> set time on;
设置统计执行时间
SQL 18:23:53> set timing on;
4.帮助:
关于autotrace几个常用选项的说明:
SET AUTOTRACE OFF ------------ 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ----- AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------- 同set autotrace on，但是不显示查询输出
 
sys@TICKET> set auo
SP2-0158: 未知的 SET 选项 "auo"
sys@TICKET> set autot
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
sys@TICKET> set autotrace on stat;
sys@TICKET> create table test as select * from dba_objects
2  union all select * from dba_objects
3  union all select * from dba_objects
4  union all select * from dba_objects
5  union all select * from dba_objects
6  union all select * from dba_objects
7  union all select * from dba_objects
8  union all select * from dba_objects
9  union all select * from dba_objects
10  union all select * from dba_objects
11  union all select * from dba_objects
12  union all select * from dba_objects
13  union all select * from dba_objects
14  union all select * from dba_objects
15  union all select * from dba_objects
16  union all select * from dba_objects;
表已创建。
sys@TICKET>
sys@TICKET> commit;
提交完成。
sys@TICKET> commit;
提交完成。
sys@TICKET> select extents from user_segments where segment_name='TEST';
EXTENTS
----------
88

统计信息
----------------------------------------------------------
22  recursive calls
0  db block gets
96  consistent gets
1  physical reads
0  redo size
418  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed
sys@TICKET> truncate table test;
表被截断。
sys@TICKET> select extents from user_segments where segment_name='TEST';
EXTENTS
----------
1

统计信息
----------------------------------------------------------
0  recursive calls
0  db block gets
90  consistent gets
0  physical reads
0  redo size
418  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed
sys@TICKET> drop table test;
表已删除。
sys@TICKET> create table test as select * from dba_objects
2  union all select * from dba_objects
3  union all select * from dba_objects
4  union all select * from dba_objects
5  union all select * from dba_objects
6  union all select * from dba_objects
7  union all select * from dba_objects
8  union all select * from dba_objects
9  union all select * from dba_objects
10  union all select * from dba_objects
11  union all select * from dba_objects
12  union all select * from dba_objects
13  union all select * from dba_objects
14  union all select * from dba_objects
15  union all select * from dba_objects
16  union all select * from dba_objects;
表已创建。
sys@TICKET>
sys@TICKET> commit;
提交完成。
sys@TICKET> select extents from user_segments where segment_name='TEST';
EXTENTS
----------
88

统计信息
----------------------------------------------------------
0  recursive calls
0  db block gets
90  consistent gets
0  physical reads
0  redo size
418  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed
sys@TICKET> truncate table test drop storage;
表被截断。
sys@TICKET> select extents from user_segments where segment_name='TEST';
EXTENTS
----------
1

统计信息
----------------------------------------------------------
0  recursive calls
0  db block gets
90  consistent gets
0  physical reads
0  redo size
418  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed
sys@TICKET> drop table test;
表已删除。
sys@TICKET> create table test as select * from dba_objects
2  union all select * from dba_objects
3  union all select * from dba_objects
4  union all select * from dba_objects
5  union all select * from dba_objects
6  union all select * from dba_objects
7  union all select * from dba_objects
8  union all select * from dba_objects
9  union all select * from dba_objects
10  union all select * from dba_objects
11  union all select * from dba_objects
12  union all select * from dba_objects
13  union all select * from dba_objects
14  union all select * from dba_objects
15  union all select * from dba_objects
16  union all select * from dba_objects;
表已创建。
sys@TICKET> commit;
提交完成。
sys@TICKET> select extents from user_segments where segment_name='TEST';
EXTENTS
----------
88

统计信息
----------------------------------------------------------
0  recursive calls
0  db block gets
90  consistent gets
0  physical reads
0  redo size
418  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed
sys@TICKET> truncate table test reuse storage;
表被截断。
sys@TICKET> select extents from user_segments where segment_name='TEST';
EXTENTS
----------
88

统计信息
----------------------------------------------------------
0  recursive calls
0  db block gets
90  consistent gets
0  physical reads
0  redo size
418  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed
sys@TICKET> select * from test;
未选定行

统计信息
----------------------------------------------------------
323  recursive calls
1  db block gets
40  consistent gets
1  physical reads
96  redo size
1124  bytes sent via SQL*Net to client
405  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
0  rows processed
sys@TICKET> drop table test;
表已删除。
sys@TICKET> create table test as select * from dba_objects
2  union all select * from dba_objects
3  union all select * from dba_objects
4  union all select * from dba_objects
5  union all select * from dba_objects
6  union all select * from dba_objects
7  union all select * from dba_objects
8  union all select * from dba_objects
9  union all select * from dba_objects
10  union all select * from dba_objects
11  union all select * from dba_objects
12  union all select * from dba_objects
13  union all select * from dba_objects
14  union all select * from dba_objects
15  union all select * from dba_objects
16  union all select * from dba_objects;
表已创建。
sys@TICKET>
sys@TICKET> commit;
提交完成。
sys@TICKET> select extents from user_segments where segment_name='TEST';
EXTENTS
----------
88

统计信息
----------------------------------------------------------
0  recursive calls
0  db block gets
90  consistent gets
0  physical reads
0  redo size
418  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed
sys@TICKET> drop table test;
表已删除。
sys@TICKET> select extents from user_segments where segment_name='TEST';
未选定行

统计信息
----------------------------------------------------------
0  recursive calls
0  db block gets
66  consistent gets
0  physical reads
0  redo size
284  bytes sent via SQL*Net to client
405  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
0  rows processed
sys@TICKET> spool off;
由以上可以看出得出一下信息:
1.如果从性能的角度来看，truncate  reuse   storage 主要是针对大量extent 的字典管理表空间.
2.DROP TABLE的时候，Extents要被回收。而truncate table test reuse storage没有Extents要被回收.
3.truncate table test drop storage 的时候，Extents要被回收,HWM会变化 。
4.truncate table test功能和truncate table test drop storage差不多.
5.truncate table reuse storage在性能上比truncate table  drop storage好,