Oracle大表清理truncate .. reuse storage

最近需要清理一张大表,要求不能影响性能。在MySQL里边我们可以通过借助coreutils以及硬链接的方式来最小化I/O,Oracle也可以通过分批次回收空间来最小化I/O,到底如何,下面我们拭目以待。

一、TRUNCATE TABLE 语法

TRUNCATE TABLE [schema_name.]table_name
  [ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ]
  [ DROP STORAGE | REUSE STORAGE ] ;
--下面仅列出reuse storage的说明部分  
REUSE STORAGE 
Specify REUSE STORAGE to retain the space from the deleted rows allocated to the table. Storage values are not reset to the values when the table was created. This space can subsequently be used only by new data in the table resulting from insert or update operations. This clause leaves storage parameters at their current settings.

This setting is useful as an alternative to deleting all rows of a very large table—when the number of rows is very large, the table entails many thousands of extents, and when data is to be reinserted in the future. TRUNCATE TABLE with REUSE STORAGE performs several orders of magnitude faster than deleting all rows, but has the following drawbacks:

•You cannot roll back a TRUNCATE TABLE statement.

•All cursors are invalidated.

•You cannot flash back to the state of the table before the truncate operation.

This clause is not valid for temporary tables. A session becomes unbound from the temporary table when the table is truncated, so the storage is automatically dropped.

If you have specified more than one free list for the object you are truncating, then the REUSE STORAGE clause also removes any mapping of free lists to instances and resets the high-water mark to the beginning of the first extent.

二、演示truncate table .. reuse storage(11g)

SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> create table tb_reuse as select * from dba_objects;

Table created.

SQL> /     --多次执行

37200896 rows created.

SQL> create table tb_noreuse as select * from tb_reuse;

Table created.

SQL> select count(*) from tb_reuse;

  COUNT(*)
----------
  37200896

SQL>  select count(*) from tb_noreuse;

  COUNT(*)
----------
  37200896

SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in('TB_REUSE','TB_NOREUSE');

SEGMENT_NAME                        BYTES/1024/1024
----------------------------------- ---------------
TB_REUSE                                       4165   --占用空间接近4GB
TB_NOREUSE                                     4172

SQL> truncate table tb_noreuse;   --直接truncate,速度很快    

Table truncated.

Elapsed: 00:00:00.25
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in('TB_REUSE','TB_NOREUSE');

SEGMENT_NAME                        BYTES/1024/1024
----------------------------------- ---------------
TB_REUSE                                       4165
TB_NOREUSE                                    .0625  -- 空间已回收

Elapsed: 00:00:00.03

SQL> truncate table tb_reuse reuse storage;          --使用reuse storage方式,并无太多性能提升

Table truncated.

Elapsed: 00:00:00.07
SQL> alter table tb_reuse deallocate unused keep 2048;  --这里漏掉了指定m,缺省为byte

Table altered.

Elapsed: 00:00:00.36
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in('TB_REUSE','TB_NOREUSE');

SEGMENT_NAME                        BYTES/1024/1024
----------------------------------- ---------------
TB_REUSE                                      .0625
TB_NOREUSE                                    .0625

Elapsed: 00:00:00.03

三、演示truncate table .. reuse storage(12g)

SQL> select * from v$version where rownum=1;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0

SQL> create table tb_12_use as select * from dba_objects;

Table created.

SQL> insert into tb_12_use select * from tb_12_use;

90903 rows created.

SQL> /

11635584 rows created.

SQL> create table tb_12_nouse as select * from tb_12_use;

Table created.

SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in('TB_12_USE','TB_12_NOUSE');

SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ---------------
TB_12_NOUSE                               3074   --使用空间为3GB
TB_12_USE                                 3072

SQL> select 'Leshami' As author,'http://blog.csdn.net/leshami' as Blog from dual;

AUTHOR  BLOG
------- ----------------------------
Leshami http://blog.csdn.net/leshami

SQL> set timing on;
SQL> truncate table TB_12_NOUSE;              --使用常规方式truncate

Table truncated.

Elapsed: 00:00:01.73
SQL> truncate table TB_12_USE reuse storage;  --使用reuse storage方式,并无太多性能提升

Table truncated.

Elapsed: 00:00:01.10
SQL> alter table TB_12_USE deallocate unused keep 2048m; 

Table altered.

Elapsed: 00:00:00.25
SQL> alter table TB_12_USE deallocate unused keep 1m;

Table altered.

Elapsed: 00:00:00.14
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in('TB_12_USE','TB_12_NOUSE');

SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ---------------
TB_12_NOUSE                              .0625
TB_12_USE                               1.0625

Elapsed: 00:00:00.03

-- 由于前面的测试在非归档模式,因此重启切换到归档模式后再次测试
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     396
Next log sequence to archive   398
Current log sequence           398

SQL> select count(*) from tb_12_use;

  COUNT(*)
----------
  23273472

SQL> select count(*) from tb_12_nouse;

  COUNT(*)
----------
  23273472

SQL> truncate table TB_12_NOUSE;    

Table truncated.

Elapsed: 00:00:02.07

SQL> truncate table TB_12_USE reuse storage; --归档后使用reuse storage方式,同样无太多性能提升
                                             --因为truncat属于DDL,本身并不会产生太大arch
Table truncated.

Elapsed: 00:00:00.76

四、小结

a、通过上述测试,当使用reuse storage与普通方式并无明显差异 b、truncate table 是ddl操作,无法回滚 c、尽管无明显性能差异,生产环境大表情况,还是建议使用reuse storage结合deallocate方式

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

基于DB time的调优分析 (r6笔记第79天)

继昨天使用DB time能够快速灵活的定位sql语句之后,发现分析问题更快捷,高效了。今天就牛刀小试,把一个数据库从500%的负载调到不到100%的负载。前提是...

31440
来自专栏杨建荣的学习笔记

关于索引和空值的讨论(r3笔记第80天)

在日常的工作中,空值总是有特殊的身份,对于它的处理有时候也是比较纠结。 有时候创建索引的时候会因为空值出现一些奇怪的结果。 有时候一个简单的查询因为空值却走不了...

28360
来自专栏乐沙弥的世界

函数使得索引列失效

      在索引列上使用函数使得索引失效的是常见的索引失效原因之一,因此尽可能的避免在索引列上使用函数。尽管可以使用基于函数的索引来 解决索引失效的问题,但...

14930
来自专栏乐沙弥的世界

Oracle 全表扫描及其执行计划(full table scan)

    全表扫描是Oracle访问数据库表是较为常见的访问方式之一。很多朋友一看到SQL语句执行计划中的全表扫描,就要考虑对其进行修理一番。全表扫描的存在,的确...

26510
来自专栏乐沙弥的世界

批量生成sqlldr文件,高速卸载数据

      SQL*Loader 是用于将外部数据进行批量高速加载的数据库的最高效工具,可用于将多种平面格式文件加载到Oracle数据库。SQL*Loader支...

21020
来自专栏杨建荣的学习笔记

聚簇因子和执行计划的联系(r3笔记第90天)

在平时的工作中,可能会碰到一种很奇怪的问题,本来在生产环境中有些sql语句执行没有问题,一个很普通的查询预期走了索引扫面,但是拷贝数据到其它环境之后,就发现却走...

29450
来自专栏杨建荣的学习笔记

走索引扫描的慢查询(r3笔记45天)

今天查看awr报告的时候,发现一条sql语句异常。 Elapsed Time (s) Executions Elapsed Time per Exec (s)...

39780
来自专栏乐沙弥的世界

Oracle 历史SQL语句执行计划的对比与分析

    基于CBO优化器的环境中,SQL执行计划的生成依赖于统计信息的真实与完整。如列的离散度,列上的直方图,索引的可用性,索引上的聚簇因子。当这些信息是真实完...

10510
来自专栏Jerry的SAP技术分享

使用BAPISDORDER_GETDETAILEDLIST创建S/4HANA的Outbound Delivery

要在S/4HANA里创建Outbound Delivery,首先要具有一个销售订单,ID为376,通过事务码VA03查看。

12310
来自专栏跟着阿笨一起玩NET

把数据库中表的内容转存为XML文件

10400

扫码关注云+社区

领取腾讯云代金券