оптимизация дублирующего оператора удаления Oracle

Насколько большой стол? Если количество удаленных строк составляет до 12%, вы можете подумать об index. Не могли бы вы как-то разделить свою таблицу - например, по неделям, а затем сканировать только текущую неделю?

Может быть, это могло бы быть более эффективным. Когда вы используете агрегатную функцию, оракул должен пройти через все соответствующие строки (в вашем случае полное сканирование), но когда вы используете существования, он останавливается, когда найдено первое вхождение. (и, конечно, запрос был бы намного быстрее, если бы был один индекс на основе функции (из-за NVL) для всех столбцов в предложении where)

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'))
);

Хотя некоторые могут не согласиться, я сторонник процедурного запуска больших и длительных удалений. На мой взгляд, гораздо проще контролировать и отслеживать прогресс (и ваш администратор баз данных будет любить вас больше ;-) Кроме того, не уверен, почему вам нужно присоединять таблицу1 к себе, чтобы идентифицировать дубликаты (и мне было бы любопытно, если вы когда-нибудь столкнетесь с снимок слишком старых проблем с вашим текущим подходом). Вам также не нужно несколько операторов удаления, все дубликаты должны обрабатываться в одном процессе. Наконец, вы должны проверить, ПОЧЕМУ вы постоянно повторно вводите дубликаты каждую неделю, и, возможно, изменить процесс загрузки (возможно, выполняя слияние/upsert, а не все вставки).

Тем не менее, вы можете попробовать что-то вроде:

-- 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;

Проверьте свою таблицу журналов для статуса прогресса.

<сильный>1. Параллельно

alter session enable parallel dml;

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

Предполагая, что у вас есть Enterprise Edition, разумная конфигурация сервера и вы используете 11g. Если вы не используете 11g, параллельный синтаксис немного отличается.

<сильный>2. Уменьшите требования к памяти

На плане показано хэш-соединение, что, вероятно, хорошо. Но без каких-либо полезных фильтров Oracle приходится хэшировать всю таблицу. (Запрос Tbone, который использует только GROUP BY, выглядит лучше и может работать быстрее. Но он также, вероятно, столкнется с той же проблемой, пытаясь отсортировать или хешировать всю таблицу.)

Если хэш не помещается в памяти, его необходимо записать на диск, что может быть очень медленным. Поскольку вы запускаете этот запрос каждую неделю, только одна из таблиц должна просматривать все строки. В зависимости от того, когда именно он запускается, вы можете добавить что-то вроде этого в конец запроса: ) where b.loaddt >= sysdate - 14. Это может значительно уменьшить количество операций записи во временное табличное пространство. И это также может уменьшить количество операций ввода-вывода при чтении, если вы используете какую-либо стратегию разбиения, например предложенную jakub.petr.

<сильный>3. Активный отчет

Если вы хотите точно знать, что делает ваш запрос, запустите Active Report:

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

(Сохраните результат в файле .html и откройте его в браузере.)