#sql #oracle #subquery #oracle12c #sql-delete
#sql #Oracle #подзапрос #oracle12c #sql-удалить
Вопрос:
Очевидные запросы
delete from in_pipe
where id in (
select id
from in_pipe
where id not in
(select distinct inpipeid from out_pipe)
fetch first 1000 rows only
)
или
delete from in_pipe
where id in (
select i.id
from in_pipe i
left join out_pipe o on o.inpipeid = i.id
where o.id is null
fetch first 1000 rows only
)
Существует primary key index on in_pipe.id и out_pipe.inpipeid имеет индекс CREATE INDEX ix_outpipe_inpipeid ON out_pipe(inpipeid)
Оба этих запроса выполнят задание, и планы выполнения выглядят нормально.
НО я боюсь за производительность этих запросов, как только мы перейдем к производству, а таблицы будут содержать миллионы строк (десятки миллионов). Производительность очистки не критична, но я боюсь, что эти запросы никогда не завершатся.
Очистка не должна влиять на производительность удалений / вставок из out_pipe или in_pipe, поэтому я бы не стал использовать триггер для этого. Я бы предпочел, чтобы эта очистка выполнялась в фоновом режиме во время простоя. Это можно (и нужно) делать постепенно.
Так что, я думаю, я ищу умные идеи…
Редактировать: я думаю просматривать идентификаторы in_pipe пакетно, начиная с самого низкого и двигаясь вверх, и проверяя наличие пакета в out_pipe, пока я не дойду до конца, а затем снова начну с начала.
Комментарии:
1. Это oracle, прошло некоторое время, но можете ли вы выполнять циклы вокруг вашего оператора delete и удалять верхние 1000 или около того для каждого удаления, но делать это в циклах? Я часто делаю это в MS SQL, но не уверен в синтаксисе oracle, но хотел дать совет.
2. Спасибо @Brad, это то, что я бы сделал (отсюда и выборка первых 1000 строк), но я боюсь, что даже в одном цикле запроса возникнут проблемы с завершением миллионов строк
Ответ №1:
Как насчет двух с половиной шагов?
Первый шаг: таблица ID s, которые не используются:
create table not_used as
select id from in_pipe
minus
select inpipeid from out_pipe;
Половина шага: индекс:
create index i1nu on not_used (id);
Второй шаг: удалите ID неиспользуемые строки:
delete from in_pipe a
where exists (select null
from not_used n
where n.id = a.id
);
Комментарии:
1. Это выглядит весьма многообещающе! Немного тяжелее, чем я надеялся, но если это выполняется, например, раз в месяц, это выполнимо. Я предполагаю, что вопрос в следующем: выполняется ли вставка во временную таблицу через select id минус select inpipeid быстрее / эффективнее, чем выполнение подзапроса / соединения?
2. Я не знаю, но — если вы попробуете и сравните все варианты, которые вы собрали в этом обсуждении, — вы, вероятно, найдете победителя и используете его впоследствии. Убедитесь, что вы проиндексировали столбцы, включенные в предложения WHERE, соберите статистику, сравните планы объяснений (без выполнения какого-либо кода) и надейтесь на лучшее. Удачи вам!
Ответ №2:
Я бы рекомендовал not exists :
delete from in_pipe i
where not exists (
select 1 from out_pipe o where o.inpipeid = i.id
)
Комментарии:
1. Синтаксис стал чище, но есть ли здесь что-нибудь, что делает запрос более производительным? План выполнения выглядит примерно так же, как и другие (join — худший); этот запрос имеет полное сканирование индекса первичного ключа (как и другие).
2. Это всего лишь один уровень подзапроса против 2 в вашем исходном коде. Для повышения производительности требуется включить индекс
out_pipe(inpipeid ), чтобы подзапрос выполнялся быстро.3. Единственная причина, по которой в моем исходном запросе есть еще один подзапрос, заключается в том, что они выбирают первые 1000 идентификаторов и удаляют их, вместо того, чтобы пытаться удалить все за один раз. Исходные запросы можно было бы переписать на delete from, просто не указав id select . Существование указываемого вами индекса уже упоминается в вопросе. Я сомневаюсь, что этот заданный вами запрос будет работать лучше.