Удаление БОЛЬШОГО КОЛИЧЕСТВА данных в Oracle

Самый быстрый путь состоит в том, чтобы создать новый с 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, выполняете a 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 не желателен, потому что может создать Снимок слишком старое сообщение, должное, что Вы фиксируете (и заключаете сделку) с открытым курсором (циклично выполненный), Вы хотите продолжить использовать его. Oracle позволяет его, но это не хорошая практика.

ОБНОВЛЕНИЕ: Почему я могу удостовериться, что последний блок PLSQL собирается работать? Поскольку я предполагаю что:

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

При выполнении крупных удалений в 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 для Вашего запроса удаления (который это по всей вероятности сделает), и временная таблица не впишется 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
       )

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

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

Если Ваш исходный SQL занимает очень долгое время, некоторый параллельный SQLs может медленно работать, поскольку они должны использовать ОТМЕНУ для восстановления версии данных без незафиксированных изменений.

Компромисс может быть чем-то как

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 означает более частые фиксации и (вероятно) уменьшенное влияние на другие сессии с точки зрения необходимости применить отмену. Однако в зависимости от планов выполнения, может быть другое влияние, и, вероятно, потребуется больше времени в целом. Технически 'ДЛЯ' части цикла является ненужным, поскольку ВЫХОД закончит цикл. Но я параноик о неограниченных циклах, поскольку это - боль для уничтожения сессии, если они действительно застревают.

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

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

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

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

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

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

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(...) строки в файле удаляют-tables.sql

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

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 || 's 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 || 's 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;

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

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

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