删除大型数据表数据可行性办法

       

--如何查询oracle表的大小SELECT  segment_name, BYTES/1024/1024/1024  FROM  user_segments  WHERE  segment_name='表名'。

        通过执行单条DELETE语句来删除一个大型的数据集会有以下的缺点：

        1) DELETE语句的操作要被完整地记录到日志中，这要求在事务日志中要有足够的空间以完成整个事务；

        2) 在删除操作期间（可能会花费很长时间），从最早打开的事务到当前时间点的所有日志都不能被重写；而且，如果由于某种原因，事务被中断，此前发生的所有操作都将被回滚，这也会花费一些时间；

        3) 当同时删除许多行时，可能会把被删除行上的单一锁升级为排他锁，以阻止DELETE完成之前对目标表的读写访问。

        想避免以上的问题，可以参考下面的这种方案的代码：

--若最终想保留的SELECT结果集在10W条以下，可以尝试此方法CREATE TABLE tmp AS SELECT * FROM t_port WHERE TO_DATE(update_time, 'YYYY-MM-DD HH24:MI:SS') >           TO_DATE('2017-01', 'YYYY-MM');TRUNCATE TABLE t_port;INSERT INTO t_port SELECT * FROM tmp;COMMIT;DROP TABLE tmp;

若想保留大表中大部分数据，也可使用以下几种方法，以避免上述存在的几个问题。

--可以分批次删除，能有效减少日志的大小DECLARE  CURSOR MYCURSOR IS    SELECT ROWID      FROM t_port     WHERE TO_DATE(update_time, 'YYYY-MM-DD HH24:MI:SS') <           TO_DATE('2017-01', 'YYYY-MM')     ORDER BY ROWID;  TYPE ROWID_TABLE_TYPE IS TABLE OF ROWID INDEX BY PLS_INTEGER;  V_ROWID ROWID_TABLE_TYPE;BEGIN  OPEN MYCURSOR;  LOOP    FETCH MYCURSOR BULK COLLECT      INTO V_ROWID LIMIT 5000; --每5000行一提交    EXIT WHEN V_ROWID.COUNT = 0;    FORALL I IN V_ROWID.FIRST .. V_ROWID.LAST      DELETE FROM t_port WHERE ROWID = V_ROWID(I);    COMMIT;  END LOOP;  CLOSE MYCURSOR;END;

--或者完全手动删除，一次删除几十万条数据，性能上也完全没问题DELETE FROM t_port p        WHERE TO_DATE(p.update_time,'yyyy-mm-dd hh24:mi:ss') < to_date('2017-01','YYYY-MM');

--SQL Server支持TOP关键字因此更容易些，如下：WHILE 1 = 1BEGIN  DELETE TOP (5000) FROM dbo.LargeOrders  WHERE orderdate < '20070101';    IF @@rowcount < 5000 BREAK;ENDGOWHILE 1 = 1BEGIN  UPDATE TOP (5000) dbo.LargeOrders    SET custid = 123  WHERE custid = 55;  IF @@rowcount < 5000 BREAK;ENDGO

Oracle数据库执行DELETE后，表占用的空间并不会减少。从10g开始，oracle开始提供Shrink的命令，可在执行DELETE后执行。

--要使用shink，首先需要使该表支持行移动ALTER TABLE t_port ENABLE ROW MOVEMENT;ALTER TABLE t_port SHRINK SPACE;ALTER TABLE t_port DISABLE ROW MOVEMENT;