🎵 «Я люблю тебя до слёз
Каждый вздох, как в первый раз
Вместо лжи красивых фраз
Это облако из роз» — Александр Серов

🎯 Задача из собеседовании в ЦБ РФ в 2020г.
Это забытое время без ИИ, тогда задачи могли высылать по почте с ограниченным временем на решение.
В самых первых сообщениях на канале я добавил именно самые интересные из них.
Поскольку я еще не знал/не умел оформлять посты, то эти задачи были недостаточно раскрыты.
Считаю, что это крайне несправедливо к задачам.
Хочу их раскрыть подробнее.
Дело в том, что для их решения "стандартного" обучения недостаточно (слишком они специфичны).
Технологию их решения требуется знать - выдумать на ходу не получится.

И так, задача 1:
Есть таблицы по реквизитам.

-- Основные реквизиты организации
CREATE TABLE T1 (
  ID    NUMBER,           -- Уникальный идентификатор
  OGRN  VARCHAR2(20),    -- ОГРН
  INN   VARCHAR2(20),    -- ИНН
  NAME  VARCHAR2(200),   -- Наименование организации
  DS    DATE,            -- Дата начала действия записи
  DE    DATE             -- Дата окончания действия записи
);

-- Расширенные реквизиты организации
CREATE TABLE T2 (
  ID       NUMBER,          -- Уникальный идентификатор
  STATUS   VARCHAR2(10),   -- Статус организации
  ADDRESS  VARCHAR2(200),  -- Адрес
  EQ       NUMBER,          -- Уставной капитал
  DS       DATE,            -- Дата начала действия записи
  DE       DATE             -- Дата окончания действия записи
);

История в таблицах T1 и T2 может начинаться с разных дат и является непрерывной
(без пропусков,
дата окончания записи = дата начала следующей записи минус один день или 31.12.9999 у последней записи в истории) .

📄 Пример данных (ID = 125)

T1 — основные реквизиты
ID   OGRN           INN         NAME                  DS         DE
125  1127847448520  7810880684  ООО "ЛЕН-РЕЗЕРВ"      01.01.17   30.06.18
125  1127847448520  7810880684  ООО "ЛЕНТЕХ-РЕЗЕРВ"   01.07.18   31.12.19
125  1127847448520  7810880684  ООО "ЛЕН-РЕЗЕРВ"      01.01.20   31.12.9999

T2 — расширенные реквизиты
ID   STATUS  ADDRESS                               EQ      DS         DE
125  001     СПб, Шуваловский пр. 22              10000   05.01.17   10.09.18
125  101     СПб, пр. Просвещения 130             10000   11.09.18   20.04.19
125  001     СПб, Московский пр. 222              10000   21.04.19   31.12.9999

🧠 Задача:
Написать запрос, который формирует сводную историю всех реквизитов из обоих таблиц для организации с id=125.

ID   OGRN           INN         NAME                  STATUS  ADDRESS                              EQ      DS         DE
125  1127847448520  7810880684  ООО "ЛЕН-РЕЗЕРВ"               -                                   -      01.01.17   04.01.17
125  1127847448520  7810880684  ООО "ЛЕН-РЕЗЕРВ"      001     СПб, Шуваловский пр. 22             10000   05.01.17   30.06.18
125  1127847448520  7810880684  ООО "ЛЕНТЕХ-РЕЗЕРВ"   001     СПб, Шуваловский пр. 22             10000   01.07.18   10.09.18
125  1127847448520  7810880684  ООО "ЛЕНТЕХ-РЕЗЕРВ"   101     СПб, пр. Просвещения 130            10000   11.09.18   20.04.19
125  1127847448520  7810880684  ООО "ЛЕНТЕХ-РЕЗЕРВ"   001     СПб, Московский пр. 222             10000   21.04.19   31.12.19
125  1127847448520  7810880684  ООО "ЛЕН-РЕЗЕРВ"      001     СПб, Московский пр. 222             10000   01.01.20   31.12.9999

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

💬 Есть что писать - пишите! 👇.
#️⃣ #RealInterviewTasks #SQL #Oracle #PLSQL #PostgreSQL

🎵 «Кто весел, тот смеётся,
Кто хочет, тот добьётся,
Кто ищет, тот всегда найдёт!» — из к/ф "Дети капитана Гранта"

Сегодня день опросов и принятия "решений".
Так само по себе сложилось, что образовались рубрики:
1️⃣ SQL Оптимизация
2️⃣ Решение задач процедурными методами
3️⃣ Полезные ресурсы
4️⃣ Задачи на собесах
5️⃣ Случаи из практики
6️⃣ Архитектура кода

Я ими дорожу. 💛
По-моему, весьма солидный набор.

У меня есть мысли создать рубрики:
"Критический разбор чужих авторов"
и/или, например, "Читаем книгу вместе" 📚,
легкие задачи на 5 минут.

Но, возможно, то, что интересно мне, не интересно Вам. 🤔

Подскажите, что Вам нравится/не нравится больше всего.
Какие темы/рубрики Вы бы хотели видеть на канале? 💬

🎵 «Мы бандито, знаменито, мы стрелято пистолето, о йес,
Мы фиато разъезжанто целый день в кабриолето, о йес,
Постоянно пьем чинзано, постоянно сыто-пьяно, о йес,
Держим банко миллионо и плеванто на законо, о йес.» - м/ф «Приключения капитана Врунгеля»

📊 Разбор задачи с собеседования ЦБ РФ

🧩 Идея задачи
В таблицах T1 и T2 хранится история изменений по компаниям — периоды действия реквизитов и атрибутов.
Проблема в том, что эти временные промежутки не совпадают, и чтобы свести данные по каждой организации, нужно сформировать единую временную сетку — матрицу всех возможных периодов.

📌 Главная мысль:
Мы создаём «нитку» времени, на которую потом будем нанизывать «бусинки» реквизитов из обеих таблиц.

🧠 Шаг 1. Формируем временные периоды

SELECT   t.id,
         t.reg_date AS start_period,
         LEAD(t.reg_date, 1, TO_DATE('31.12.9999', 'DD.MM.YYYY'))
             OVER (PARTITION BY t.id ORDER BY t.reg_date) - INTERVAL '1' DAY AS end_period
FROM     (SELECT t1.id, t1.ds AS reg_date FROM t1
          UNION
          SELECT t2.id, t2.ds AS reg_date FROM t2) t
ORDER BY t.id, t.reg_date;

🪄 Здесь мы:
Объединили даты начала (ds) из обеих таблиц;
Для каждой строки нашли следующую дату (через LEAD) и вычли 1 день;
Нам вторая дата de, по сути, и не нужна, мы знаем, что end_period это следующая - 1 день, поэтому мы её и вычисляем.
Таким образом, получили интервалы [start_period, end_period].

🧩 Шаг 2. Совмещаем данные
Теперь, когда у нас есть нитка периодов, можно аккуратно «нанизать» реквизиты из обеих таблиц по левой связи:

WITH periods AS (
  SELECT t.id,
         t.reg_date AS start_period,
         LEAD(t.reg_date, 1, TO_DATE('31.12.9999','DD.MM.YYYY'))
             OVER (PARTITION BY t.id ORDER BY t.reg_date) - INTERVAL '1' DAY AS end_period
  FROM   (SELECT t1.id, t1.ds AS reg_date FROM t1
          UNION
          SELECT t2.id, t2.ds AS reg_date FROM t2) t
)
SELECT p.id,
       t1.ogrn, t1.inn, t1.name,
       t2.status, t2.address, t2.eq,
       p.start_period AS ds,
       p.end_period   AS de
FROM   periods p
  LEFT JOIN t1 ON (p.id = t1.id AND p.start_period BETWEEN t1.ds AND t1.de AND p.end_period BETWEEN t1.ds AND t1.de)
  LEFT JOIN t2 ON (p.id = t2.id AND p.start_period BETWEEN t2.ds AND t2.de AND p.end_period BETWEEN t2.ds AND t2.de)
ORDER BY p.id, p.start_period;

⚙️ Результат:
Получаем непрерывную временную линию для каждой организации, где в каждом периоде указаны действующие:
реквизиты (из T1),
атрибуты (из T2).

💡 Вывод:
Такой приём — построение предварительной матрицы — часто встречается в задачах.
Это может быть и временные периоды, и матрицей всех возможных значений, матрица может выглядеть как набор множества полей.
Смысл один нужна "нитка", на которую нанизывают "бусинки".

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

👍 Палец вверх — хорошая задача.
👎 Палец вниз — трата времени в пустую.

💬 Хотите сказать - скажите! 👇.
#️⃣ #RealInterviewTasks #sql #Oracle #PostgreSQL #PL/SQL #PL/pgSQL

🎵 «Пускай капризен успех
Он выбирает из тех,
Кто может первым посмеяться над собой.
Пой засыпая, пой во сне, проснись и пой.» — Владимир Луговой

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

🐘 Если вы работаете со слоном PostgreSQL, то, возможно, уже знаете,
а если нет — срочно исправляйтесь:
есть мощный инструмент pgCodeKeeper для Eclipse.

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

🔗 pgcodekeeper.org
🔗 eclipse.org
🔗 Группа поддержки проекта

Его разработали проггеры из Такси Максим и это крутой инструмент.
Я сам его использовал и могу подтвердить, что такой инструмент повышает скорость разработки,
но и, вообще, меняет модель разработки - всё становится проще.

Идея в том, что плагин умеет сравнивать код базы и код из источника (GIT).
И все изменения из GIT может влить в выбранную базу, а все изменения из базе может записать в папку.
Останется только зафиксировать изменения в GIT.
А также может подтвердить, что источники эквивалентны.

Если Вы работаете с PostgreSQL, то использовать или нет это Ваше дело, но изучить этот инструмент Вы обязаны.

💎 Поддержка канала⁉️.

👍 Палец вверх — для Вас полезная информация
👎 Палец вниз — "ерунда на постном масле"

💬 Если не закончились клавиши на клавиатуре! 👇.
#️⃣ #Tools #PostgreSQL

🎵 «Отвесные стены — а ну, не зевай!
Ты здесь на везение не уповай
В горах ненадёжны ни камень, ни лёд, ни скала
Надеемся только на крепость рук
На руки друга и вбитый крюк
И молимся, чтобы страховка не подвела» — Владимир Высоцкий

📌 Разбор задачи: Максимальная разница между соседями

Я продолжаю тему решение задач процедурными методами.
Иногда жизнь — как дорога среди равнин и гор.
Мы шагаем вперёд, иногда ровно, а иногда — то вверх, то вниз.
Вот и в массиве бывают такие «перепады»: соседние значения могут отличаться сильно или почти не заметно.
Наша цель — найти самый большой скачок между элементами.

🧠 Что делает скрипт?

Берёт набор чисел:
0, 1, 2, 3, 7, 5, 6

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

DECLARE
    TYPE set_tab IS TABLE OF NUMBER;
    "set" set_tab := set_tab (0,1,2,3,7,5,6);
    residualMaxAbs NUMBER := 0;
BEGIN
    FOR i IN 2.."set".LAST LOOP
        IF ABS("set"(i) - "set"(i-1)) > residualMaxAbs THEN
          residualMaxAbs := ABS("set"(i) - "set"(i-1));
        END IF;
    END LOOP;

    DBMS_OUTPUT.put_line('residualMaxAbs = ' || TO_CHAR(residualMaxAbs));
END;

💡 Как работает:
Код движется по массиву,
замеряя, где был самый крутой подъём или спад.
Для массива (0,1,2,3,7,5,6)
максимальный скачок — 4 (между 3 и 7).

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

👍 Палец вверх — интересно
👎 Палец вниз — так себе

💬 Если лимит на буквы не закончился! 👇.
#️⃣ #sql #Oracle #PostgreSQL #PL/SQL #PL/pgSQL

🎵 «И если мне неймётся и не спится
Или с похмелья нет на мне лица
Открою Кодекс на любой странице
И не могу — читаю до конца» — Владимир Высоцкий

🎯 Вторая задача с собеса ЦБ РФ (2020 год).
Это было время, когда SQL-интервью ещё высылали по почте.
На тот момент эта задача мне показалась трудной, но очень интересной.
Потратил на неё много времени - 3-4 часа!
Я опубликовал задачи от ЦБ самыми первыми на канале, но хочу их переопубликовать,
чтобы уделить задачам больше внимание. Эти задачи этого заслуживают.

📂 В прошлой задаче мы формировали полную сводную историю реквизитов организации.
А теперь — вторая часть: нужно объединить бизнес-реквизиты и сократить историю.

Используем те же таблицы T1 и T2:
T1 — основные реквизиты (OGRN, INN, NAME)
T2 — расширенные реквизиты (EQ и даты действия)
История по каждой таблице непрерывная и может начинаться с разных дат.

📄 Написать запрос, который формирует сводную историю бизнес реквизитов OGRN, INN, NAME и EQ

Номер ID     OGRN           INN         NAME                 EQ      DS          DE
1      125   1127847448520  7810880684  ООО "ЛЕН-РЕЗЕРВ"             01.01.2017  04.01.2017
2      125   1127847448520  7810880684  ООО "ЛЕН-РЕЗЕРВ"     10 000  05.01.2017  30.06.2018
🔴 3   125   1127847448520  7810880684  ООО "ЛЕНТЕХ-РЕЗЕРВ"  10 000  01.07.2018  10.09.2018
🔴 4   125   1127847448520  7810880684  ООО "ЛЕНТЕХ-РЕЗЕРВ"  10 000  11.09.2018  20.04.2019
🔴 5   125   1127847448520  7810880684  ООО "ЛЕНТЕХ-РЕЗЕРВ"  10 000  21.04.2019  31.12.2019
6      125   1127847448520  7810880684  ООО "ЛЕН-РЕЗЕРВ"     10 000  01.01.2020  31.12.9999

При этом две и более, рядом стоящие в истории строки, с одинаковыми бизнес реквизитами
должны быть заменены одной строкой с объеденным интервалом действия этих записей
(строки выделены красным цветом, строки с номерам 3-5).
Строки с номерами 2 и 6 имеют одинаковые бизнес реквизиты, но они не являются рядом стоящие,
так как между этими записями во временном интервале присутствует другой набор бизнес реквизитов (строки 3-5).
Итоговая таблица должна быть следующего вида:

ID     OGRN           INN         NAME                 EQ      DS          DE
125    1127847448520  7810880684  ООО "ЛЕН-РЕЗЕРВ"             01.01.2017  04.01.2017
125    1127847448520  7810880684  ООО "ЛЕН-РЕЗЕРВ"     10 000  05.01.2017  30.06.2018
125    1127847448520  7810880684  ООО "ЛЕНТЕХ-РЕЗЕРВ"  10 000  01.07.2018  31.12.2019
125    1127847448520  7810880684  ООО "ЛЕН-РЕЗЕРВ"     10 000  01.01.2020  31.12.9999

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

💬 Как бы вы подошли к решению? Пишите в комментариях свои идеи! 👇
#️⃣ #RealInterviewTasks #SQL #Oracle #PLSQL #PostgreSQL

🎵 «Возьму с собою я в прогулку кавалера
Он песняки мои все знает наизусть
Не иностранец и не сын миллионера
Бухгалтер он простой, да ну и пусть» — Алена Апина

📊 Разбор второй задачи с собеса ЦБ РФ (2020 г.)

🧩 Идея задачи
В таблицах T1 и T2 хранится история по компаниям — периоды действия основных и дополнительных реквизитов.
После объединения этих таблиц мы получаем длинную «историю жизни» компании, но с дублированием соседних строк, где бизнес-реквизиты не менялись.

WITH
  periods AS
    (SELECT   t.id,
              t.reg_date AS start_period,
              COALESCE (LEAD (t.reg_date) OVER (PARTITION BY t.id ORDER BY t.reg_date) - INTERVAL '1' DAY,
                        TO_DATE ('31.12.9999', 'DD.MM.YYYY')) AS end_period
     FROM     (SELECT t1.id, t1.ds AS reg_date FROM t1
               UNION
               SELECT t2.id, t2.ds AS reg_date FROM t2) t
     ORDER BY t.id, t.reg_date),
  summary_data AS
    (SELECT   p.id,
              t1.ogrn,
              t1.inn,
              t1.name,
              t2.eq,
              p.start_period AS ds,
              p.end_period AS de,
              STANDARD_HASH (t1.ogrn || t1.inn || t1.name || '/' || t2.eq) AS hash_group
     FROM     periods p
              LEFT JOIN t1 ON (p.id = t1.id AND p.start_period BETWEEN t1.ds AND t1.de AND p.end_period BETWEEN t1.ds AND t1.de)
              LEFT JOIN t2 ON (p.id = t2.id AND p.start_period BETWEEN t2.ds AND t2.de AND p.end_period BETWEEN t2.ds AND t2.de)
     )
SELECT
  MAX(sd2.id) AS id,
  MAX(sd2.ogrn) AS ogrn,
  MAX(sd2.inn) AS inn,
  MAX(sd2.name) AS name,
  MAX(sd2.eq) AS eq,
  MIN (sd2.ds) AS ds,
  MAX (sd2.de) AS de 
FROM
  (SELECT
    sd1.*,
    SUM(hash_group_sign) OVER (ORDER BY sd1.ds) AS group_id
  FROM
  (SELECT 
    sd.*,
    CASE 
      WHEN ROW_NUMBER () OVER (PARTITION BY sd.hash_group ORDER BY sd.ds) = 1 THEN 1
      ELSE 0
    END AS hash_group_sign
  FROM   summary_data sd) sd1) sd2
GROUP BY sd2.hash_group, sd2.group_id
ORDER BY id, ds

Шаг 1. Формируем непрерывную временную матрицу. (periods )
Шаг 2. Нанизываем реквизиты на временную «нить» (summary_data )
Теперь к каждому периоду подцепляем актуальные значения из T1 и T2.
Первые два шага уже разбирали - повторяться не буду.
Есть интересный момент с хаш функцией: вместо, чтобы "тащить" поля t1.ogrn, t1.inn, t1.name, t2.eq, мы вычисляем ХАШ значения hash_group.
Дело в том, что от количество полей, которые используется в GROUP BY, напрямую зависит объем использования временного табличного пространства TEMP и зависимость драматическая. Необходимо использовать, либо хаш функции, либо указываете только те поля, которые определяют группировку и не более.
Остальные же поля можно вывести в виде результата агрегатных функций. Например как MAX(sd2.id) AS id.

Шаг 3. Сжимаем соседние периоды с одинаковыми данными
Чтобы объединить подряд идущие периоды, используем оконные функции.

Тут есть интересный SQL трюк, который выдумать на ходу крайне сложно.
Для нахождения нужной нам группы мы используем ROW_NUMBER() и ставим 1, если начинается новая группа и 0, если мы находимся в старой группе.
И так, мы определим поле hash_group_sign.
Далее, мы используем оконную функцию, которая проходит по полю hash_group_sign и вычисляет накопительную сумму
и таким образом все записи одной группы будут иметь свой вычисляемый синтетический идентификатор group_id.

Шаг 4. Агрегируем по группе и берём минимальный ds и максимальный de.
Поле hash_group задает уникальные реквизиты, поле group_id задает уникальность внутри соседних групп.

💡 Вывод:
Такой приём — «временная компрессия» — часто встречается в аналитических задачах.
Сначала строим «нить времени», потом «нанизываем» реквизиты, и наконец — сжимаем лишние интервалы, где данные не менялись.

⚠️ Хотите проверить скрипты, но нет базы под рукой — используйте онлайн-песочницу.
💎 Поддержка канала⁉️

👍 Палец вверх — задача стоящая.
👎 Палец вниз — слишком много аналитики.

💬 Как бы вы подошли к этой задаче? Пишите в комментариях 👇
#️⃣ #RealInterviewTasks #SQL #Oracle #PostgreSQL

🎵 «Я гляжу ей вслед -
Ничего в ней нет,
А я все гляжу -
Глаз не отвожу.» — Лев Ошанин

⚙️ Рубрика — «Случай из практики»: антиспам и агрегация сообщений

Есть таблица сообщений, из которой внешний сервис читает новые записи и:
пересылает события подписанным системам,
а также дублирует информацию во внутренний чат мессенджера для менеджеров.

Если что-то пошло не так, сообщение попадает в чат.
И вот тут начинается самое интересное…
Допустим, один из видов сообщений —
«Шеф! Всё пропало! Клиент уезжает — гипс снимают!»

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

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

Смысл — группировать похожие сообщения и публиковать их реже,
если поток становится слишком интенсивным.

📊 Пример логики:
Первое сообщение отправляем сразу.
Если за период пришло 2–10 сообщений → отправляем одно сгруппированное сообщение с задержкой 10 минут.
Если за период пришло 11–50 сообщений → одно сообщение с задержкой 30 минут.
Если за период пришло более 50 сообщений → одно сообщение с задержкой 1 час.

Формат сгруппированного сообщения, например:
«За последние 10 минут поступило 27 сообщений вида
“Шеф! Всё пропало! Клиент уезжает — гипс снимают!”»

⚠️ Требования к решению:
1️⃣ Исключить конкуренцию между сессиями — без блокировок и гонок данных.
2️⃣ Поддерживать разные настройки частоты для разных типов сообщений.

⚠️ Хотите проверить скрипты, но нет базы под рукой — используйте онлайн-песочницу.
💎 Поддержка канала⁉️

👍 Палец вверх — интересна реализация.
👎 Палец вниз — не интересно.

💬 Есть мысли как это сделать - пишите 👇
#️⃣ #Cases #SQL #Oracle #PostgreSQL #PLSQL

🎵 «Сапер ошибается два раза в жизни: первый раз при выборе профессии,
второй раз - когда женится» — несмешной анекдот

📌 Рубрика решение задач процедурными методами
Сапёр: Для данной матрицы сапёра, где -1 обозначает бомбу, а 0 — пустую клетку, вычислите количество бомб, смежных с каждой пустой клеткой.

Что делает скрипт?
Берёт матрицу поля, где
-1 — это бомба,
0 — пустая клетка.

Проходит по всем клеткам.

Для каждой бомбы прибавляет +1
во все соседние ячейки — вверх, вниз, влево, вправо и по диагонали.
В итоге каждая пустая клетка «узнаёт», сколько мин рядом.

Получается классическая карта сапёра.

DECLARE
  TYPE row_tab IS TABLE OF NUMBER;
  TYPE matrix_tab IS TABLE OF row_tab;
  
  matrix  matrix_tab := matrix_tab (row_tab (-1, 0, -1, 0), row_tab (0, -1, 0, -1), row_tab (0, 0, 0, 0), row_tab (-1, -1, 0, 0));
BEGIN
  FOR i IN 1..matrix.COUNT
  LOOP
    FOR j IN 1..matrix(1).COUNT
    LOOP
      IF matrix(i)(j) = -1 THEN
        IF i+1 <= matrix.COUNT AND j+1 <= matrix(1).COUNT THEN
          IF matrix(i + 1)(j+1) != -1 THEN
            matrix(i + 1)(j+1) := matrix(i + 1)(j+1) + 1;
          END IF;
        END IF;
        
        IF i+1 <= matrix.COUNT THEN
          IF matrix(i+1)(j) != -1 THEN
            matrix(i+1)(j) := matrix(i+1)(j) + 1;
          END IF;
        END IF;

        IF j+1 <= matrix(1).COUNT THEN
          IF matrix (i)(j+1) != -1 THEN
            matrix(i)(j+1) := matrix(i)(j+1) + 1;
          END IF;
        END IF;

        IF i - 1 > 0 AND j - 1 > 0  THEN
          IF matrix (i-1)(j-1) != -1 THEN
            matrix(i-1)(j-1) := matrix(i-1)(j-1) + 1;
          END IF;
        END IF;

        IF i - 1 > 0 THEN
          IF matrix (i-1)(j) != -1 THEN
            matrix(i-1)(j) := matrix(i-1)(j) + 1;
          END IF;
        END IF;

        IF j - 1 > 0 THEN
          IF matrix (i)(j-1) != -1 THEN
            matrix(i)(j-1) := matrix(i)(j-1) + 1;
          END IF;
        END IF;
      END IF;
    END LOOP;
  END LOOP;
  
  -- print matrix
  FOR i IN 1..matrix.COUNT LOOP
    DECLARE s VARCHAR2(100);
    BEGIN
      FOR j IN 1..matrix(1).COUNT LOOP
        s := s || LPAD(matrix(i)(j), 5);
      END LOOP;
      DBMS_OUTPUT.put_line(s);
    END;
  END LOOP;
END;

💡 Как работает:
Каждая мина «освещает» вокруг себя все восемь направлений.
Код считает количество соседних мин, где каждая клетка знает, насколько опасно рядом.

🧩 Из входных данных:

-1   0  -1   0
 0  -1   0  -1
 0   0   0   0
-1  -1   0   0

получаем:

-1   3  -1   2
 2  -1   3  -1
 2   2   1   1
-1  -1   1   0

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

👍 Палец вверх — алгоритмы трогают Ваше сердце
👎 Палец вниз — это всё не для DB

💬 Пишите, и Ваше слово будет прочитано! 👇.
#️⃣  #SQL #Oracle #PLSQL

🎵 «Разлук так много на земле и разных судеб,
Надежду дарит на заре паромщик людям.
То берег левый нужен им, то берег правый…
Влюблённых много — он один у переправы.»
— Михаил Танич

🚀 Серия: Оптимизация SQL-запросов
📘 Тема: Чтение плана запроса при соединении двух и более таблиц.

Для примера возьмём две таблицы — A и B.
И самый простой запрос:

SELECT a.*, b.*
FROM a 
INNER JOIN b ON (a.id = b.id);

Поставьте себя на место оптимизатора.
Как бы вы соединили таблицы?

Наверное, взяли бы первую строку из таблицы A, посмотрели её id,
а потом по этому id нашли бы соответствующие строки в B.
То есть прошли бы циклом по A, а для каждой строки делали бы поиск в B.

Верно? 💡
Отлично! Именно этот способ называется Nested Loop Join.

📖 Подробности можно прочитать в официальной документации Oracle: docs.oracle.com — Joins

Но подождите…
А если в A — миллиард записей, а в B — всего две?
Проходить миллиард раз и искать в B — нерационально.
Очевидное решение — просто поменять местами таблицы.

И вот тут начинается самое интересное —
🧩 Оптимизатор должен выбрать порядок соединения таблиц.

Если таблиц много (A, B, C, D, …, Z),
то количество вариантов соединения растёт факториально:

10! = 3 628 800 вариантов при соединении 10 таблиц

Даже Терминатор T-800 устанет столько считать!

Oracle действительно оценивает разные варианты,
но чтобы не сойти с ума, ставит внутренние ограничения
(точную границу в документации не раскрывают) на разбор всех вариантов. Тут хинты требуются точно.

💡 Как подсказать оптимизатору, в каком порядке соединять таблицы?

Хинт LEADING — явно указываем порядок:

SELECT /*+ LEADING(z y x ... a) */
  a.*, b.*
FROM a 
JOIN b ON (a.id = b.id)
JOIN c ON (a.id = c.id)
...
JOIN z ON (y.id = z.id);

Хинт ORDERED — порядок берётся прямо из текста запроса:

SELECT /*+ ORDERED */
  ...
FROM a
JOIN b
JOIN c ...

📚 Сегодня мы обсудили:
1️⃣ Тип соединения таблиц — Nested Loop
2️⃣ Порядок соединения и способы его изменить (LEADING, ORDERED)

А что делать, если в A — 1 млрд строк, а в B — 2 млрд?
Даже уникальный индекс не спасёт — цикл будет слишком долгим.
В следующем посте разберём другие типы соединений:
HASH JOIN и MERGE JOIN.

💎 Поддержка канала

👍 Палец вверх — продолжаем разбор SQL-планов
👎 Палец вниз — зачем писать очевидные вещи?

💬 Если заметили неточность — напишите в комментариях 👇
#️⃣ #SQLOptimization #SQL #Oracle

🎵 «А нам всё равно, пусть боимся мы волка и сову…» — Леонид Дербенёв.

📊 Простая, но полезная задача из практики

Часто приходится проверять, что возвращает метод или процедура, особенно если она выдаёт результат в виде SYS_REFCURSOR.
Но когда полей в выборке десятки (а то и сотни) — перечислять их явно в FETCH совсем не хочется 😅

Например, такой метод

CREATE OR REPLACE PACKAGE test_refcur AS
  PROCEDURE get_refcur(refcur OUT SYS_REFCURSOR);
END test_refcur;
/

CREATE OR REPLACE PACKAGE BODY test_refcur AS
  PROCEDURE get_refcur (refcur OUT SYS_REFCURSOR) IS
  BEGIN
    OPEN refcur FOR
      SELECT 
        1 AS col1,
        2.3 AS col2,
        '4' AS col3,
        CAST (NULL AS NUMBER) AS col4,
        CAST (NULL AS VARCHAR2 (10)) AS col5
      FROM DUAL;
  END get_refcur;
END test_refcur;
/

🧩 Вопрос подписчикам:
Как вывести содержимое SYS_REFCURSOR — включая NULL-поля — не указывая имена колонок явно?

Есть способ сделать это лаконично и даже красиво 😎
Как бы Вы поступили в такой ситуации?

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

👍 Палец вверх — задача полезная
👎 Палец вниз — бессмысленная трата времени

👇 Делитесь своими решениями в комментариях!
#️⃣ #Cases #SQL #Oracle #PLSQL

🎵 «В темно-синем лесу
Где трепещут осины
Где с дубов-колдунов
Облетает листва
Hа поляне траву
Зайцы в полночь косили
И при этом напевали
Странные слова» — Леонид Дербенёв.

🎯 Решение задачи про вывод SYS_REFCURSOR

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

Вот один из простых и красивых способов:
через DBMS_XMLGEN, который превращает курсор в XML и сохраняет даже NULL-поля.

DECLARE
  refcur  SYS_REFCURSOR;
  xml  CLOB;
  ctx   DBMS_XMLGEN.ctxhandle;
BEGIN
  TEST_REFCUR.get_refcur (refcur => refcur);

  ctx := DBMS_XMLGEN.newcontext(refcur);

  DBMS_XMLGEN.setnullhandling(ctx, DBMS_XMLGEN.empty_tag);

  xml := DBMS_XMLGEN.getxml(ctx);

  DBMS_XMLGEN.closecontext(ctx);

  DBMS_OUTPUT.put_line(SUBSTR(xml, 1, 10000));
END;

📄 На выходе получаем XML со всеми колонками — даже пустыми.
Удобно, когда нужно просто убедиться, что метод возвращает нужные данные.

А один из подписчиков в LinkedIn предложил альтернативу — через DBMS_SQL:

DECLARE
    refcur               SYS_REFCURSOR;
    cur                  INTEGER;
    columnCount          INTEGER;
    columnDiscriptions   DBMS_SQL.DESC_TAB;
    columnValue          VARCHAR2 (4000);
BEGIN
    TEST_REFCUR.get_refcur (refcur => refcur);

    cur := DBMS_SQL.to_cursor_number (refcur);

    DBMS_SQL.describe_columns (cur, columnCount, columnDiscriptions);

    FOR index# IN 1 .. columnCount
    LOOP
        DBMS_SQL.define_column (cur,
                                index#,
                                columnValue,
                                4000);
    END LOOP;

    WHILE DBMS_SQL.fetch_rows (cur) > 0
    LOOP
        FOR index# IN 1 .. columnCount
        LOOP
            DBMS_SQL.COLUMN_VALUE (cur, index#, columnValue);
            DBMS_OUTPUT.put (
                   columnDiscriptions (index#).col_name
                || ' = "'
                || columnValue
                || '"  ');
        END LOOP;

        DBMS_OUTPUT.new_line;
    END LOOP;

    DBMS_SQL.close_cursor (cur);
END;

📊 Оба варианта рабочие:
DBMS_XMLGEN — проще и даёт структурированный XML,
DBMS_SQL — чуть нижеуровневый, но гибче.

⚠️UPDATE 29.10.2025 (Спасибо за инфу читателю "Asmodeus")
Дело в том, что SQL Developer, TOAD, SQL*Plus умеют "перехватывать" результат курсора и показывать его во вкладке Data Grid или подобной.
Я использую PL/SQL developer и он, к сожалению, это делать не умеет. Ну как не умеет - умеет, но в режиме Test. Пользоваться таким образом не удобно.
Итак, для "Жабы" все гораздо проще.

DECLARE
  refcur SYS_REFCURSOR;
BEGIN
  TEST_REFCUR.get_refcur(refcur => refcur);
  DBMS_SQL.RETURN_RESULT(refcur);
END;
/

Немного за PL/SQL developer стало стыдно!

UPDATE 30.10.2025
Я вспомнил еще один способ решения этой задачи.

DECLARE
  refcur SYS_REFCURSOR;
  l_xml XMLTYPE;
BEGIN
  TEST_REFCUR.get_refcur (refcur  => refcur);

  l_xml := XMLTYPE.createxml (refcur);

  CLOSE refcur;

  DBMS_OUTPUT.put_line (l_xml.GetCLOBVal());
END;

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

👍 Палец вверх — так держать
👎 Палец вниз — "машина - задний ход"

👇 Хотите молчать - молчите! Хотите сказать - скажите!
#️⃣ #Cases #SQL #Oracle #PLSQL

🎵 «Но песню иную
О дальней земле
Возил мой приятель
С собою в седле.
Он пел, озирая
Родные края:
"Гренада, Гренада,
Гренада моя!"» — Михаил Светлов

📊 Не менее полезная задача из практики.
В прошлом посте мы разбирали, как неявно вывести поля из SYS_REFCURSOR — много полезного подчерпнул.
Теперь предвкушаю такое же удовольствие по похожей задаче.

Довольно часто нужно проверять коллекцию из набора полей.
Указывать их явно в цикле утомительно, особенно если их десятки.
Мне интересно: как вы решаете такую проблему?

Например, есть функция и процедура, которые возвращает коллекцию:

CREATE OR REPLACE TYPE COLLECTION_REC FORCE IS OBJECT
(
    col1 INTEGER,
    col2 NUMBER,
    col3 VARCHAR2 (10),
    col4 NUMBER,
    col5 VARCHAR2 (10)
)
/
CREATE OR REPLACE TYPE COLLECTION_TBL FORCE IS TABLE OF COLLECTION_REC
/
CREATE OR REPLACE PACKAGE TEST_COLLECTION
AS
    PROCEDURE get_collection (collection OUT COLLECTION_TBL);
    
    FUNCTION get_collection RETURN COLLECTION_TBL;
END TEST_COLLECTION;
/ 
CREATE OR REPLACE PACKAGE BODY TEST_COLLECTION
AS
    PROCEDURE get_collection (collection OUT COLLECTION_TBL)
    IS
    BEGIN
        collection :=
            COLLECTION_TBL (COLLECTION_REC (col1   => 1,
                                            col2   => 2.3,
                                            col3   => '4',
                                            col4   => NULL,
                                            col5   => NULL));
    END get_collection;

    FUNCTION get_collection RETURN COLLECTION_TBL
    IS
    BEGIN
        RETURN
            COLLECTION_TBL (COLLECTION_REC (col1   => 1,
                                            col2   => 2.3,
                                            col3   => '4',
                                            col4   => NULL,
                                            col5   => NULL));
    END get_collection;
END TEST_COLLECTION;
/

🧩 Вопрос подписчикам:
Как вывести содержимое коллекций, не указывая имена колонок явно?

Я знаю хороший способ для функции, но для процедуры — пока нет.

⚠️ Хотите проверить скрипты, но нет базы под рукой — онлайн-песочница
💎 Поддержка канала⁉️

👍 Палец вверх — вызов принят
👎 Палец вниз — скукотище

👇 Делитесь своими подходами в комментариях!
#️⃣ #Cases #SQL #Oracle #PLSQL

🎵«Он песенку эту
Твердил наизусть...
Откуда у хлопца
Испанская грусть?
Ответь, Александровск,
И, Харьков, ответь:
Давно ль по-испански
Вы начали петь?» - Михаил Светлов

💡 Разбор задачи — как вывести содержимое коллекции, не указывая поля явно.

🧩 Задача:
Есть типы COLLECTION_REC и COLLECTION_TBL, а также функция и процедура, возвращающие коллекцию.
Нужно красиво вывести содержимое, не перечисляя вручную col1, col2, col3, …

Решение для функции
Мой способ — использовать DBMS_XMLGEN.
Он позволяет превратить результат SQL-запроса (в том числе из коллекции) в XML
и посмотреть структуру без указания имён колонок.
Единственно, этот метод явно пустые значения не выдает.

DECLARE
    l_xml   CLOB;
BEGIN
    l_xml :=
        DBMS_XMLGEN.getXML (
            sqlQuery   =>
                'SELECT * FROM TABLE(CAST(TEST_COLLECTION.get_collection () AS COLLECTION_TBL))');
    DBMS_OUTPUT.put_line (l_xml);
END;

Результат:

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <COL1>1</COL1>
  <COL2>2.3</COL2>
  <COL3>4</COL3>
 </ROW>
</ROWSET>

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

⚙️ Провернуть такой фокус с процедурой не получится, так как DBMS_XMLGEN не поддерживает передачу параметров в запрос.
Приходится "костылить" - , проще всего, сделать функцию-обёртку,
которая просто вызывает процедуру и возвращает ту же коллекцию, и
тогда можно пользоваться универсальным способом через DBMS_XMLGEN, без циклов и лишнего кода.

Итого:
Для функции → DBMS_XMLGEN.getxml('SELECT * FROM TABLE(CAST(tmp_function () AS COLLECTION_TBL))')
Для процедуры → завернуть в функцию.
Никаких явных имён полей, всё динамически.

UPDATE 2025.10.31
Подписчик Антон предложил вариант через ANYDATA (Спасибо огромное).
Вариант проще моего, к тому же повторяет названия коллекции COLLECTION_TBL и название объекта COLLECTION_REC .

DECLARE
    l_xml        XMLTYPE;
    collection   COLLECTION_TBL;
BEGIN
    TEST_COLLECTION.get_collection (collection => collection);
    l_xml := XMLTYPE (ANYDATA.convertCollection (COALESCE (collection, COLLECTION_TBL ())));

    DBMS_OUTPUT.put_line (l_xml.getclobval(1,4));
END;

Вот так: век живи - век учись!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

👍 Палец вверх — отличный контент.
👎 Палец вниз — не то, что надо.

👇 А Вы как выводите содержимое коллекций в Oracle?
#️⃣ #Cases #SQL #Oracle #PLSQL

🎵 «Не кочегары мы, не плотники,
Но сожалений горьких нет, как нет!
А мы монтажники-высотники, да,
И с высоты вам шлём привет!» — Владимир Котов

🚀 Серия: Оптимизация SQL-запросов. HASH-соединения таблиц

В прошлый раз мы разобрали, как работает NESTED LOOP JOIN — пошаговое соединение,
когда для каждой строки одной таблицы ищутся совпадения в другой.
Но что, если у нас миллиарды строк с обеих сторон?

Возьмём две таблицы: A и B и в каждой — по миллиарду записей.
И самый простой запрос:

SELECT a.*, b.*
FROM a
JOIN b ON (a.id = b.id);

В таком случае оптимизатор выбирает другие механизмы — HASH JOIN или MERGE JOIN.

📘 HASH JOIN — тяжёлая артиллерия оптимизатора

Перед тем как соединять таблицы, Oracle считывает данные и строит hash-таблицы,
которые состоят из результата hash-функции и данных таблицы.

Как именно оптимизатор строит hash-таблицу?
Он делает это по условию соединения — в данном случае по a.id и b.id.

Точную функцию, которую использует Oracle, мы не знаем,
но, вероятно, она похожа на:

STANDARD_HASH(a.id)

Если соединение идёт по нескольким полям:

a.id = b.id AND a.something = b.something

то hash-функция будет примерно такой:

STANDARD_HASH(a.id || '/' || a.something)

где '/' — это разделитель, помогающий избежать коллизий и проблем с NULL.

Главная идея метода — минимум обращений к диску:
данные считываются один раз (или частями, если они слишком велики).

Пример плана выполнения:

|   0 | SELECT STATEMENT     |      
| * 1 |   HASH JOIN          |      
|   2 |    TABLE ACCESS FULL | A    
|   3 |    TABLE ACCESS FULL | B    

Особенности HASH JOIN
1️⃣ Соединение только по равенству.
Если условие будет вроде

a.id > b.id - 1 AND a.id < b.id + 1

то построить hash-таблицы невозможно — хэш не работает с неравенствами.
2️⃣ Порядок таблиц имеет значение.
Первая таблица (build input) должна быть меньше по размеру.
Если A — 1 млрд записей, а B — 100 млн, правильный порядок: B => A, а не наоборот.
3️⃣ Использование TEMP.
На малых объёмах hash-таблица хранится в памяти,
но при больших — оптимизатор выгружает промежуточные данные в табличное пространство TEMP.
Если там не хватает места — возможны сильные тормоза.
4️⃣ Коллизии.
Иногда hash-функция вычисляет одинаковые значения для разных строк.
Oracle умеет такие конфликты корректно обрабатывать.
5️⃣ Для глубокого понимания.
Подробный разбор алгоритма есть в книге Джонатана Льюиса
«Основы стоимостной оптимизации» — чтение полезное, но не из лёгких. Я новичкам читать не рекомендую - отобьёт всякое желание.

В итоге оптимизатор сравнивает:
что выгоднее — прочитать всё и построить hash-таблицу,
или пройтись циклом (Nested Loop).

Из практики — в сложных запросах оптимизатор может ошибаться и выбрать не самый удачный вариант.
Поэтому можно явно подсказать порядок и тип соединения:

SELECT /*+ LEADING(b a) USE_HASH(b a) */
  a.*, b.*
FROM a
JOIN b ON (a.id = b.id);

или так:

SELECT /*+ ORDERED USE_NL(a b) */
  a.*, b.*
FROM a
JOIN b ON (a.id = b.id);

💡 Можно строить гипотезы, какой метод лучше,
но любые гипотезы требуют проверки и замеров.

Чтобы не "перекормить" новичков информацией,
метод MERGE JOIN разберём в следующем посте.

⚠️ Хотите проверить скрипты, но нет базы под рукой — используйте онлайн-песочницу.
💎 Поддержка канала⁉️

👍 Палец вверх — машина: "Полный ход!"
👎 Палец вниз — Стоп машина!

👇 Если заметили неточность — напишите!
А если есть, что добавить — тем более.
#️⃣ #SQLOptimization #SQL #Oracle

🎵 «Там, где клен шумит над речной волной,
Говорили мы о любви с тобой.
Пожелтел тот клен, в поле бродит мгла,
А любовь как сон стороной прошла.» - Леонтий Шишко

📌 Рубрика: решение задач процедурными методами
Тема: Проверка, является ли бинарное дерево деревом поиска (BST — Binary Search Tree)

Что делает скрипт?
Он создаёт бинарное дерево, обходит его разными способами (в глубину и в ширину)
и проверяет, выполняется ли у него свойство BST —
у каждого узла значение слева меньше, а справа больше.

Этапы работы скрипта:

1️⃣ Инициализация дерева.
Процедура init_btree создаёт случайное бинарное дерево из N чисел,
вставляя каждое значение по правилам дерева поиска:
меньшее идёт влево,
большее — вправо.
Хранение ведётся в объектной таблице nodes,
а каждый узел — это объект типа node с полями "value", left, right.

2️⃣ Вывод дерева.
print_btree рекурсивно обходит дерево и показывает:
уровень узла,
его значение,
есть ли левые/правые потомки.

3️⃣ Обход в ширину (BFS).
get_BFS формирует таблицу уровней, а print_BFS выводит дерево по слоям,
как будто вы смотрите на него горизонтально.

4️⃣ Проверка на BST.
Функция is_bst проверяет каждый узел рекурсивно:
если левый ребёнок ≥ родителя — дерево нарушает правила;
если правый ≤ родителя — тоже нарушение.
Если всё соблюдено — возвращается TRUE,
и вы увидите сообщение:
BFS IS BST!
иначе — BFS IS NOT BST!

Код во вложении.

💡 Как работает:
Каждый узел проверяет своих потомков и сообщает:
«Я — родитель, и порядок у меня соблюдён!»
Если хотя бы один узел нарушает порядок — дерево теряет статус BST.

📊 На выходе вы увидите дерево, его уровни и результат проверки.

⚠️ Хотите попробовать, но нет базы под рукой — воспользуйтесь онлайн-песочницей.
💎 Поддержка канала⁉️

👍 Палец вверх — если считаете, что PL/SQL способен на алгоритмы
👎 Палец вниз — если деревья не дают вам спать

💬 Молчание - золото? Возможно, но только не на этом канала! 👇.
#️⃣ #SQL #Oracle #PLSQL

🎵 «Ах, как хочется вернуться,
Ах, как хочется ворваться в городок
На нашу улицу в три дома,
Где все просто и знакомо, на денек.» — Кирилл Крастошевский

📌 Затравка в следующему посту.
Как Вы считаете, есть ли здесь неточность в запросе?
Выдаст ли ORACLE exception NO_DATA_FOUND?

DECLARE
  something   NUMBER := 122;
  id          NUMBER;
BEGIN
  SELECT a.id
    INTO id
    FROM a
   WHERE a.something = something;

  DBMS_OUTPUT.put_line ('id = ' || TO_CHAR (id));   
END;

если

CREATE TABLE a
(
  id           NUMBER PRIMARY KEY,
  something    NUMBER
);

CREATE UNIQUE INDEX a_01
  ON a (something);

INSERT INTO a (id, something)
VALUES (1,123);

COMMIT;

⚠️ Хотите попробовать, но нет базы под рукой — воспользуйтесь онлайн-песочницей.
💎 Поддержка канала⁉️

👌 - Если не вижу проблем.
🙏 - Если считаю, что ошибка есть.

💬 Буквы => Слова => Предложения => Комментарии 👇.
#️⃣ #Cases #SQL #Oracle #PLSQL

🎵«Где без спроса входят в гости,
Где нет зависти и злости — милый дом,
Где рождение справляют
И навеки провожают всем двором.» — Кирилл Крастошевский

📌 Сегодня — о возвращении к основам.
Вот пример, где всё кажется очевидным:

DECLARE
  something   NUMBER := 122;
  id          NUMBER;
BEGIN
  SELECT a.id
    INTO id
    FROM a
   WHERE a.something = something;

  DBMS_OUTPUT.put_line('id = ' || TO_CHAR(id));
END;

🧩 Выглядит невинно, но проблема — в именах.
PL/SQL видит в запросе два something:
одно — из таблицы, другое — из блока.

Без уточнения, что something — это переменная, легко попасть в ловушку:
код выглядит корректно, но сравнение может вернуть не тот результат, или не вернуть ничего вовсе.
Дело в том, что условие a.something = something выглядит в глазах ORACLE, как 1 = 1, если something IS NOT NULL.

⚙️ Как избежать таких ситуаций?
Есть два подхода:
Использовать “венгерский код”, когда имя отражает тип или назначение переменной:
v_ — обычная переменная,
p_ — параметр,
l_ — локальная,
c_ — константа.

Большинство организаций применяют этот подход — он рабочий.
Но не всем он нравится.

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

Я подхожу к неймингу по другому - имена даю из расчета сути переменной и не более.
Если меня зовут Кирилл, зачем мне писать l_kirill или p_kirill?
Если вы работаете с заказом (order), пользователем (user) или другой сущностью (entity) — зачем лишние префиксы и суффиксы?

Хотя, как говорится, это вопрос “религиозный”.
Должны же мы разработчики хоть во что-то верить.

💡 Совет:
Какое бы правило именования вы ни выбрали —
всегда явно указывайте полное имя переменной в DML-запросах.

Полное имя состоит из двух частей:
имя блока или метода и имя переменной через точку.

Даже неименованному блоку можно задать имя!

DECLARE
BEGIN
 <<get_a_id>>
  DECLARE
    something   NUMBER := 123;
    id          NUMBER;
  BEGIN
    SELECT a.id
      INTO id
      FROM a
     WHERE a.something = get_a_id.something;

    DBMS_OUTPUT.put_line ('id = ' || TO_CHAR (id));
  END;
END;
/

или при создании метода:

CREATE OR REPLACE PROCEDURE get_a_id (something IN NUMBER)
IS
  id  NUMBER;
BEGIN
  SELECT a.id
    INTO id
    FROM a
   WHERE a.something = get_a_id.something;

  DBMS_OUTPUT.put_line('id = ' || TO_CHAR(id));
END get_a_id;
/

🎯 Результат:
📉 меньше шансов на логические ошибки,
📈 выше читаемость,
и Oracle не преподносит сюрпризов.

⚠️ Хотите попробовать, но нет базы под рукой — воспользуйтесь онлайн-песочницей.
💎 Поддержка канала⁉️

👍 Палец вверх — нравится
👎 Палец вниз — не нравится

💬 Каждый комментарий на вес золото. Будьте щедры 👇.
#️⃣ #Cases #SQL #Oracle #PLSQL

🎵 «Капелькой дождя упал
На твои ладони этот день.
Ты меня повсюду ждал
И бродил за мною словно тень.
Прячешь ты глаза словно
Робостью навек, скован.
Сделай первый шаг,
Будь же посмелей со мной» — Леонид Агутин

🚀 Серия: Оптимизация SQL-запросов. MERGE-соединения таблиц

В прошлый раз мы говорили о HASH JOIN — тяжёлой артиллерии оптимизатора, которая идеально подходит для гигантских таблиц и равных соединений.

Сегодня разберём MERGE JOIN — метод, который сочетает в себе простоту и эффективность, но требует одного важного условия: оба набора данных должны быть отсортированы по ключу соединения.
Вернее, если набор данных отсортирован, такой вид соединения весьма эффективен. Если же нет — Oracle потратит значительные ресурсы на сортировку. ⚡

Итак, по порядку:

Если у нас есть таблицы A и B, и обе отсортированы по id,
Oracle считывает данные из двух наборов и идёт по ним “в ногу”, сравнивая строки и сдвигаясь вперёд, пока не найдёт совпадение.
Сначала он идёт по первому набору, пока не найдёт расхождение со вторым. Потом перескакивает и идёт по второму набору до расхождения, и так до конца одного из списков.

Понятней будет на примере:

SELECT a.*, b.*
FROM a
JOIN b ON (a.id = b.id);

Есть набор данных, по которым идёт соединение:

A = 1, 2, 3, 5, 6, 8, 9
B = 3, 4, 5, 6

Два списка можно представить так:

A: 1   2   3       5   6       8   9
B:         3   4   5   6
       ↑       ↑
     ptrA    ptrB

Комментарий:
ptrA и ptrB — указатели на элементы набора A и B.
Oracle идёт по общему отсортированному потоку, сравнивая стрелки:
если A < B → двигаем A ⬇️
если A > B → двигаем B ⬇️
если A = B → найдено совпадение ✅ → двигаем обе стрелки ⬇️⬇️

Результат: пары (3,3), (5,5), (6,6)

Вот как MERGE JOIN выглядит в плане выполнения:

|   0 | SELECT STATEMENT     |
| * 1 |   MERGE JOIN         |
|   2 |    SORT JOIN         | A
|   3 |    SORT JOIN         | B

📊 Получается потоковое соединение без случайных обращений к данным.

🔥 Когда MERGE JOIN — лучший выбор
1️⃣ Оба набора данных уже отсортированы (например, при индексе по ключу соединения).
2️⃣ Условие соединения — равенство или диапазон (в отличие от HASH JOIN).
3️⃣ Таблицы слишком большие для полной hash-таблицы в памяти.
4️⃣ Нужно соединять результат, который уже упорядочен — например, после ORDER BY или GROUP BY.

⚠️ Особенности и подводные камни
1️⃣ Сортировка — не бесплатная.
Если данные не отсортированы, Oracle создаст временные сортировки (SORT JOIN).
Это может привести к активному использованию TEMP и росту времени выполнения.
2️⃣ MERGE JOIN может быть быстрее HASH JOIN,
если сортировки уже есть (например, индексы по id) или если соединяются диапазоны.
3️⃣ Порядок таблиц важен.
Как и в других соединениях, можно управлять порядком через хинт LEADING.
Я не нашел, прямого упоминания, что порядок имеет значение. Но и обратного утверждения в доках нет. Возможно, что разница есть, но она не значительная.

В сложных запросах, особенно с временными данными, оптимизатору сложно разобраться.
Он может выбрать далеко не самый оптимальный вариант.
Его можно понять — "Все мы люди и все ошибаемся"
Поэтому можно явно подсказать порядок и тип соединения:

SELECT /*+ LEADING(b a) USE_MERGE(b a) */
  a.*, b.*
FROM a
JOIN b ON (a.id = b.id);

или так:

SELECT /*+ ORDERED USE_HASH(a b) */
  a.*, b.*
FROM a
JOIN b ON (a.id = b.id);

💡 Если вы считаете, что оптимизатор ошибается — смело давайте ему советы. Только ваши гипотезы требуют проверки и доказательства. Нельзя вмешиваться без чёткой уверенности в своих действиях.

⚠️ Хотите проверить скрипты, но нет базы под рукой — используйте онлайн-песочницу.
💎 Поддержка канала⁉️

👍 Палец вверх — есть контакт
👎 Палец вниз — нет контакта

👇 Каждый комментарий от профи — драгоценность для начинающих.
Каждый вопрос от новичка — проверка знаний для профи.
И то, и другое приветствуется.
#️⃣ #SQLOptimization #SQL #Oracle