оптимизиране на изявление за изтриване на дублиран Oracle

Колко голяма е масата? Ако броят на изтритите редове е до 12%, тогава можете да помислите за индекс. Бихте ли могли по някакъв начин да разделите таблицата си - например седмица по седмица и след това да сканирате само действителната седмица?

Може би това ще бъде по-ефективно. Когато използвате агрегатна функция, тогава Oracle трябва да премине през всички съответни редове (във вашия случай пълно сканиране), но когато използвате съществува, той спира, когато бъде намерено първото срещане. (и разбира се, заявката би била много по-бърза, когато имаше един базиран на функция (заради 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'))
);

Въпреки че някои може да не са съгласни, аз съм привърженик на процедурно изпълнение на големи, продължителни изтривания. По мое мнение е много по-лесно да контролирате и проследявате напредъка (и вашият DBA ще ви харесва повече ;-) Освен това не съм сигурен защо трябва да присъединявате таблица1 към себе си, за да идентифицирате дубликати (и бих бил любопитен, ако някога се натъкнете на моментна снимка на твърде стари проблеми с настоящия ви подход). Също така не трябва да се нуждаете от множество изрази за изтриване, всички дубликати трябва да се обработват в един процес. И накрая, трябва да проверите ЗАЩО непрекъснато въвеждате дубликати всяка седмица и може би да промените процеса на зареждане (може би да направите сливане/подмяна, а не всички вмъквания).

Въпреки това може да опитате нещо като:

-- 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. Това може значително да намали обема на запис във временното таблично пространство. И може също така да намали IO за четене, ако използвате някаква стратегия за разделяне, като предложената от jakub.petr.

3. Активен отчет

Ако искате да знаете точно какво прави вашата заявка, стартирайте активния отчет:

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

(Запазете резултата в .html файл и го отворете с браузър.)