ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 海量数据表删除方案

海量数据表删除方案

原创 Linux操作系统 作者:realkid4 时间:2011-03-30 22:43:02 0 删除 编辑

 

声明:本文思路参考了hrb_qiuyb老师的文章《如何有效删除一张大表》http://space.itpub.net/9134/viewspace-166488,特此感谢。

 

 

当我们的系统进入海量数据时代后,很多过去看起来轻松简单的事情就变得比较复杂。此时,就需要我们采取一些独特的技术和技巧,来避免因此带来的一些问题。

 

 

如果一个数据表要进行删除,而数据表对应的数据量很大,对应空间多,此时要进行数据表drop需要面对一些困难。

 

首先是系统内存资源和CPU资源的使用峰值。集中进行drop操作,系统会进行数据表结构的回收,对应数据分区和数据块的回收。当数据表很大的时候,这个过程自然很长,对CPU来说消耗时间和数量都很大。同时,进行drop table的过程中,会将数据表转移到临时段进行处理,这个过程对临时段空间的消耗也是巨大的。

 

第二个是一个重要问题,就是对整体前端应用的影响。如果贸然进行drop操作,给业务系统造成影响,进而带来影响。如果借用业务系统窗口期进行删除操作,又不能保证窗口期够用。

 

 

分步分阶段大表删除

 

 

总的指导原则是先删除数据,不影响数据一致性的要求,之后分阶段分步骤的进行空间回收。避免一次性drop对系统造成过大的压力。

 

步骤:

 

1、首先使用带reuse storage子句的truncate table,将数据删除。reuse storage子句的作用是单纯降低数据段data segment的高水位线,对分配的空间不进行回收。这样truncate操作不涉及到空间回收,速度是可以接受的。

2、分若干次数,使用deallocate unused keep XXX的方法,将分配的空间回收。因为keep后面可以加入维持空间数量,所以可以分若干个窗口期进行回收。

 

 

实验演示

 

首先准备实验环境。

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0    Production

 

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

Executed in 0.051 seconds

 

--准备20万的数据,远谈不上海量,实例而已。

SQL> select count(*) from m;

 

  COUNT(*)

----------

    201600

 

Executed in 0.221 seconds

 

 

此时,我们分析作为一个data segment,数据表M的相关参数信息。

 

 

SQL> select header_file, header_block, blocks, bytes, extents from dba_segments where segment_name='M' and wner='SYS';

 

HEADER_FILE HEADER_BLOCK     BLOCKS      BYTES    EXTENTS

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

          1        62353       2816   23068672         37

 

Executed in 0.201 seconds

 

 

通过dba_segments视图可以知道,数据表M共分配在37个分区上,共包括2816个数据块。空间占有为23M左右。

 

删除实验,首先使用truncate table。

 

SQL> truncate table m reuse storage;

 

Table truncated

 

Executed in 0.52 seconds

 

 

reuse storage子句下,空间是不进行回收的,所以相对速度较快。下面是一个对比同类型规模数据表实验。

 

 

SQL> select count(*) from md;

 

  COUNT(*)

----------

    201604

 

Executed in 0.451 seconds

 

SQL> truncate table md;

 

Table truncated

 

Executed in 0.901 seconds

 

 

相同的数据结构和数据量,使用的时间要超过reuse storage的方式。当然,带有reuse storage的语句下空间也是不回收的。

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

 

PL/SQL procedure successfully completed

 

Executed in 0.43 seconds

 

SQL> select header_file, header_block, blocks, bytes, extents from dba_segments where segment_name='M' and wner='SYS';

 

HEADER_FILE HEADER_BLOCK     BLOCKS      BYTES    EXTENTS

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

          1        62353       2816   23068672         37

 

Executed in 0.131 seconds

 

 

发现,虽然数据被删除了,但是空间没有回收。在dba_segments上,依然显示37个数据区的空间分配。

 

分阶段进行空间回收,让回收工作在受控范围下进行。

 

 

--第一次空间回收

SQL> alter table m deallocate unused keep 20M; --控制在20M

 

Table altered

 

Executed in 0.11 seconds

 

SQL> select header_file, header_block, blocks, bytes, extents from dba_segments where segment_name='M' and wner='SYS';

 

HEADER_FILE HEADER_BLOCK     BLOCKS      BYTES    EXTENTS

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

          1        62353       2568   21037056         36

 

Executed in 0.12 seconds

 

 

 

可见,将空间近似降低到21M左右。此后,可以在不同的窗口期中,根据自身情况不断进行回收空间工作。

 

SQL> alter table m deallocate unused keep 15M;

 

Table altered

 

Executed in 0.06 seconds

 

SQL> alter table m deallocate unused keep 10M;

 

Table altered

 

Executed in 0.01 seconds

 

SQL> alter table m deallocate unused keep 3M;

 

Table altered

 

Executed in 0.01 seconds

 

SQL> alter table m deallocate unused keep 100k;

 

Table altered

 

Executed in 0.01 seconds

 

SQL>

 

SQL> select header_file, header_block, blocks, bytes, extents from dba_segments where segment_name='M' and wner='SYS';

 

HEADER_FILE HEADER_BLOCK     BLOCKS      BYTES    EXTENTS

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

          1        62353         16     131072          2

 

Executed in 0.03 seconds

 

 

最后,要求回收到1k实验。

 

SQL> alter table m deallocate unused keep 1k;

 

Table altered

 

Executed in 0 seconds

 

SQL> select header_file, header_block, blocks, bytes, extents from dba_segments where segment_name='M' and wner='SYS';

 

HEADER_FILE HEADER_BLOCK     BLOCKS      BYTES    EXTENTS

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

          1        62353          8      65536          1

 

Executed in 0.03 seconds

 

 

虽然没有按照我们的希望回收到1k,但也是已经到一个分区八个数据块的程度。最后就好处理了,直接drop table即可。

 

SQL> drop table m;

 

Table dropped

 

Executed in 0.06 seconds

 

 

实验告诉我们几个结论:

 

1、在写一句SQL的时候,一定要注意操作数据集合、对象的范围。对于可能存在的海量数据访问,一定要实现有准备,事后有监控。这样才能保证系统可用性以及上线成功率;

2、海量数据处理的一个方法就是分割,根据业务的限制和要求,在时间上进行分割。处理原则是避开业务系统繁忙时间段,尽量将高负载工作进行划分实现;

3、drop数据表的方法。一般来说,进行drop数据表是不能中间终止的。drop数据表时,oracle首先把数据段转化为临时段对象,之后开始不断的进行空间回收。即使这个过程中间停止(强制终止),再次启动的时候smon进行回复也会进行回收删除操作。所以,对数据表进行直接drop的时候,切记三思。

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-691332/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7688284