Рефакторинг медленного курсора plsql с помощью оператора обновления With
У меня медленный курсор plsql, где я обновляю столбец в таблице, поэтому я хотел заменить этот курсор одним оператором sql (хотя и несколько сложным). Это то, что я пробовал до сих пор
with gexc_cursor as (
select *
from ( select tmp.*, rownum rn from (
select id, oib_dob as oib, opis_grupe, smjer from GRU_EXC order by ID ) tmp )
where rn between 1 and 5 ),
gd_cursor as (
select gc.id, gd.id_grupe from GRU_DOK gd, gexc_cursor gc where
gc.smjer = 'ON' AND gd.opis_grupe = gc.opis_grupe
and gd.id_vlasnika in ( select id_vlasnika from VLAS where oib_vlasnika = gc.oib )
union
select gc.id, gd.id_grupe from GRU_DOK gd, gexc_cursor gc where
gc.smjer = 'OFF' AND gd.opis_grupe = gc.opis_grupe
and gd.id_vlasnika not in ( select id_vlasnika from VLAS where oib_vlasnika = gc.oib )
)
update GRU_EXC set id_grupe = gd_cursor.id_grupe
where id = gd_cursor.id;
--ORA-00928: missing SELECT keyword
Следующий блок sql выполняется, но не работает должным образом (он всегда обновляет все строки, а не только соответствующие строки).
update GRU_EXC ge set id_grupe = (
with gexc_cursor as (
select *
from ( select tmp.*, rownum rn from (
select id, oib_dob as oib, opis_grupe, smjer from GRU_EXC order by ID ) tmp )
where rn between 1 and 5 )
select gd.id_grupe from GRU_DOK gd, gexc_cursor gc
where gc.smjer = 'ON' and gc.id = ge.id and gd.opis_grupe = gc.opis_grupe
and gd.id_vlasnika in ( select id_vlasnika from VLAS where oib_vlasnika = gc.oib )
UNION
select gd.id_grupe from GRU_DOK gd, gexc_cursor gc
where gc.smjer = 'OFF' and gc.id = ge.id and gd.opis_grupe = gc.opis_grupe
and gd.id_vlasnika not in ( select id_vlasnika from VLAS where oib_vlasnika = gc.oib )
)
@Barbaros Özhan, для части разбиения по страницам необходимо условие order by. Объединение существует потому, что у меня есть два разных оператора выбора в зависимости от того, включен ли gc.smjer или выключен (вероятно, его следует заменить на случай выбора). Я могу предоставить решение для курсора, если вы считаете, что это поможет.
Кроме того, вы могли бы предпочесть NOT EXISTS вместо NOT IN и ROW_NUMBER() аналитическую функцию вместо rownum.
@Барбарос Ожан, спасибо за попытку помочь, но твои предложения все равно не помогут. К сожалению, на SO мало таких примеров, поэтому я застрял и рассматриваю альтернативы.
Каждая строка обновляется, поскольку в обновлении нет предложения where. Подзапрос коррелирует, но это не означает, что обновляются только соответствующие строки; те, у которых нет совпадений, будут просто установлены на ноль. Может быть, вы хотите исследовать merge?
Утверждение merge — это определенно то, что я бы рассматривал вместо утверждения update, как предлагает Алекс. Однако мне интересно знать, почему вы вообще «отправляете» обновление? Мне это кажется странным: вы действительно обновляете только первые 5 строк или выполняете цикл обновления, выполняя 5 строк за раз? Возможно, если бы вы предоставили нам контекст кода PL/SQL, который вы пытаетесь заменить, это помогло бы нам лучше вам помочь.
@Alex Poole, ну, в первом операторе with есть предложениеwhere, но я не смог правильно понять синтаксис. Не могли бы вы привести простой пример использования слияния?
@Boneist, да, я собираюсь предоставить код plsql, который пытаюсь оптимизировать.
Пожалуйста, отредактируйте вопрос и включите минимальный воспроизводимый пример с утверждениями CREATE TABLE и INSERT для ваших выборочных данных; и английское (не кодовое) объяснение логики, которую вы пытаетесь реализовать; и ожидаемый результат для вашего образца данных. На данный момент вы включили код, но не объяснили или не привели пример проблемы, поэтому очень сложно перепроектировать код, чтобы попытаться выяснить, чего вы пытаетесь достичь.
Есть ли причина, по которой ваш код обновляется через нумерацию страниц? Я думаю, вы могли бы обновить всю таблицу с помощью гораздо более простого оператора merge, если бы вам не нужно было выполнять нумерацию страниц!
@Boneist, это было требование, поэтому я просто добавил нумерацию страниц.
Ответы 2
Вы можете использовать оператор MERGE и объединить запрос UNION в один запрос, используя AND и OR:
MERGE INTO gru_exc dst
USING (
SELECT (
SELECT gd.id_grupe
FROM gru_dok gd
LEFT OUTER JOIN id_vlasnika iv
ON iv.oib_vlasnika = ge.oib_dob
WHERE gd.opis_grupe = ge.opis_grupe
AND ( ( ge.smjer = 'ON'
AND iv.oib_vlasnika IS NOT NULL )
OR ( ge.smjer = 'OFF'
AND iv.oib_vlasnika IS NULL )
)
-- FETCH FIRST ROW ONLY
) AS id_grupe
FROM (
SELECT id,
oib_dob,
opis_grupe,
smjer
FROM gru_exc
ORDER BY id
OFFSET 0 ROWS
FETCH FIRST 5 ROWS ONLY
) ge
) src
ON (src.ROWID = dst.ROWID)
WHEN MATCHED THEN
UPDATE
SET dst.id_grupe = src.id_grupe
Если у вас есть образец данных:
CREATE TABLE gru_exc (id, oib_dob, opis_grupe, smjer, id_grupe) AS
SELECT 1, 1, 1, 'ON', CAST(NULL AS NUMBER) FROM DUAL UNION ALL
SELECT 2, 2, 2, 'OFF', NULL FROM DUAL;
CREATE TABLE gru_dok (opis_grupe, oib_dob, id_grupe) AS
SELECT 1, 1, 3 FROM DUAL UNION ALL
SELECT 2, 2, 4 FROM DUAL;
CREATE TABLE id_vlasnika (oib_vlasnika) AS
SELECT 1 FROM DUAL;
Затем после MERGE таблица содержит:
спасибо за это, я посмотрю. Небольшое уточнение: перед обновлением GRU_EXC.id_grupe имеет значение null, поэтому я не устанавливаю для него идентичное значение.
@dsp_user Обновлено - действительно было бы полезно, если бы вы предоставили минимальный воспроизводимый пример в вопросе с примерами данных и ожидаемым результатом, а не ожидали, что люди перепроектируют вашу логику из кода.
ты прав, но у меня сейчас просто нет на это времени. Надеюсь, позже в тот же день я добавлю несколько примеров.
Я думаю, что без нумерации страниц ваше обновление можно было бы сделать так (минимально протестированное, и, похоже, оно ведет себя так же, как ваше исходное обновление):
MERGE INTO (SELECT *
FROM gru_exc
WHERE smjer IN ('ON', 'OFF')) tgt
USING gru_dok src
ON (tgt.opis_grupe = src.opis_grupe)
WHEN MATCHED THEN
UPDATE SET tgt.id_grupe = src.id_grupe
WHERE (tgt.smjer = 'ON' AND src.id_vlasnika IN (SELECT id_vlasnika FROM vlas WHERE (vlas.oib_vlasnika = tgt.oib_dob))
OR (tgt.smjer = 'OFF' AND src.id_vlasnika NOT IN (SELECT id_vlasnika FROM vlas WHERE vlas.oib_vlasnika = tgt.oib_dob))
AND (tgt.id_grupe != src.id_grupe OR tgt.id_grupe IS NULL);
Обновление таблицы частями, по моему мнению, требует веских причин (например, это разовое обновление и табличное пространство отката имеет неправильный размер), в противном случае я бы отказался от этого требования. Особенно, если это будет выполняться часто.
В комментариях ОП заявил, что «GRU_EXC.id_grupe имеет значение null перед обновлением», поэтому ваша последняя строка должна быть AND (tgt.id_grupe != src.id_grupe OR tgt.id_grupe IS NULL);
@MT0, спасибо - я добавил этот предикат в конце и совершенно забыл учитывать NULL! Ошибка новичка, ох!
@Boneist, да, похоже, это так (я тоже провел лишь минимальное тестирование), что касается нумерации страниц, я думаю, я просто создам временную таблицу, содержащую только подмножество данных GRU_EXC, и выполню обновление этой таблицы . Затем я могу обновить GRU_EXC из этой временной таблицы.
На первый взгляд, предложение
ORDER BY, вложенное в подзапрос, следует удалить, и возникает вопрос: действительно ли этоUNIONнеобходимо? Возможно, вы подумаете заменить его наUNION ALL