Как избежать при каждой итерации цикла два inner join-а в select-е для delete?

Question

Как избежать при каждой итерации цикла два inner join-а в select-е, которые я использую для delete, может есть возможность результат select-a сохранить в переменную и переиспользовать ?

Удалять необходимо, указывая batchSize, т.к., большой объем данных

begin
loop
    delete from BpmsByteArrayEntityImpl where id in
    (select BA.id from BpmsByteArrayEntityImpl BA
    inner join BpmsHistoricDetailEntityImpl HDE on BA.id = HDE.byteArrayRefId
    inner join BpmsHistoricProcessInstanceEntityImpl HPI on HDE.processInstanceId = HPI.id
    where HPI.endTime <= sysdate - 1
    fetch next ${batchSize} rows only);
    EXIT WHEN sql%ROWCOUNT = 0;
    commit;
end loop;
end;

Использую СУБД Oracle 12c

Answer 1

Судя по алгоритму, он должен итеративно удалять из таблицы по batchSize записей за одну итерацию.

Это означает, что при каждой итерации вложенный SELECT возвращает новые id записей, которые надо удалить.

Потому ответ - нет, нельзя. Вернее нельзя сохранить результат селекта, так как он каждый раз нужен новый.

По поводу оптимизации самого селекта - возможность этого зависит уже от данных которые там лежат, и решить эту головоломку предстоит вам самим.

Answer 2

Для ускорения работы с SQL-запросами используются BULK COLLECT для выбора нескольких значений и цикл FORALL для выполнение SQL-инструкций единым целом.

Необходимо выбрать требуемые идентификаторы записей в коллекцию, а затем произвести изменения в цикле FORALL.

Простой пример

declare
  type t_arr is table of integer;

  v_arr t_arr;
begin
  select
    o.object_id bulk collect into v_arr
  from all_objects o
  where rownum < 100;

  dbms_output.put_line(v_arr.count);

  forall i in v_arr.first..v_arr.last 
    delete from z_temp d
    where d.id = v_arr(i);
end;

Не знаю насколько это поможет для удаления 10 млн.записей.

Answer 3

Надо так: fetch ... limit + bulk binding + rowid. Попробуйте, быстрее eщё не придумали:

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

declare
    batchSize constant number := 10000;
    type rowIDs is table of rowid;
    rids rowIDs;  
    cursor cur is 
        select rowid 
        from t1
        where created < sysdate-1;
begin 
    open cur; <<batches>> loop
        fetch cur bulk collect into rids limit batchSize;
        exit batches when rids.count = 0;
        forall ix in indices of rids
            delete from t1 where rowid = rids(ix);
        dbms_output.put_line (sql%rowcount||' rows deleted.');
    end loop; close cur;
end;
/

Результат (укорочен для наглядности):

10000 rows deleted.
10000 rows deleted.
...
10000 rows deleted.
9977 rows deleted.

Важно: здесь delete только для примера. Ни в коем случае не стоит с его помощью удалять большой объем данных.

БЛОГ НА HUSL

Как избежать при каждой итерации цикла два inner join-а в select-е для delete?

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