Оптимальный способ УДАЛИТЬ указанные строки из Oracle

У меня есть проект, который иногда должен удалять несколько десятков тысяч строк из одной из шести таблиц разных размеров, но между ними около 30 миллионов строк. Из-за структуры данных, которые мне дали, я не знаю, в какой из шести таблиц есть строка, которую нужно удалить, поэтому мне нужно выполнить все удаления для всех таблиц. Я построил INDEX для столбца ID, чтобы попытаться ускорить процесс, но его можно удалить, если это ускорит процесс.

Моя проблема в том, что я не могу найти эффективный способ действительно выполнить удаление. В целях моего тестирования я запускаю 7384 удаления строк в одной тестовой таблице, которая имеет около 9400 строк. Я протестировал ряд возможных решений для запросов в Oracle SQL Developer:

7384 раздельных DELETE Заявления заняли 203 секунды:

delete from TABLE1 where ID=1000001356443294;
delete from TABLE1 where ID=1000001356443296;
etc...

7384 раздельных SELECT Заявления заняли 57 секунд:

select ID from TABLE1 where ID=1000001356443294
select ID from TABLE1 where ID=1000001356443296
etc...

7384 раздельных DELETE from (SELECT) Заявления заняли 214 секунд:

delete from (select ID from TABLE1 where ID=1000001356443294);
delete from (select ID from TABLE1 where ID=1000001356443296);
etc...

1 SELECT заявление, которое имеет 7384 OR пункты, где взял 127,4 с:

select ID from TABLE1 where ID=1000001356443294 or ID = 1000001356443296 or ...

1 DELETE from (SELECT) заявление, которое имеет 7384 OR пункты, где взял 74.4s:

delete from (select ID from TABLE1 where ID=1000001356443294 or ID = 1000001356443296 or ...)

Хотя последний может быть самым быстрым, при дальнейшем тестировании он все еще очень медленный, когда масштабируется от таблицы с 9000 строк до даже 200000 таблиц строк (что по-прежнему < 1% от окончательного размера набора таблиц), где тот же оператор занимает 14 минут до запустить. Хотя> на 50% быстрее на строку, это все равно экстраполирует примерно до суток при работе с полным набором данных. У меня есть все основания полагать, что часть программного обеспечения, которую мы использовали для выполнения этой задачи, могла бы сделать это за 20 минут.

Итак, мои вопросы:

  • Есть ли лучший способ удалить?
  • Должен ли я использовать раунд SELECT операторы (т. е. как второй тест), чтобы узнать, в какой таблице находится данная строка, а затем отбросить запросы на удаление? Даже это выглядит довольно медленно, но...
  • Что еще я могу сделать, чтобы ускорить удаление? У меня нет доступа или знаний уровня DBA.
Источник

4 ответа

Решение

Перед тем, как ответить на мои вопросы, я бы так и поступил:

Минимизируйте количество заявлений и выполненную ими работу в относительном выражении.

Все сценарии предполагают, что у вас есть таблица идентификаторов (PURGE_IDS) удалить из TABLE_1, TABLE_2, так далее.

Рассмотрите возможность использования CREATE TABLE AS SELECT для действительно больших удалений

Если нет одновременного действия, и вы удаляете более 30% строк в одной или нескольких таблицах, не удаляйте; выполнить create table as select со строками, которые вы хотите сохранить, и замените новую таблицу на старую таблицу. INSERT /*+ APPEND */ ... NOLOGGING удивительно дешево, если вы можете себе это позволить. Даже если у вас есть какие-то параллельные действия, вы можете использовать онлайн-переопределение таблиц, чтобы перестроить таблицу на месте.

Не запускайте операторы DELETE, которые, как вы знаете, не будут удалять строки

Если значение идентификатора существует не более чем в одной из шести таблиц, следите за тем, какие идентификаторы вы удалили, и не пытайтесь удалить эти идентификаторы из других таблиц.

CREATE TABLE TABLE1_PURGE NOLOGGING
AS 
SELECT ID FROM PURGE_IDS INNER JOIN TABLE_1 ON PURGE_IDS.ID = TABLE_1.ID;

DELETE FROM TABLE1 WHERE ID IN (SELECT ID FROM TABLE1_PURGE);

DELETE FROM PURGE_IDS WHERE ID IN (SELECT ID FROM TABLE1_PURGE);

DROP TABLE TABLE1_PURGE;

и повтори.

Управление параллелизмом, если вам нужно

Другой способ - использовать PL/SQL зацикливание таблиц, выполняя оператор удаления с ограниченным числом строк. Это наиболее вероятно, если имеется значительная параллельная загрузка вставки / обновления / удаления для таблиц, для которых выполняется удаление.

declare
  l_sql varchar2(4000);
begin
  for i in (select table_name from all_tables 
             where table_name in ('TABLE_1', 'TABLE_2', ...)
             order by table_name);
  loop
    l_sql := 'delete from ' || i.table_name || 
             ' where id in (select id from purge_ids) ' || 
             '   and rownum <= 1000000';
    loop
      commit;
      execute immediate l_sql;
      exit when sql%rowcount <> 1000000;  -- if we delete less than 1,000,000
    end loop;                             -- no more rows need to be deleted!
  end loop;
  commit;
end;
Источник

Сохраните все идентификаторы для удаления в таблицу. Тогда есть 3 способа. 1) перебрать все идентификаторы в таблице, затем удалить по одной строке за интервал X фиксации. X может быть 100 или 1000. Он работает в среде OLTP, и вы можете контролировать блокировки.

2) Используйте Oracle Bulk Delete

3) Используйте коррелированный запрос на удаление.

Один запрос обычно выполняется быстрее, чем несколько запросов, из-за меньшего количества переключений контекста и, возможно, меньшего разбора.

Источник

Во-первых, было бы полезно отключить индекс во время удаления.

Попробуйте с утверждением MERGE INTO:
1) создайте временную таблицу с идентификаторами и дополнительным столбцом из TABLE1 и протестируйте с помощью следующего

MERGE INTO table1 src
USING (SELECT id,col1
         FROM test_merge_delete) tgt
ON (src.id = tgt.id)
WHEN MATCHED THEN
  UPDATE
     SET src.col1 = tgt.col1
  DELETE
   WHERE src.id = tgt.id
Источник

Я пробовал этот код, и он отлично работает в моем случае.

DELETE FROM NG_USR_0_CLIENT_GRID_NEW WHERE rowid IN
( SELECT rowid FROM
  (
      SELECT wi_name, relationship, ROW_NUMBER() OVER (ORDER BY rowid DESC) RN
      FROM NG_USR_0_CLIENT_GRID_NEW
      WHERE wi_name = 'NB-0000001385-Process'
  )
  WHERE RN=2
);
Источник
Другие вопросы по тегам