Oracle如何快速批量删除数据

一张表有500万条数据,有效数据为50万条,如何快速删除其它450万条数据?

一、TRUNCATE命令

TRUNCATE TABLE命令仅适用于全表删除,因为不写REDO LOG FILE,所以速度很快。但如果只删除表中的部分数据时,此方法行不通。 

二、DELETE命令

直接使用DELETE FROM...WHERE...命令进行删除,在删除数据的过程中,会不断扩展回滚段,非常耗时,且如果回滚段有错误时,恢复非常麻烦。

三、通过PL/SQL循环分段删除

写一段PL/SQL程序,分段删除数据,逐步提交事务。 例如有一个数据表PANTAB,我们将对其中字段CARDBIN不等于110125/112025,或者CARDBIN为空的记录进行删除,可以采用以下的PL/SQL程序:

declare
--定义存储结构
type card_type is record
(
    cardbin CARDTAB.CARDBIN%type,
    cardname CARDTAB.CARDNAME%type
);
card_record card_type;

cursor cur_card is
    --取出所有需要删除的CARD类型
    select CARDBIN, CARDNAME
    from CARDTAB
    where CARDBIN not in('110125','112025');
begin
    for card_record in cur_card loop
          --删除,按CARD类型
          delete from PANTAB where CARDBIN = card_record.cardbin;
          dbms_output.put_line(to_char(card_record.cardname)||'正在删除...');
          --提交
          commit;
    end loop;

    --删除额外的CARD类型,在这里是CARDBIN为空
    delete from PANTAB where CARDBIN is null;
    dbms_output.put_line('正在删除cardname为空的记录...');
    commit;

exception
    when others then
        dbms_output.put_line('sqlerrm-->' ||sqlerrm);
        rollback;
end;

四、重新建表拷贝数据,然后改名 

即通过CREATE TABLE ... NOLOGGING ... AS SELECT * FROM...的方法将要保留的数据备份到另一个表中,将原来的表删除,然后再 ALTER TABLE RENAME TO 命令将备份的表改为原来表的名字。
注意:此方法不写日志文件,速度很快,但需要重新建立索引和约束。

create table PANTAB_NEW nologging as select * from PANTAB where CARDBIN in('110125','112025');
drop table PANTAB;
alter table PANTAB_NEW rename to PANTAB;

重建建立索引(根据自己需要更改):

  CREATE UNIQUE INDEX "MYTEST"."PANTAB_PK" ON "MYTEST"."PANTAB" ("SYSCODE"); 
  CREATE UNIQUE INDEX "MYTEST"."PANTAB_UK1" ON "MYTEST"."PANTAB" ("PAN") ;
  CREATE INDEX "MYTEST"."PANTAB_INDEX_AMT" ON "MYTEST"."PANTAB" ("TXNAMT"); 
  CREATE INDEX "MYTEST"."PANTAB_INDEX_BALANCE" ON "MYTEST"."PANTAB" ("BALANCE") ;
  CREATE INDEX "MYTEST"."INDEX1" ON "MYTEST"."PANTAB" ("TXNTIMEOFREQ") ;
  CREATE BITMAP INDEX "MYTEST"."PANTAB_INDEX_BIN" ON "MYTEST"."PANTAB" ("CARDBIN") ;
  ALTER TABLE "MYTEST"."PANTAB" ADD CONSTRAINT "PANTAB_PK" PRIMARY KEY ("SYSCODE");
  ALTER TABLE "MYTEST"."PANTAB" ADD CONSTRAINT "PANTAB_UK1" UNIQUE ("PAN");

 

posted @ 2022-06-16 14:27  那些年的事儿  阅读(6770)  评论(0编辑  收藏  举报