Оптимизировать запрос на удаление с большим количеством данных в оракуле

Я работаю над оракулом 9i. У меня есть таблица со 135 000 000 записей, разделенных на разделы, где каждый раздел имеет прибл. 10 000 000 строк. все проиндексировано и все.

Мне нужно удалить около 70 000 000 строк из этого в качестве нового бизнес-требования.

Поэтому я создал резервную копию строк, которые нужно удалить как отдельную таблицу.

Table1 <col1, col2........> -- main table (135,000,000 rows)

Table2 <col1, col2........> -- backup table (70,000,000 rows)

Попробовал приведенный ниже запрос на удаление.

Delete from table1 t1 where exists (select 1 from table2 t2 where t2.col1 = t1.col1)

но это занимает бесконечные часы.

затем попробовал

declare
cursor c1 is 
select col1 from table2;
c2 c1%rowtype;
cnt number;
begin
cnt :=0;
open c1;
loop
    fetch c1 into c2;
    exit when c1%notfound;

    delete from table1 t1 where t1.col1 = c2.col1;
    if cnt >= 100000 then
        commit;
    end if;
    cnt:=cnt+1;
end loop;
close c1;
end;

даже до сих пор его работает в течение более 12 часов. и до сих пор не завершена.

Обратите внимание, что в таблице 1 есть несколько индексов, а в таблице 2 — индекс столбца 1. анализируются все таблицы и индексы.

Пожалуйста, сообщите, есть ли способ оптимизации для этого сценария.

Спасибо, парни.


sql optimization plsql oracle9i
person Jude F'do    schedule 20.03.2012    source источник
comment
Если ваш код действительно выглядит так, вы будете фиксировать каждую строку после строки 100000, что, вероятно, не то, что вам нужно, и, конечно, будет медленным. Установите свой cnt:=0 после коммита   -  person Tilman Fliegel    schedule 16.01.2014
comment
Счетчик Ya нуждается в сбросе, я думаю, что это была опечатка. Но я предпочел сбросить индексы и воссоздать как лучший ответ   -  person Jude F'do    schedule 28.09.2015

Ответы (4)


arrow_upward
4
arrow_downward

Отбросьте все индексы (создайте резервную копию операторов create) Используйте оператор select, который использовался для создания резервной таблицы, создайте из него команду DELETE. Воссоздайте весь индекс

person Peter Kiss    schedule 20.03.2012

arrow_upward
4
arrow_downward

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

1) Создайте еще одну таблицу с идентичной структурой

2) Вставьте в новую таблицу записи, которые вы хотите сохранить (используйте прямую вставку пути, чтобы ускорить это)

3) Удалить старую таблицу

4) Переименуйте новую таблицу

person Nikhil    schedule 20.03.2012

arrow_upward
1
arrow_downward

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

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

CREATE TABLE rows_to_save
    AS SELECT *
         FROM table1
        WHERE <<criteria to select the 65 million rows you want to keep>>

TRUNCATE TABLE table1;

INSERT /*+ append */
  INTO table1
SELECT *
  FROM rows_to_save;

За исключением того, что вместо создания резервной таблицы было бы более эффективно просто выполнить оператор DELETE.

DELETE FROM table1
 WHERE <<criteria to select the 70 million rows you want to keep>>

Вы также можете удалить или отключить индексы и ограничения перед запуском DELETE.

person Justin Cave    schedule 21.03.2012

arrow_upward
0
arrow_downward

Я собираюсь ответить на этот вопрос, предполагая, что фильтрация по резервной таблице дешевле, но, вероятно, будет дешевле просто использовать отрицание критериев, которые вы использовали для заполнения резервной таблицы.

1) создать новую таблицу с той же структурой. Никаких индексов, ограничений или триггеров.

2) 

    select 'insert /*+ append nologging */ into new_table partition (' || n.partition_name || ') select * from old_table partition (' || o.partition_name || ') minus select * from bak_table partition (' || b.partition_name || ');'
    from all_tab_partitions o, all_tab_partitions n, all_tab_partitions b
    where o.partition_no = all( n.partition_no, b.partition_no)
      and o.table_name = 'OLD_TABLE' and o.table_owner = 'OWNER'
      and n.table_name = 'NEW_TABLE' and n.table_owner = 'OWNER'
      and b.table_name = 'BAK_TABLE' and b.table_owner = 'OWNER';
    -- note, I haven't run this it may need minor corrections in addition to the obvious substitutions

3) проверить и запустить результат предыдущего запроса

4) при необходимости создайте индексы, ограничения и триггеры

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

Возможно, вы могли бы работать быстрее с параллельной вставкой + параллельным выбором, но это, вероятно, не обязательно. Просто не делайте параллельный выбор без вставки и «изменения сеанса включения параллельного dml»

person llayland    schedule 21.03.2012
comment
nologging это не подсказка, вам нужно только append. - person Jon Heller; 22.03.2012