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

14

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

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

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

<code>delete from TABLE1 where ID=1000001356443294;
delete from TABLE1 where ID=1000001356443296;
etc...
</code>

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

<code>select ID from TABLE1 where ID=1000001356443294
select ID from TABLE1 where ID=1000001356443296
etc...
</code>

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

<code>delete from (select ID from TABLE1 where ID=1000001356443294);
delete from (select ID from TABLE1 where ID=1000001356443296);
etc...
</code>

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

<code>select ID from TABLE1 where ID=1000001356443294 or ID = 1000001356443296 or ...
</code>

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

<code>delete from (select ID from TABLE1 where ID=1000001356443294 or ID = 1000001356443296 or ...)
</code>

Хотя последний может быть самым быстрым, при дальнейшем тестировании он все еще очень медленный, когда масштабируется от таблицы с 9000 строк до даже до 200 000 таблиц строк (что по-прежнему составляет & lt; 1% от окончательного размера набора таблиц), где требуется тот же оператор14mins бежать. Хотя & gt; На 50% быстрее в строке, что все равно экстраполирует примерно до суток при работе с полным набором данных. У меня есть все основания полагать, что часть программного обеспечения, которую мы использовали для выполнения этой задачи, могла бы20mins.

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

Is there a better way to delete? Should I use a round of SELECT statements (i.e., like the second test) to discover which table any given row is in and then shoot off delete queries? Even that looks quite slow but... Is there anything else I can do to speed the deletes up? I don't have DBA-level access or knowledge.
Вы удаляете 30 миллионов строк из этих шести таблиц. Сколько строк, приблизительный, останется в каждой таблице? Сколько одновременных операций записи требуется для поддержки, пока это происходит? Adam Musch
@ chac - Только что попробовал, и это не работает. Я получаю ошибку:ORA-01795: maximum number of expressions in a list is 1000 (только в этом тестовом запросе более 7000) GIS-Jonathan
Чтобы удалить 1000, сколько времени требуется? это должно масштабироваться более или менее линейно ... CapelliC
пытатьсяdelete from TABLE1 where ID in (1000001356443294,1000001356443296,...) CapelliC
@ AdamMusch - я удаляю только несколько десятков тысяч строк из 30 миллионов. Намного меньше, чем 1% строк будут удалены. В течение этого периода не будет никаких писем (но, вероятно, много одновременного чтения). GIS-Jonathan

Ваш Ответ

4   ответа
1

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

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

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

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

0

Попробуй с утверждением 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
Отключение индекса во время удаления звучит замечательно - если только он не используется для обеспечения уникальности. Adam Musch
@ AdamMusch Индекс не обеспечивает уникальность (хотя он установлен как УНИКАЛЬНЫЙ). Причина, по которой он был создан, заключалась в том, что мой администратор БД сказал, что без этого Oracle будет выполнять полное сканирование таблицы для каждогоWHERE пункт; как и предполагал @Allan, этот индекс - тот же столбец, что и вwhere. Есть два других индекса, но оба они необходимы для приложений. GIS-Jonathan
Остерегайтесь перестроения индекса, это может быть настоящей болью CapelliC
@ chac - действительно ли необходимо перестроить индекс? Мои общие показания подразумевают, что индекс автоматически обновляется после любых изменений данных. Я нашел этоOracle сообщение в блоге но для меня это не имеет особого смысла (вероятно, это должен быть отдельный вопрос). GIS-Jonathan
Удаление индекса будет контрпродуктивным, если индекс находится в столбце, указанном вwhere оговорка. Allan
15

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

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

Consider Использование 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;
Вы бы повторили четыре утверждения для каждой таблицы, из которой вы хотите удалить. Так что при чистке отTABLE2, вы бы создали таблицуTABLE2_PURGE и так далее Adam Musch
Спасибо за варианты. (1) Я не удаляю много строк (<1%, как указано выше), поэтому дублирование таблицы может быть глупым. (2) Ваш второй вариант выглядит хорошо, так как идентификатор должен быть только в одной таблице, однако я не совсем понимаю, как его использовать. Откуда берется PURGE_IDS? Похоже, это отдельная таблица от TABLE1_PURGE, но тогда зачем создавать новую таблицу, когда они уже находятся в новой таблице (PURGE_IDS)? (3) Как отмечено в моем комментарии, во время этого процесса ничего не будет написано. Там будут некоторыеSELECT заявления бегут. GIS-Jonathan
Я предполагаю, что есть некоторый набор бизнес-правил, в которых вы фиксируете идентификаторы, которые вы использовали бы для удаления строк из набора таблиц. «Преимущество» создания TABLE1_PURGE заключается в том, что он будет захватывать идентификаторы, удаленные из TABLE_1, прежде чем вы их удалите, поэтому вы можете удалить эти идентификаторы из будущих чисток. Adam Musch
Я немного озадачен вторым вариантом. Есть 4 утверждения, создание, 2 удаления и удаление. Выполняете ли вы один раз создание и удаление для всей операции, и удаляет ли n раз столько желаний, сколько мы хотим, или выполняем их все n раз? sisharp
Я только что проверил это и, похоже, я не до конца понял, что он делал (отсюда и комментарий-вопрос). Указанный вами способ 2 невероятно быстр! На таблице 200 000 строк это заняло менее 3 секунд! Против таблицы с 3 миллионами строк (те же 7000 удалений) было всего 36 с! Это значительное увеличение скорости по сравнению со всеми другими опробованными методами, я не осознавал, что существует такая большая изменчивость. Большое спасибо GIS-Jonathan
0

и в моем случае он работает нормально.

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

Похожие вопросы