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

       

--如何查询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 = 1
BEGIN
  DELETE TOP (5000) FROM dbo.LargeOrders
  WHERE orderdate < '20070101';
  
  IF @@rowcount < 5000 BREAK;
END
GO


WHILE 1 = 1
BEGIN
  UPDATE TOP (5000) dbo.LargeOrders
    SET custid = 123
  WHERE custid = 55;

  IF @@rowcount < 5000 BREAK;
END
GO

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;