Как можно ускорить delete 10 миллионов строк?

Question

Есть анонимный блок на удаление 10 миллионов записей, выполняется очень долго. Как можно ускорить его? Использую СУБД ORACLE 12C

declare
batchSize number := 100000;
batchCount number;
totalCount number;
begin
select count(HD.id)
into totalCount
from BpmsHistoricDetailEntity HD
         inner join BpmsHistoricProcessInstanceEntityImpl HPI
                    on HD.processInstanceId = HPI.id
where HPI.endTime is not null
  and HPI.endTime < sysdate - 1;

if totalCount < batchSize then
    batchSize := totalCount;
    batchCount := 1;
else
    batchCount := floor(totalCount / batchSize) + 1;
end if;

for i in 1..batchCount
    loop
delete from BpmsHistoricDetailEntityImpl where id in
(select HD.id from BpmsHistoricDetailEntityImpl HD
inner join BpmsHistoricProcessInstanceEntityImpl HPI on HD.processInstanceId = HPI.id
where HPI.endTime is not null and HPI.endTime <= sysdate -1
order by HD.id
fetch next batchSize rows only);
        commit;
    end loop;
end;

Answer 1

Удаление с DELETE это довольно дорогая операция в БД по сравнению с другими DML. Постранство от удаленных данных не освобождается автоматически, т.е. таблица будет сильно дефрагментирована, индексы разбалансированы, указатели HWM и low-HWM не переместятся. Другими словами, работать с таблицей для вставки новых данных будет крайне неэффективно.

Золотое правило -- если будет удалено ~10% и более от общего объёма данных, то будет гораздо лучше просто пересоздать таблицу с данными, которые не должны быть удалены.

Несколько упрощённый пример (это не единственный способ):

create table mytab as 
    select rownum id, sysdate-level/24/60 created from dual
    connect by level <= 10e4;

create table mytabnew nologging as
    select * from mytab where created >= trunc (sysdate)-1;

rename mytab to mytabold; 

Table renamed.

rename mytabnew to mytab; 

Table renamed.

Все ограничения (constraints), разрешения (grants) и индексы должны быть перенесены со старой на новую таблицу. Как убедитесь, что всё работает, то:

drop table mytabold purge;

Если таблица постоянно заполняется новыми данными и переодически должны быть удалены старые данные, то однозначно, таблицу надо секционировать по ключу HPI.endTime, чтобы удалять целые секции.

БЛОГ НА HUSL

Как можно ускорить delete 10 миллионов строк?

Ответы (1 шт):