优化dup delete语句Oracle

Question

我有两个删除语句，需要很长时间才能完成。where子句中的列上有几个索引。

什么是重复?如果两个或更多的记录在id、cid、type、trefid、ordrefid、和paydt列中具有相同的值，则存在重复。

删除删除了大约100万条记录。

可以用任何方式重写以使它更快吗？

DELETE FROM TABLE1 A WHERE loaddt < (
    SELECT max(loaddt) FROM TABLE1 B
    WHERE 
    a.id=b.id and
    a.cid=b.cid and
    NVL(a.type,'-99999') = NVL(b.type,'-99999') and
    NVL(a.trefid,'-99999')=NVL(b.trefid,'-99999') and
    NVL(a.ordrefid,'-99999')= NVL(b.ordrefid,'-99999') and
    NVL(a.amount,'-99999')=NVL(b.amount,'-99999') and
    NVL(a.paydt,TO_DATE('9999-12-31','YYYY-MM-DD'))=NVL(b.paydt,TO_DATE('9999-12-31','YYYY-MM-DD'))
);

    COMMIT;

DELETE FROM TABLE1 a where rowid > (
    Select min(rowid) from TABLE1 b
    WHERE 
    a.id=b.id and
    a.cid=b.cid and
    NVL(a.type,'-99999') = NVL(b.type,'-99999') and
    NVL(a.trefid,'-99999')=NVL(b.trefid,'-99999') and
    NVL(a.ordrefid,'-99999')= NVL(b.ordrefid,'-99999') and
    NVL(a.amount,'-99999')=NVL(b.amount,'-99999') and
    NVL(a.paydt,TO_DATE('9999-12-31','YYYY-MM-DD'))=NVL(b.paydt,TO_DATE('9999-12-31','YYYY-MM-DD'))
);

commit;

解释计划：

DELETE  TABLE1         

    HASH JOIN 1296491 
    Access Predicates 

        AND 
        A.ID=ITEM_1 
        A.CID=ITEM_2 
        ITEM_3=NVL(TYPE,'-99999') 
        ITEM_4=NVL(TREFID,'-99999') 
        ITEM_5=NVL(ORDREFID,'-99999') 
        ITEM_6=NVL(AMOUNT,(-99999)) 
        ITEM_7=NVL(PAYDT,TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 

    Filter Predicates 
        LOADDT<MAX(LOADDT)

    TABLE ACCESS  TABLE1     FULL    267904 
    VIEW VW_SQ_1         690385 
    SORT GROUP BY    690385 
        TABLE ACCESS TABLE1      FULL    267904 

Answer 1

这张桌子有多大？如果删除行的计数高达12%，则可以考虑索引。你能一周一周地划分你的桌子，然后只扫描实际的一周吗？

也许这样会更有效。当您使用聚合函数时，oracle必须遍历所有相关的行(在您的例子中是完全扫描)，但是当您使用exists时，它会在第一次出现时停止。(当然，当where子句中的所有列都有一个基于函数的索引(因为NVL)时，查询会快得多)。

DELETE FROM TABLE1 A 
WHERE exists (
SELECT 1 
FROM TABLE1 B
WHERE 
A.loaddt != b.loaddt
a.id=b.id and
a.cid=b.cid and
NVL(a.type,'-99999') = NVL(b.type,'-99999') and
NVL(a.trefid,'-99999')=NVL(b.trefid,'-99999') and
NVL(a.ordrefid,'-99999')= NVL(b.ordrefid,'-99999') and
NVL(a.amount,'-99999')=NVL(b.amount,'-99999') and
NVL(a.paydt,TO_DATE('9999-12-31','YYYY-MM-DD'))=NVL(b.paydt,TO_DATE('9999-12-31','YYYY-MM-DD'))
);

Answer 2

虽然有些人可能不同意，但我支持从程序上运行大型、长时间运行的删除。在我看来，控制和跟踪进度要容易得多(您的DBA会更喜欢您;-)而且，也不确定为什么需要将table1加入到自己以识别重复项(我很好奇您是否曾经遇到过快照太老的问题。)您也不应该需要多个delete语句，所有重复的语句都应该在一个进程中处理。最后，您应该检查为什么每周都会不断地重新引入重复的内容，并且可能会更改加载过程(可能会进行合并/重新插入，而不是所有的插入)。

话虽如此，你可能会尝试这样的方法：

-- first create mat view to find all duplicates
create materialized view my_dups_mv
tablespace my_tablespace
build immediate
refresh complete on demand
as
select id,cid,type,trefid,ordrefid,amount,paydt, count(1) as cnt
from table1
group by id,cid,type,trefid,ordrefid,amount,paydt
having count(1) > 1;

-- dedup data (or put into procedure and schedule along with mat view refresh above)
declare
  -- make sure my_dups_mv is refreshed first
  cursor dup_cur is
  select * from my_dups_mv;

  type duprec_t is record(row_id rowid);
  duprec duprec_t;
  type duptab_t is table of duprec_t index by pls_integer;
  duptab duptab_t;

  l_ctr pls_integer := 0;
  l_dupcnt pls_integer := 0;
begin
  for rec in dup_cur
  loop
    l_ctr := l_ctr + 1;

    -- assuming needed indexes exist
    select rowid
    bulk collect into duptab
    from table1
    where id = rec.id
    and cid = rec.cid
    and type = rec.type
    and trefid = rec.trefid
    and ordrefid = rec.ordrefid
    and amount = rec.amount
    and paydt = rec.paydt
    -- order by whatever makes sense to make the "keeper" float to top
    order by loaddt desc
    ;

    for i in 2 .. duptab.count
    loop
      l_dupcnt := l_dupcnt + 1;
      delete from table1 where rowid = duptab(i).row_id;
    end loop;

    if (mod(l_ctr, 10000) = 0) then
      -- log to log table here (calling autonomous procedure you'll need to implement)
      insert_logtable('Table1 deletes', 'Commit reached, deleted ' || l_dupcnt || ' rows');
      commit;
    end if;

  end loop;
  commit;
end;

检查您的日志表以了解进度状态。

Answer 3

1.并行

alter session enable parallel dml;

DELETE /*+ PARALLEL */ FROM TABLE1 A WHERE loaddt < (
...

假设您拥有Enterprise，一个正常的服务器配置，并且您在11g上。如果您不在11g上，则并行语法略有不同。

2.减少内存需求

该计划显示了一个散列连接，这可能是一件好事。但是没有任何有用的过滤器，Oracle必须对整个表进行散列。(Tbone的查询，只使用GROUP BY，看起来更好，可能运行得更快。)但是它可能也会遇到同样的问题，试图对整个表进行排序或散列。)

如果散列不能放入内存中，则必须将其写入磁盘，这可能非常慢。由于您每周运行此查询，因此只需要一个表查看所有行。具体取决于它何时运行，您可以将类似的内容添加到查询的末尾：) where b.loaddt >= sysdate - 14。这可能会大大减少临时表空间的写入量。如果使用jakub.petr建议的分区策略，也可能会减少读取IO。

3.活动报告

如果您想确切地知道您的查询在做什么，请运行活动报告：

select dbms_sqltune.report_sql_monitor(sql_id => 'YOUR_SQL_ID_HERE', type => 'active')
from dual;

(将输出保存到.html文件中，并使用浏览器打开它。)