oracle上亿条记录大表delete

最新推荐文章于 2021-04-02 22:29:57 发布

转载最新推荐文章于 2021-04-02 22:29:57 发布 · 7.8k 阅读

文章标签：

#oracle

oracle 专栏收录该内容

34 篇文章

订阅专栏

本文介绍了一种使用Oracle DBMS_JOB并行处理大量数据删除的方法，通过创建多个job并分配特定范围的数据来提升效率。此外，还讨论了INSERT、UPDATE及DELETE操作的日志产生量，并提出在大量数据操作时使用INSERT替代UPDATE的建议。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

delete /*+ use_hash(a,b) parallel(a,15)*/ from tabacca where exists (select 1 from temptablea b where a.id=b.id and b.type='1')；

可以试试多个job分工，同时进行。
比如开10个job，每个job分十万数据

DECLARE
   x   NUMBER;
BEGIN
   FOR i IN 0 .. 9
   LOOP
      DBMS_JOB.submit (
         x,
         'begin DELETE   tabacca t1
WHERE   EXISTS
            (SELECT   1
               FROM   (SELECT   id
                         FROM   (SELECT   SUBSTR (TO_CHAR (ROWNUM), -1) rn,
                                          id
                                   FROM   temptablea
                                  WHERE   TYPE = ''1'')
                        WHERE   rn = '''
         || i
         || ''') t2
              WHERE   t2.id = t1.id);

COMMIT; end;'
);
END LOOP;
END;

分区表的话，就加一层分区进行循环。

for 分区
for i in 0.. 9

DBMS_JOB.SUBMIT(:jobno,//job号
                                     'your_procedure;',//要执行的过程
                                     trunc(sysdate)+1/24,//下次执行时间
                                     'trunc(sysdate)+1/24+1'//每次间隔时间
                                   );
     删除job:dbms_job.remove(jobno);
     修改要执行的操作:job:dbms_job.what(jobno,what);
     修改下次执行时间：dbms_job.next_date(job,next_date);
     修改间隔时间：dbms_job.interval(job,interval);
     停止job:dbms.broken(job,broken,nextdate);
     启动job:dbms_job.run(jobno);

附：调用语句和参数说明：

dbms_job.submit( job out binary_integer,

what　　　　　　　in　　　archar2,
next_date　　　　in　　　date，
interval　　　　　in　　　varchar2,
no_parse　　　　　in　　　boolean)
其中：
●    job：输出变量，是此任务在任务队列中的编号；
●    what：执行的任务的名称及其输入参数；
●    next_date：任务执行的时间；
●    interval：任务执行的时间间隔

ORACLE的INSERT、UPDATE和DELETE在执行过过程中都是会产生日志的，因此，当插入大量数据的时候可以添加“nologging”选项，以提高执行速度，三种操作所做的具体东西而相关资料是这样写的：
      常INSERT产生最少的Undo，Update产生的Undo居中，而Delete操作产生的Undo最多。
      对于INSERT操作，回滚段只需要记录插入记录的rowid，如果回退，只需将该记录根据rowid删除即可；
      对于UPDATE操作，回滚段只需要记录被更新字段的旧值即可（前镜像），回退时通过旧值覆盖新值即可完成回退；
      对于DELETE操作，Oracle则必须记录整行的数据，在回退时，Oracle通过一个反向操作恢复删除的数据。
因此INSERT是最快的
所以当遇到大量数据修改的时候，可以考虑用INSERT来代替UPDATE。

例子：
    有一个表A有100万条记录，我现在要把这个表A备份成另外一个表B。我用了两种方式
    create table b as select * from a
    第二种是
    create table b as select * from a where 1=0    可以加上（alter table b nologging）
    insert into b select * from a
    这两种速度，差别是十几倍。为什么啊？

第一种属于ddl语句，不产生redo，日志量小

而第二种用到的insert 属于dml，产生日志，由于记录多，产生的redo量也是很大的，还要写很多log