Merge pl sql: Oracle PL/SQL оператор MERGE — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

sql — Использование подзапроса в выражении MERGE

Хочу смержить таблицу с другой таблицей. Но один из столбцов мапится в таблице ключей и нужно использовать уже новые значения. Не получается использовать подзапрос.

Код сейчас такой:

MERGE INTO TABLE_2 ST
  USING (SELECT * 
           FROM TABLE_1 FT 
             LEFT JOIN KEY_TABLE ksubs 
                ON ksubs.PK_ID = ft.PK_ID) joined
  ON
       (ksubs.NEW_PK_ID = ST.PK_ID
    AND FT.COL_2 = <константа>)
    WHEN MATCHED THEN
      UPDATE SET ST.COL_3 = FT.COL_3

Ошибка в:

FT.COL_2 = <константа>

FT.COL_2 — недопустимый идентификатор (ORA-00904: invalid identifier).

Можно ли вообще в качестве источника для MERGE использовать подзапрос?

  • sql
  • oracle
  • sql-merge






5

Да, можно. Как источник в выражении MERGE можно указать таблицу, представление или результат подзапроса.

USING Clause
Use the USING clause to specify the source of the data to be updated or inserted. The source can be a table, view, or the result of a subquery.

Касательно ошибки в вопросе, в условии ON необходимо указывать алиас всего подзапроса, алиасы определённые внутри подзапроса здесь не видны.

create table source as 
    select rownum id, 'src '||rownum name from xmlTable ('1 to 3')
;
create table target as select * from source where 1=0; 
create view sview as select * from source;

Все три запроса действительны:

merge into target t using source s on (t.id = s.id)
when not matched then insert values (s.id, s.name)
;
merge into target t using sview s on (t.id = s.id)
when not matched then insert values (s.id, s.name)
;
merge into target t using (select * from source) s on (t.id = s.id)
when not matched then insert values (s.id, s.name)
;

и дадут один и тот же результат:

3 rows merged.







Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации



Почта

Необходима, но никому не показывается




By clicking “Отправить ответ”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.


sql — В каком порядке выполненяются выражения в SET запроса MERGE?

В каком порядке происходит выполнение запроса на слияние данных MERGE?

Например:

MERGE INTO SCEHMA. TABLE updtab
USING (SELECT * FROM SOURCE_VIEW) fromtab
ON (updtab.key = fromtab.key)
WHEN MATCHED THEN UPDATE SET 
    updtab.field_1 = case when (updtab.field_1 is null) then fromtab.val_1 end,
    updtab.field_2 = case when (updtab.field_1 is null) then fromtab.val_2 end;

Будут ли оба условия для field_1 is null в SET выполнены сразу, или каждое из них будет выполняться последовательно и второй для field_2 всегда будет неудачным, поскольку field_1 больше не будет NULL после первой строки SET?

Полагаю, что по стандарту SQL выборка всегда выполняется первой. Но это же происходит на этапе обновления, и как в этом случае? Документация не совсем ясна и заставляет задуматься.

Свободный перевод вопроса Order of Evaluation — Merge Queries от участника @Greg

  • sql
  • oracle
  • transaction






5

Все происходит в рамках одного запроса MERGE. Oracle БД обеспечивает согласованность чтения на уровне запроса, что гарантирует, что данные, возвращаемые одним запросом, зафиксированы и согласованы для одного момента времени. Statement-Level Read Consistency:

Oracle Database always enforces statement-level read consistency, which guarantees that data returned by a single query is committed and consistent for a single point in time.

В данном случае момент времени — это время, когда запрос был «открыт» — время начала выполнения.

Это значит, что field_1 воспринимается всеми частями запроса MERGE с одним и тем же значением до тех пор, пока запрос не завершится. Каждое изменение field_1 в операторе SET запроса не отражается на его текущем значении в других частях запроса.

Within a single transaction, you’re correct. In the default read committed isolation level a non-repeatable read is possible. If a SELECT statement is re-run and another transaction has committed data then the data read by your first transaction might be different. The MERGE is a statement, and not a transaction.

Если бы в пределах одной транзакции, то утверждение из вопроса было бы верно. На стандартном уровне изоляции с фиксацией чтения возможно «неповторяющееся чтение». Если запросSELECT выполняется повторно и другая транзакция зафиксировала данные с момента начала первого выполнения, то данные считанные первым и вторым запросом могут отличаться. Но MERGE является запросом, а не транзакцией.

Прим.ред.: пошаговый и воспроизводимый пример, наглядно помогающий понять выше изложенное, уже имеется в ответе на похожий вопрос.

Свободный перевод ответа от участника @Ben







Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации



Почта

Необходима, но никому не показывается




By clicking “Отправить ответ”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.


База данных Oracle — операция MERGE JOIN

О

MERGE JOIN объединяет таблицы путем слияния отсортированных списков записей из каждой таблицы в столбце соединения . Он эффективен для больших пакетных операций, но может быть неэффективен для соединений, используемых приложениями обработки транзакций.

MERGE JOIN используется всякий раз, когда Oracle не может использовать индекс при выполнении соединения. В следующем примере все таблицы полностью проиндексированы. Таким образом, в этом примере индексы намеренно отключены путем добавления 0 к числовым клавишам во время соединения, чтобы вызвать соединение слиянием.

MERGE JOIN — это заданная операция.

План выполнения 

 СЛИЯТЬ СОЕДИНЕНИЕ
СОРТИРОВАТЬ ПРИСОЕДИНИТЬСЯ
СТОЛ ДОСТУП ПОЛНАЯ ПРОДАЖА
СОРТИРОВАТЬ ПРИСОЕДИНИТЬСЯ
СТОЛ ДОСТУП ПОЛНАЯ КОМПАНИЯ

Интерпретация плана выполнения

Есть два потенциальных индекса, которые могут использоваться запросом, соединяющим таблицу COMPANY с таблицей SALES. Во-первых, есть индекс COMPANY.COMPANY_ID, но этот индекс нельзя использовать из-за добавленного к нему значения +0.

Как показано в плане, Oracle выполнит полное сканирование таблицы (TABLE ACCESS FULL) для каждой таблицы, отсортирует результаты (используя операцию SORT JOIN) и объединит наборы результатов. Использование объединений слиянием указывает на то, что индексы либо недоступны, либо отключены синтаксисом запроса.

шага в MERGE JOINS

Операция MERGE JOIN объединяет результаты двух просмотров данных. Обычно операция состоит из следующих шагов:

  • ДОСТУП К ТАБЛИЦАМ ПОЛНЫЙ для каждой таблицы в соединении

  • SORT JOIN для сортировки результатов операций доступа к данным (например, TABLE ACCESS FULL)

  • MERGE JOIN для объединения результатов операции SORT JOIN.

Использование объединений слиянием указывает на то, что индексы либо недоступны, либо отключены синтаксисом запроса.

Как заданная операция, которая выполняет полное сканирование своих таблиц, MERGE JOIN обычно не подходит для использования в многопользовательских онлайн-приложениях по следующим причинам:

  • Возврат первой строки из запроса может быть медленным. Поскольку MERGE JOIN является операцией множества, а не строкой, она не возвращает строки пользователю до тех пор, пока все строки не будут обработаны.

  • Его результирующий набор не будет оставаться в SGA очень долго. Поскольку блоки, используемые для чтения таблицы, были прочитаны с помощью полного сканирования таблицы, они будут первыми блоками, удаленными из SGA, когда потребуется больше места в буферном кэше блоков данных SGA.

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

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

Последствия настройки для MERGE JOINS

MERGE JOIN будет эффективным везде, где будет эффективным полное сканирование таблицы. То есть в ситуациях, когда полное сканирование таблицы предпочтительнее сканирования диапазона индексов/доступа к таблице по комбинации ROWID, операция MERGE JOIN предпочтительнее операции соединения NESTED LOOPS.

Полное сканирование таблицы предпочтительнее при следующих условиях:

  • Когда задействованная таблица очень мала. Если таблица очень маленькая, может быть быстрее выполнить полное сканирование таблицы, чем сканирование индекса с последующим частичным сканированием таблицы. Например, если таблица полностью хранится в количестве сканируемых блоков при одном чтении базы данных, то вся таблица может быть прочитана за одно чтение физической базы данных. Если всю таблицу можно прочитать во время одного физического чтения из базы данных, то полное сканирование таблицы будет более эффективным, чем сканирование диапазона индексов и доступ к таблице по комбинации ROWID (для чего потребуется несколько физических чтений).

  • Когда задействованная таблица очень велика. Если таблица очень большая, может оказаться более эффективным выполнить полное сканирование таблицы, чем сканирование индекса с последующим частичным сканированием по трем причинам. Во-первых, в зависимости от степени, в которой данные физически хранятся в упорядоченном формате, и количества выбранных записей, вы можете прочитать меньше блоков, чтобы выполнить полное сканирование таблицы, чем при сопоставимом сканировании индекса и доступе к таблице с помощью ROWID. Во-вторых, блоки, прочитанные в буферном кеше блоков данных SGA при полном сканировании таблицы, недолго хранятся в SGA, поэтому они не будут мешать обмену данными между пользователями; блоки, прочитанные при сканировании индекса, долго удерживаются в SGA, поэтому сканирование большого индекса может помешать обмену данными между пользователями.

  • Наконец, при полном сканировании таблиц можно воспользоваться опцией Oracle Parallel Query Option.

Во время операции SORT JOIN Oracle сортирует как можно больше данных в области памяти, называемой областью сортировки. Максимальный размер области сортировки определяется параметром SORT AREA SIZE в файле экземпляра init.ora; он выделяется с шагом 8 КБ. Если область сортировки недостаточно велика для хранения всех отсортированных данных, Oracle выделит временный сегмент на время выполнения запроса. Стоимость выделения и освобождения временного сегмента примерно эквивалентна стоимости примерно 10 операторов INSERT/UPDATE/DELETE. Выделение и освобождение временных сегментов требует выделения блокировок. Когда несколько пользователей одновременно пытаются отсортировать большие наборы результатов, они могут столкнуться с конкуренцией в процессе выделения временной блокировки сегмента.

Данные из временного сегмента загружаются в область сортировки для сортировки. Каждый набор шагов «загрузка и сортировка» называется прогоном. Если результирующий набор велик по сравнению с размером области сортировки, данные могут быть загружены и выгружены несколько раз на диск и с диска, что приведет к избыточному вводу-выводу.

В Oracle запись во временные сегменты обходит SGA с помощью метода, называемого записью с прямой сортировкой. Запись с прямой сортировкой повышает производительность записи во временные сегменты как минимум на 40 процентов по сравнению с более ранними версиями ядра.

Третий шаг MERGE JOIN, часть сравнения и слияния, очень эффективен. Затраты на производительность, связанные с MERGE JOIN, почти полностью связаны с первыми двумя шагами: полным сканированием таблицы и операциями сортировки. Поэтому настройка операций MERGE JOIN должна быть сосредоточена на повышении производительности первых двух шагов.

Производительность полного сканирования таблиц можно повысить за счет:

  • Настройка ввода-вывода и улучшенное использование возможностей многоблочного чтения Oracle,

  • или с помощью параметра параллельного запроса.

Вы можете улучшить производительность сортировки, установив высокое значение для параметра SORT AREA SIZE или выделив табличное пространство для операций временного сегмента.

Документация/Справочник

  • Соединения MJ

производительность — операция слияния оракула зависает (или зацикливается); тюнер запросов утверждает, что это декартова операция произведения

Задавать вопрос

спросил

Изменено
10 лет, 8 месяцев назад

Просмотрено
3к раз

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

Вот план объяснения:

 -------------------------------------------------------- -------------------------------------------------- --------
| Идентификатор | Операция | Имя | Ряды | Байты | Стоимость (% ЦП)| Время |
-------------------------------------------------- --------------------------------------------------
| 0 | ЗАЯВЛЕНИЕ О СЛИЯНИИ | | 1 | 18 | 75 (10)| 00:00:01 |
| 1 | ОБЪЕДИНИТЬ | CM_SSS_DETAIL | | | | |
| 2 | ПОСМОТРЕТЬ | | | | | |
| 3 | СОРТИРОВАТЬ ПО | | 1 | 1352 | 75 (10)| 00:00:01 |
| 4 | ВЛОЖЕННЫЕ ЦИКЛЫ | | | | | |
| 5 | ВЛОЖЕННЫЕ ЦИКЛЫ | | 1 | 1352 | 74 (9)| 00:00:01 |
| 6 | ВЛОЖЕННЫЕ ЦИКЛЫ | | 1 | 1328 | 73 (9)| 00:00:01 |
| 7 | ОБЪЕДИНЯЙТЕ ДЕКОРТОВУЮ СЕНСОРНУЮ | | 1 | 1306 | 72 (9)| 00:00:01 |
| 8 | ДОСТУП К СТОЛУ ПОЛНЫЙ | CM_SSS_DETAIL | 1 | 1274 | 18 (0)| 00:00:01 |
| 9 | БУФЕРНАЯ СОРТИРОВКА | | 12815 | 400К| 54 (12)| 00:00:01 |
|* 10 | ДОСТУП К СТОЛУ ПОЛНЫЙ | CI_CC | 12815 | 400К| 54 (12)| 00:00:01 |
| 11 | ДОСТУП К ТАБЛИЦАМ ПО ИНДЕКСУ ROWID| CI_CASE | 4 | 88 | 1 (0)| 00:00:01 |
|* 12 | ИНДЕКС ДИАПАЗОН СКАН | XT220S1 | 6 | | 1 (0)| 00:00:01 |
|* 13 | ИНДЕКС ДИАПАЗОН СКАН | XT222P0 | 1 | | 1 (0)| 00:00:01 |
|* 14 | ДОСТУП К ТАБЛИЦАМ ПО INDEX ROWID | CI_CASE_CHAR | 1 | 24 | 1 (0)| 00:00:01 |
-------------------------------------------------- --------------------------------------------------
Информация о предикате (определяется идентификатором операции):
-------------------------------------------------- -
10 - filter("CC". "CC_TYPE_CD"='1NAA-LTR' ИЛИ ​​"CC"."CC_TYPE_CD"='1NEA-LTR' ИЛИ
"CC"."CC_TYPE_CD"='1NIP-LTR' ИЛИ ​​"CC"."CC_TYPE_CD"='CBS-LTR' ИЛИ
"CC"."CC_TYPE_CD"='CBS1-LTR' ИЛИ ​​"CC"."CC_TYPE_CD"='CBS2-LTR' ИЛИ
"CC"."CC_TYPE_CD"='DNAR-LTR' ИЛИ ​​"CC"."CC_TYPE_CD"='INR-LTR' ИЛИ ​​"CC"."CC_TYPE_CD"='IPL-LTR'
ИЛИ "CC"."CC_TYPE_CD"='SOFB-LTR')
12 - доступ("CC"."PER_ID"="C"."PER_ID")
13 - доступ("CHR"."CASE_ID"="C"."CASE_ID" AND "CHR"."CHAR_TYPE_CD"='OBLGID')
14 - фильтр("D"."SA_ID"="CHR"."CHAR_VAL_FK1")
-------------------------------------------------- -----------------------------

Вот мой запрос:

 MERGE INTO cm_sss_detail d
С ИСПОЛЬЗОВАНИЕМ (
    ВЫБЕРИТЕ chr.char_val_fk1, MIN (cc.cc_dttm) first_date
    ИЗ ci_case c
    ПРИСОЕДИНЯЙТЕСЬ
        ВКЛ (cc.per_id = c.per_id)
    ПРИСОЕДИНЯЙТЕСЬ к ci_case_char chr
        ВКЛ (chr.case_id = c.case_id)
    ГДЕ chr.char_type_cd = 'ОБЛГИД'
        И cc.cc_type_cd IN ( '1NAA-LTR','CBS1-LTR','CBS2-LTR','CBS-LTR',
                                «DNAR-LTR», «IPL-LTR», «INR-LTR», «SOFB-LTR»,
                                «1NEA-LTR», «1NIP-LTR»)
    СГРУППИРОВАТЬ ПО chr. char_val_fk1
) б
ВКЛ (d.sa_id = b.char_val_fk1)
КОГДА СООТВЕТСТВУЕТ, ТО ОБНОВЛЯЕТСЯ
    УСТАНОВИТЕ d.fst_bill_date = b.first_date;

Таблица cm_sss_detail имеет индекс столбца sa_id (это также PK).

  • oracle
  • performance
  • merge
  • query-performance

В соответствии с планом запроса оптимизатор ожидает, что оператор MERGE изменит 1 строку. Кажется, ожидается, что декартово соединение также вернет одну строку. На основании того факта, что запрос не возвращается в течение нескольких миллисекунд, это обычно означает, что статистика по одному или нескольким объектам (таблицам или индексам) существенно неверна. Когда в последний раз собиралась статистика по этим таблицам и зависимым от них индексам? Существенно ли изменились фактические данные в таблицах с того времени?

4

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.