Как оптимизировать запрос на удаление (с подзапросом) в Oracle?

У меня есть запрос как:

 delete from tableA 
 where tableA.fk in (select id 
                     from tableB 
                     where tableB.column1='somevalue' 
                     and tableB.date between date1 and date2)
   ;

Таблица tableB содержит около 100 000 000 записей. Так

select id 
from tableB 
where  tableB.column1='somevalue' 
and tableB.date between date1 and date2

возвращает около 1 000 000 записей. Как результат - удаление не работает вообще - проблема с размером сегмента отката. Я не могу увеличить размер сегмента.

Как это можно выполнить?

Источник

3 ответа

Решение

Если вы заполняете сегмент отката, это, вероятно, связано с объемом данных, задействованных в вашей транзакции.

Я бы разделил на несколько кусков и зафиксировал каждый из них, что-то вроде:

 delete from tableA where tableA.fk in (
    select id from (
            select id from tableB where 
                tableB.column1='somevalue' and tableB.date between date1 and date2 and id in (select distinct fk from tableA.fk)

        )
        where rownum < XXXX 
    )

дайте XXXX желаемое значение (10000) или что-то еще, а затем выполните цикл выполнения этого оператора и не забудьте зафиксировать в каждой итерации.

Вы должны выполнить цикл, пока оператор delete не вернет 0 (количество затронутых строк).

ОБНОВЛЕННЫЙ ЗАПРОС Как уже отмечалось, это не даст наилучшей производительности, но решит проблему сегмента отката

Надеюсь, поможет

Источник

Удаление является самой дорогой операцией с точки зрения использования пространства отката (отмены), потому что мы должны хранить всю удаленную строку (тогда как для отмены оператора вставки просто требуется, чтобы база данных сохраняла идентификатор строки). Самое простое решение вашей проблемы - добавить больше файлов в табличное пространство UNDO.

Но вы говорите

"Я не могу увеличить размер сегмента"

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

Но если вы не будете биться с минотавром в центре обработки данных, все, что вы можете сделать, это изменить свой код. Вот один из вариантов. Учитывая это...

select id 
from tableB 
where  tableB.column1='somevalue' 
and tableB.date between date1 and date2

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

for i in 1..10 loop
    delete from tableA 
     where tableA.fk in (select id 
                         from tableB 
                         where tableB.column1='somevalue' 
                         and tableB.date between date1 + (3 * (i-1)) and  date1 + (3 * i)
       ;
    commit;
end loop

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

Источник

Что вы хотите сделать, это:

create table foo_bar  
as select  *  
from tableA   
where tableA.fk not in (select id from tableB where 
 tableB.column1='somevalue' and tableB.date between date1 and date2);

с последующим усечением + удаление исходной таблицы

truncate tableA  
drop tableA

Тогда переименуй foo_bar в tableA

alter table foo_bar  rename to tableA

Следует обязательно отключить все индексы.

КОММЕНТАРИИ

Я не могу бросить стол. Это всегда в использовании.

Вы наверняка можете. Вам нужно спрятать стол за view которая является просто виртуальной функцией и / или отложила некоторое время на обслуживание, потому что вы не можете удалить данные во время вставки. Теперь более типичным подходом является передача этих данных в materialized view который будет действовать как fact table, Это позволяет изменять базовую таблицу (tableA) в любое время, не опасаясь нанести вред пользовательским запросам по материализованному представлению.

Использование nologging Параметр уменьшит количество используемого отката. Вы не можете восстановить материализованное представление, созданное с nologging

Источник
Другие вопросы по тегам