Подтвердить что ты не робот

Удаление большого количества данных в Oracle

Я точно не являюсь пользователем базы данных, и большая часть моей работы с db была с MySQL, так что простите меня, если что-то в этом вопросе невероятно наивно.

Мне нужно удалить 5.5 миллионов строк из таблицы Oracle, содержащей около 100 миллионов строк. У меня есть все ID строк, которые мне нужно удалить во временной таблице. Если бы это было всего несколько тысяч строк, я бы сделал это:

DELETE FROM table_name WHERE id IN (SELECT id FROM temp_table);
COMMIT;

Есть ли что-нибудь, о чем я должен знать, и/или делать по-другому, потому что это 5,5 миллионов строк? Я думал о выполнении цикла, что-то вроде этого:

DECLARE
  vCT NUMBER(38) := 0;

BEGIN
  FOR t IN (SELECT id FROM temp_table) LOOP
    DELETE FROM table_name WHERE id = t.id;
    vCT := vCT + 1;
    IF MOD(vCT,200000) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;

Прежде всего - это то, что я думаю, что это - пакетная обработка составляет 200 000 штук за раз? Предполагая, что это так, я по-прежнему не уверен, что лучше создать 5.5 миллионов SQL-операторов и совершить в партиях 200 000, или иметь один оператор SQL и совершить все сразу.

Идеи? Лучшие практики?

РЕДАКТИРОВАТЬ. Я запустил первый вариант, один оператор удаления, и для разработки потребовалось всего 2 часа. Исходя из этого, он поставил в очередь на производство.

4b9b3361

ОТВЕТЫ

Ответ 1

Первый подход лучше, потому что вы даете оптимизатору запросов четкое представление о том, что вы пытаетесь сделать, вместо того, чтобы скрывать его. Механизм базы данных может использовать другой подход к удалению 5,5 м (или 5,5% от таблицы), а не к удалению 200 тыс. (Или 0,2%).

Здесь также статья о массивном DELETE в Oracle, который вы, возможно, захотите прочитать.

Ответ 2

Самый быстрый способ - создать новый с CREATE TABLE AS SELECT с помощью параметра NOLOGGING. Я имею в виду:

ALTER TABLE table_to_delete RENAME TO tmp;
CREATE TABLE table_to_delete NOLOGGING AS SELECT .... ;

Конечно, вам нужно воссоздать ограничения без проверки, индексы с nologging, гранты,... но очень быстро.

Если у вас возникли проблемы с производством, вы можете сделать следующее:

ALTER TABLE table_to_delete RENAME to tmp;
CREATE VIEW table_to_delete AS SELECT * FROM tmp;
-- Until there can be instantly
CREATE TABLE new_table NOLOGGING AS SELECT .... FROM tmp WHERE ...;
<create indexes with nologging>
<create constraints with novalidate>
<create other things...>
-- From here ...
DROP VIEW table_to_delete;
ALTER TABLE new_table RENAME TO table_to_delete;
-- To here, also instantly

Вы заботитесь о:

  • Сохраненные процедуры могут быть недействительными, но они будут перекомпилированы во второй раз. Вы должны проверить его.
  • NOLOGGING означает, что генерируется минимальное. Если у вас есть роль DBA, запустите ALTER SYSTEM CHECKPOINT, чтобы не потерять данные, если произошел сбой экземпляра.
  • Для NOLOGGING табличное пространство должно быть также в NOLOGGING.

Другой вариант лучше, чем создание вкладок вставки:

-- Create table with ids
DELETE FROM table_to_delete
 WHERE ID in (SELECT ID FROM table_with_ids WHERE ROWNUM < 100000);
DELETE FROM table_with_ids WHERE ROWNUM < 100000;
COMMIT;
-- Run this 50 times ;-)

Выбор PLSQL не рекомендуется, поскольку может создать слишком старое сообщение Snapshot из-за того, что вы совершаете (и закрываете транзакцию) с помощью открытого курсора (зацикленного), который вы хотите продолжить использовать. Oracle позволяет это, но это не очень хорошая практика.

UPDATE: Почему я могу гарантировать, что последний PLSQL-блок будет работать? Потому что я полагаю, что:

  • Никакая другая не использует эту временную таблицу по какой-либо причине (dba или задание, собирающее статистику, задачи dab, такие как перемещение, вставка записей и т.д.). Это может быть обеспечено, потому что это вспомогательная таблица только для этого.
  • Затем, с последним утверждением, запрос будет выполнен точно с тем же планом и будет возвращать строки с тем же порядком.

Ответ 3

При выполнении массивных удалений в Oracle убедитесь, что у вас не закончилось UNDO SEGMENTS.

При выполнении DML, Oracle сначала записывает все изменения в журнал REDO (старые данные вместе с новыми данными).

Когда журнал REDO заполняется или происходит тайм-аут, Oracle выполняет log synchronization: он записывает данные new в файлы данных (в вашем случае маркирует блоки файлов данных как свободные) и записывает старые данные в табличное пространство UNDO (чтобы он оставался видимым для одновременных транзакций, пока вы не commit ваши изменения).

Когда вы совершаете свои изменения, пространство в сегментах UNDO, занятых транзакцией yuor, освобождается.

Это означает, что если вы удаляете строки 5M строк, вам нужно иметь место для all этих строк в сегментах UNDO, чтобы сначала их можно было перенести (all at once) и удаляется только после фиксации.

Это также означает, что параллельные запросы (если они есть) необходимо будет читать из REDO журналов или UNDO сегментов при выполнении сканирования таблицы. Это не самый быстрый способ доступа к данным.

Это также означает, что если оптимизатор выберет HASH JOIN для вашего запроса на удаление (что, скорее всего, это произойдет), а таблица temp не будет вписываться в HASH_AREA_SIZE (что, скорее всего, будет) то для запроса потребуется several сканирование по большой таблице, а некоторые части таблицы будут перенесены в REDO или UNDO.

Учитывая все сказанное выше, вы, вероятно, лучше удаляете данные в 200,000 кусках и фиксируете изменения между ними.

Таким образом, вы, во-первых, избавитесь от описанных выше проблем и, во-вторых, оптимизируете свой HASH_JOIN, поскольку у вас будет одинаковое количество чтений, но сами чтения станут более эффективными.

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

Чтобы сделать это, убедитесь, что ваша временная таблица имеет первичный ключ на ID и переписывает ваш запрос следующим образом:

DELETE  
FROM   (
       SELECT  /*+ USE_NL(tt, tn) */
               tn.id
       FROM    temp_table tt, table_name tn
       WHERE   tn.id = tt.id
       )

Чтобы этот запрос работал, вам нужно иметь первичный ключ temp_table.

Сравните его со следующим:

DELETE  
FROM   (
       SELECT  /*+ USE_HASH(tn tt) */
               tn.id
       FROM    temp_table tt, table_name tn
       WHERE   tn.id = tt.id
       )

посмотрите, что быстрее и придерживайтесь этого.

Ответ 4

Лучше делать все сразу, как в первом примере. Но я определенно перейду через это с вашим DBA первым, так как они могут захотеть вернуть блоки, которые вы больше не используете после очистки. Кроме того, могут возникнуть проблемы с планированием, которые обычно не видны с точки зрения пользователя.

Ответ 5

Я бы рекомендовал запустить это как одно удаление.

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

Возможно, вам понадобятся некоторые способы проверить прогресс удаления при его запуске. См. Как проверить базу данных оракула для длинных запросов?

Как и другие люди, если вы хотите проверить воду, вы можете поставить: rownum < 10000 в конце вашего запроса.

Ответ 6

Если ваш исходный SQL занимает очень много времени, некоторые параллельные SQL-запросы могут работать медленно, поскольку они должны использовать UNDO для восстановления версии данных без ваших незафиксированных изменений.

Компромисс может быть чем-то вроде

FOR i in 1..100 LOOP
  DELETE FROM table_name WHERE id IN (SELECT id FROM temp_table) AND ROWNUM < 100000;
  EXIT WHEN SQL%ROWCOUNT = 0;
  COMMIT;
END LOOP;

Вы можете настроить ROWNUM по мере необходимости. Меньший ROWNUM означает более частое совершение и (возможно) снижение воздействия на другие сессии с точки зрения необходимости отмены отмены. Однако, в зависимости от планов выполнения, могут быть другие воздействия, и, вероятно, это займет больше времени. Технически часть цикла "FOR" не нужна, так как EXIT завершает цикл. Но я параноик о неограниченных циклах, потому что это боль, чтобы убить сессию, если они застряли.

Ответ 7

В прошлом я делал что-то подобное с Oracle 7, где мне приходилось удалять миллионы строк из тысяч таблиц. Для всех раундов производительности и особенно больших удалений (миллионы строк плюс в одной таблице) этот script работал хорошо.

Вам придется немного изменить его (например, проверить пользователей/пароли, а также получить сегменты отката). Также вам нужно обсудить это с вашим администратором базы данных и запустить его в среде TEST. Сказав все это, это довольно легко. Функция delete_sql() просматривает пакет строк в указанной вами таблице, а затем удаляет их пакетным путем. Например:

exec delete_sql('MSF710', 'select rowid from msf710 s where  (s.equip_no, s.eq_tran_date, s.comp_data, s.rec_710_type, s.seq_710_no) not in  (select c.equip_no, c.eq_tran_date, c.comp_data, c.rec_710_type, c.seq_710_no  from  msf710_sched_comm c)', 500);

В приведенном выше примере удаляется 500 записей за раз из таблицы MSF170 на основе оператора sql.

Если вам нужно удалить данные из нескольких таблиц, просто добавьте дополнительные строки exec delete_sql(...) в файл delete-tables.sql

О, и не забудьте вернуть сегменты отката в онлайн, это не в script.

spool delete-tables.log;
connect system/SYSTEM_PASSWORD
alter rollback segment r01 offline;
alter rollback segment r02 offline;
alter rollback segment r03 offline;
alter rollback segment r04 offline;

connect mims_3015/USER_PASSWORD

CREATE OR REPLACE PROCEDURE delete_sql (myTable in VARCHAR2, mySql in VARCHAR2, commit_size in number) is
  i           INTEGER;
  sel_id      INTEGER;
  del_id      INTEGER;
  exec_sel    INTEGER;
  exec_del    INTEGER;
  del_rowid   ROWID;

  start_date  DATE;
  end_date    DATE;
  s_date      VARCHAR2(1000);
  e_date      VARCHAR2(1000);
  tt          FLOAT;
  lrc         integer;


BEGIN
  --dbms_output.put_line('SQL is ' || mySql);
  i := 0;
  start_date:= SYSDATE;
  s_date:=TO_CHAR(start_date,'DD/MM/YY HH24:MI:SS');


  --dbms_output.put_line('Deleting ' || myTable);
  sel_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(sel_id,mySql,dbms_sql.v7);
  DBMS_SQL.DEFINE_COLUMN_ROWID(sel_id,1,del_rowid);
  exec_sel := DBMS_SQL.EXECUTE(sel_id);
  del_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(del_id,'delete from ' || myTable || ' where rowid = :del_rowid',dbms_sql.v7);
 LOOP
   IF DBMS_SQL.FETCH_ROWS(sel_id) >0 THEN
      DBMS_SQL.COLUMN_VALUE(sel_id,1,del_rowid);
      lrc := dbms_sql.last_row_count;
      DBMS_SQL.BIND_VARIABLE(del_id,'del_rowid',del_rowid);
      exec_del := DBMS_SQL.EXECUTE(del_id);

      -- you need to get the last_row_count earlier as it changes.
      if mod(lrc,commit_size) = 0 then
        i := i + 1;
        --dbms_output.put_line(myTable || ' Commiting Delete no ' || i || ', Rowcount : ' || lrc);
        COMMIT;
      end if;
   ELSE 
       exit;
   END IF;
 END LOOP;
  i := i + 1;
  --dbms_output.put_line(myTable || ' Final Commiting Delete no ' || i || ', Rowcount : ' || dbms_sql.last_row_count);
  COMMIT;
  DBMS_SQL.CLOSE_CURSOR(sel_id);
  DBMS_SQL.CLOSE_CURSOR(del_id);

  end_date := SYSDATE;
  e_date := TO_CHAR(end_date,'DD/MM/YY HH24:MI:SS');
  tt:= trunc((end_date - start_date) * 24 * 60 * 60,2);
  dbms_output.put_line('Deleted ' || myTable || ' Time taken is ' || tt ||  from ' || s_date || ' to ' || e_date || ' in ' || i || ' deletes and Rows = ' || dbms_sql.last_row_count);

END;
/

CREATE OR REPLACE PROCEDURE delete_test (myTable in VARCHAR2, mySql in VARCHAR2, commit_size in number) is
  i integer;
  start_date DATE;
  end_date DATE;
  s_date VARCHAR2(1000);
  e_date VARCHAR2(1000);
  tt FLOAT;
BEGIN
  start_date:= SYSDATE;
  s_date:=TO_CHAR(start_date,'DD/MM/YY HH24:MI:SS');
  i := 0;
  i := i + 1;
  dbms_output.put_line(i || ' SQL is ' || mySql);
  end_date := SYSDATE;
  e_date := TO_CHAR(end_date,'DD/MM/YY HH24:MI:SS');
  tt:= round((end_date - start_date) * 24 * 60 * 60,2);
  dbms_output.put_line(i || ' Time taken is ' || tt ||  from ' || s_date || ' to ' || e_date);
END;
/

show errors procedure delete_sql
show errors procedure delete_test

SET SERVEROUTPUT ON FORMAT WRAP SIZE 200000; 

exec delete_sql('MSF710', 'select rowid from msf710 s where  (s.equip_no, s.eq_tran_date, s.comp_data, s.rec_710_type, s.seq_710_no) not in  (select c.equip_no, c.eq_tran_date, c.comp_data, c.rec_710_type, c.seq_710_no  from  msf710_sched_comm c)', 500);






spool off;

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

Ответ 8

Все ответы здесь замечательные, нужно добавить только одно: если вы хотите удалить все из записей в таблице и уверен, вы выиграли ' t нужно откат, тогда вы хотите использовать команду truncate table.

(В вашем случае вам нужно только удалить подмножество, но для кого-либо, скрывающегося с аналогичной проблемой, я думал, что добавлю это)

Ответ 9

Самый простой способ для меня: -

DECLARE
L_exit_flag VARCHAR2(2):='N';
L_row_count NUMBER:= 0;

BEGIN
   :exit_code        :=0;
   LOOP
      DELETE table_name
       WHERE condition(s) AND ROWNUM <= 200000;
       L_row_count := L_row_count + SQL%ROWCOUNT;
       IF SQL%ROWCOUNT = 0 THEN
          COMMIT;
          :exit_code :=0;
          L_exit_flag := 'Y';
       END IF;
      COMMIT;
      IF L_exit_flag = 'Y'
      THEN
         DBMS_OUTPUT.PUT_LINE ('Finally Number of Records Deleted : '||L_row_count);
         EXIT;
      END IF;
   END LOOP;
   --DBMS_OUTPUT.PUT_LINE ('Finally Number of Records Deleted : '||L_row_count);
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE ('Error Code: '||SQLCODE);
      DBMS_OUTPUT.PUT_LINE ('Error Message: '||SUBSTR (SQLERRM, 1, 240));
      :exit_code := 255;
END;