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

🚀 Серия: Оптимизация 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

🔆 «Мало кто знает, как размножаются колобки.
Они забираются на сусеки и там метутся и скребутся!» — анекдот

📌 Нейминг для Терминатора T-800 или умение писать читаемый код

Многие, как и я в прошлом, недооценивают значение нейминга в коде.
Ведь, как нас учат в школе и в ВУЗах — алгоритмы, оптимизация, следите за памятью… и, по сути, всё.
Ребята, этого драматически мало.

🧩 Нейминг и архитектура кода — это важные, критически важные вещи.

Если бы код читал Терминатор T-800 🤖, то для него что переменная
user_id NUMBER;, что a765434 NUMBER; — разницы нет.
Для человека же контекст и имена, связанные с ним, имеют огромное значение.

📜 Возьмём отрывок из простой русской сказки — «Колобок».
Вот как она звучит в оригинале:

«Жил-был старик со старухою.
Просит старик: „Испеки, старуха, колобок“.
— „Из чего печь-то? Муки нету“.
— „Э-эх, старуха! По коробу поскреби, по сусекам помети; авось муки и наберётся“.»

Теперь заменим:
👴 Дед → u1 👵 Старуха → u2 🍞 Колобок → u3
⚙️ Действия: Жил → a1, Был → a2, Просит → a3, Испеки → a4, Поскреби → a5, Помети → a6, Наберётся → a7

a1-a2 u1 со u2.
a3 u1: «a4, u2, u3».
— «Из чего a4-то? Муки нету».
— «Э-эх, u2! По коробу a5, по сусекам a6; авось муки и a7».

Ну как, понятно? 🙂
Это я ещё дополнения не заменил.
А теперь представьте, что таких участков — сотни и тысячи строк. Попробуйте разобраться!
То, что Терминатору не проблема, для человека — суровое испытание.
Не все ведь заканчивали школу Абвера по дешифровке.

Теперь попробуем изменить нейминг сказки (кода):

👴 Дед → old_man_user 👵 Старуха → old_woman_user 🍞 Колобок → ban_user
⚙️ Жил → live, Был → be, Просит → ask, Испеки → bake, Поскреби → scratch, Помети → sweep, Наберётся → get

live-be old_man_user со old_woman_user.
ask old_man_user: «bake, old_woman_user, ban_user». — «Из чего bake-то? Муки нету».
— «Э-эх, old_woman_user! По коробу scratch, по сусекам sweep; авось муки и get».

📘 По-моему, такой текст уже гораздо проще читать.
Так можно и суть понять, найти ошибку или даже дописать новый эпизод —
«Колобок и Бармалей», «Колобок и Чапаев» и т.д.

🎯 Мораль:
Язык программирования — это, прежде всего, язык.
Если вы что-то пишете, это должно выглядеть как повествование:
где есть главные и второстепенные герои, завязка, развязка и окончание.

👨‍💻 Программисты — это авторы.
Ваш код должен быть понятен всем, не только Вам.

Если Вы тратите на разработку 2–3 часа,
то не менее часа стоит потратить на нейминг —
продумать имена переменных и методов так, чтобы они отражали суть и были ясны без комментариев.

Да, поначалу поиск нужных слов вызывает сложности.
🎓 Умение писать читаемый код — это навык, который нарабатывается годами.

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

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

👇 Ваш комментарий нужен не только Вам.
#️⃣ #Cases #SQL #Oracle #PostgreSQL #PLSQL

🎵 «Как следует смажь оба "кольта",
"Винчестер" как следует смажь
И трогай в дорогу, поскольку
Взбрела тебе в голову блажь.» — Юлий Ким

📌 Разбор задачи: Разбиение строки на слова
Цель — реализовать задачу разделения строки на последовательность слов из словаря,
используя только процедурные методы PL/SQL, без регулярных выражений и внешних функций.

💡 Идея алгоритма:
Это приём динамического программирования (DP):
мы проверяем, можно ли разбить строку так, чтобы каждая часть входила в словарь.

Алгоритм проходит по строке, проверяет все возможные подстроки и отмечает,
где удаётся найти известное слово.

Исходные данные:
Строка: catsandddog
Словарь: cat, cats, and, sand, dog

Задача — понять, можно ли эту строку собрать из слов словаря.

🔧 Ключевые шаги:

1️⃣ Инициализируем массив matrix, где matrix(i) = 1,
если подстрока до позиции i может быть составлена из слов словаря.

2️⃣ Проходим по всем позициям строки,
для каждой проверяем возможные окончания слов —
если найдено совпадение с элементом из словаря, помечаем позицию как достижимую.

3️⃣ В массив prevs записываем позиции,
с которых начинается каждое найденное слово —
это даёт возможность восстановить путь разбиения.

DECLARE
 str            VARCHAR2 (100) := 'catsandddog';

 TYPE word_tab IS TABLE OF VARCHAR2 (100);

 words          word_tab
        := word_tab ('cat',
         'cats',
         'and',
         'sand',
         'dog');

 TYPE matrix_tab IS TABLE OF INTEGER;

 matrix         matrix_tab := matrix_tab ();
 prevs          matrix_tab := matrix_tab ();

 PROCEDURE get_matrix (words IN OUT NOCOPY word_tab, matrix IN OUT NOCOPY matrix_tab, prevs OUT matrix_tab) IS
  resultPrevs   matrix_tab := matrix_tab ();
 BEGIN
  matrix.EXTEND (LENGTH (str) + 1);

  matrix (1)   := 1;

  FOR i IN 2 .. matrix.COUNT LOOP
   matrix (i)   := 0;

   FOR j IN 2 .. i LOOP
    DECLARE
     isWordFound   VARCHAR2 (1) := 'N';
    BEGIN
     FOR q IN 1 .. words.COUNT LOOP

      IF matrix (j - 1) = 1 AND SUBSTR (str, j - 1, i - j + 1) = words (q) THEN
       isWordFound   := 'Y';
       EXIT;
      END IF;
     END LOOP;

     IF isWordFound = 'Y' THEN
      matrix (i)                        := 1;

      resultPrevs.EXTEND;
      resultPrevs (resultPrevs.COUNT)   := j;
     END IF;
    END;
   END LOOP;
  END LOOP;

  prevs        := resultPrevs;
 END get_matrix;

 PROCEDURE print_matrix (matrix IN OUT NOCOPY matrix_tab) IS
  strTemp   VARCHAR2 (100);
 BEGIN
  FOR i IN 1 .. matrix.COUNT LOOP
   strTemp   :=
    CASE
     WHEN strTemp IS NULL THEN '"' || COALESCE (TO_CHAR (matrix (i)), ' ')
     ELSE strTemp || '  "' || COALESCE (TO_CHAR (matrix (i)), ' ') || '"'
    END;
  END LOOP;

  DBMS_OUTPUT.put_line ('matrix');
  DBMS_OUTPUT.put_line (strTemp);
 END print_matrix;
BEGIN
    get_matrix (words => words, matrix => matrix, prevs => prevs);

    print_matrix (matrix => matrix);
    print_matrix (matrix => prevs);

    IF matrix (matrix.COUNT) = 1 THEN
        DBMS_OUTPUT.PUT_LINE('YES');
    ELSE
        DBMS_OUTPUT.PUT_LINE('NO');
    END IF;
END;

🪄 Что делает код:
Проверяет, можно ли из заданных слов собрать исходную строку.
Заполняет таблицу достижимых позиций.
Выводит результат и промежуточные таблицы (matrix, prevs) для визуального анализа.

Пример работы:
Исходная строка: catsandddog
Результат: NO

Потому что в словаре нет слова “ddog” — значит строка неразбиваемая. Если добавить в словарь "ddog", алгоритм сразу найдёт решение и выведет YES.

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

👍 Палец вверх — Ни дня без алгоритмов.
👎 Палец вниз — Хорошо же сидели и вот опять!

💬 Хороший коммент - половина дела 👇.
#️⃣ #Oracle #PLSQL

🔆 Cogito, ergo sum
или по-русски «Мыслю, следовательно, существую» - Рене Декарт

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

💡 Во всех источниках по оптимизации SQL можно встретить предупреждение:
если видите в плане строку MERGE JOIN CARTESIAN, — стоит насторожиться.

Почему?
Потому что это значит, что оптимизатор решил перемножить два набора данных,
то есть выполнить декартово произведение множеств.

Такой вид соединения вы гарантированно получите, если напишете, например:

SELECT * FROM a, b;
-- или
SELECT * FROM a CROSS JOIN b;
-- или
SELECT * FROM a INNER JOIN b ON (1 = 1);

📘 Результат этих запросов — это декартово произведение множеств A и B.
Если вдруг забыли, как это выглядит — вот пример:

A = {1, 2, 3}
B = {'A', 'B', 'C'}

A × B = {
  {1,'A'},{1,'B'},{1,'C'},
  {2,'A'},{2,'B'},{2,'C'},
  {3,'A'},{3,'B'},{3,'C'}
}

То есть количество строк = 3 * 3 = 9.

🧠 А теперь представьте, что таблица A содержит 1 млрд строк,
а таблица B — 100 млн строк.
И вот уже результат — 10^17 строк.
Не уверен, что во Вселенной столько атомов,
но пространства TEMP точно не хватит нигде.
Да и выполняться это будет сказочно долго.

📛 Поэтому в книгах по оптимизации справедливо пишут:
если в плане появился MERGE JOIN CARTESIAN,
скорее всего, вы забыли указать условие соединения.
У новичков такое бывает — пока рефлекс не выработался.

Но!
Я хочу поделиться своей идеей, которую придумал лет 5–6 назад.
В ней я осознанно и эффективно использую картезианское соединение.
Скорее всего, не я один, но приятно, что дошёл до этого сам.
Работа разработчика ведь часто состоит в том,
чтобы переосмысливать то, что увиденно, услышано, прочитано.

📊 Представьте, что вы читаете данные из таблицы A дважды или трижды,
но с разными условиями clause1, clause2,
а затем объединяете результаты.
Причём вам нужно рассчитать разные вычисляемые поля field1, field2
в зависимости от условий.
В выражения могут использоваться, и аналитические, и групповые функции.

Обычно это делают через UNION ALL:

SELECT expression1 AS field1, expression2 AS field2
FROM a
WHERE clause1 = TRUE
UNION ALL
SELECT expression3 AS field1, expression4 AS field2
FROM a
WHERE clause2 = TRUE;

🎯 Проблема в том, что таблица A должна считаться дважды (FULL ACCESS TABLE).
И по-другому вроде бы нельзя, ведь строки должны повторяться.

Попытка сделать всё в одном запросе через CASE не работает:

SELECT
  CASE WHEN clause1 THEN expression1 ELSE expression3 END AS field1,
  CASE WHEN clause2 THEN expression2 ELSE expression4 END AS field2
FROM a
WHERE clause1 OR clause2;

⚠️ Такой подход ошибочен,
потому что строки, подходящие под оба условия, обязаны дублироваться.

🧩 Чтобы не читать таблицу дважды, я придумал такой вариант:

SELECT
  CASE 
    WHEN c."case" = 1 AND clause1 THEN expression1 
    WHEN c."case" = 2 AND clause2 THEN expression2 
  END AS field1,
  CASE 
    WHEN c."case" = 1 AND clause1 THEN expression3 
    WHEN c."case" = 2 AND clause2 THEN expression4 
  END AS field2
FROM a
CROSS JOIN (
  SELECT 1 AS "case" FROM dual
  UNION ALL
  SELECT 2 AS "case" FROM dual
) c
WHERE clause1 OR clause2;

В чём выигрыш?
Мы читаем таблицу всего один раз,
а затем просто умножаем её на 2 с помощью CROSS JOIN.
Для многих отчётных запросов это даёт отличную оптимизацию.

💬 Кто не знал про такой подход — пользуйтесь!
Пусть MERGE CARTESIAN будет вам не врагом, а инструментом ⚙️

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

👍 Хороший прием буду знать!
👎 Какая ерунда, честно слово!

💬 Как приятно читать Ваши комменты! Вы бы знали! 👇
#️⃣ #SQLOptimization #Cases #SQL #Oracle #PostgreSQL #PLSQL

🎵 «Пусть нету ни кола и не двора,
Зато не платят королю налоги
Работники ножа и топора -
Романтики с большой дороги.» — Юрий Энтин

📌 Затравка для следующего поста про коллекции:
Анализируя "чужой" код, вижу неоднократно повторяющийся скрипт примерно с таким содержание.

DECLARE
  TYPE words_tab IS TABLE OF VARCHAR2 (100)
    INDEX BY PLS_INTEGER;

  words   words_tab;
BEGIN
  words (1) := 'Первый';
  words (2) := 'Второй';
  words (3) := 'Третий';

  FOR index# IN 1 .. words.COUNT
  LOOP
    DBMS_OUTPUT.put_line ('index#=' || TO_CHAR (index#) || ' -> ' || words (index#));
  END LOOP;
END;
/

Скрипт рабочий, но Вас ничего не смущает?

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

💬 Лучшая награда посту - это комменты! 👇
#Cases #SQL #Oracle #PLSQL

🚀 Хотите прокачать SQL, но базы под рукой нет?

У Славы Рожнева — автора курса SQL в Яндексе — есть два классных ресурса:
🔹 sqltest.online — задачи и тесты для практики
📢 Канал: t.me/sqltestonline
🔹 sqlize.online — онлайн-песочница для SQL-запросов прямо в браузере
📢 Канал: t.me/sqlize

✨ Отличный вариант, чтобы учиться и держать форму в любом месте и в любое время.
#FriendlyResources

🎵 «Нам лижут пятки языки костра.
За что же так не любят недотроги
Работников ножа и топора —
Романтиков с большой дороги?» — Юрий Энтин

📌 Разбор ситуации с ассоциативным массивом
Многие читатели обратили внимание, что демо код — не самый безопасный:

DECLARE
  TYPE words_tab IS TABLE OF VARCHAR2(100)
    INDEX BY PLS_INTEGER;

  words   words_tab;
BEGIN
  words(1) := 'Первый';
  words(2) := 'Второй';
  words(3) := 'Третий';

  FOR index# IN 1 .. words.COUNT LOOP
    DBMS_OUTPUT.put_line('index#=' || index# || ' -> ' || words(index#));
  END LOOP;
END;
/

Дело в том, что ассоциативные массивы в Oracle не гарантируют плотность индексов.
Они могут иметь пропуски, ключи вовсе не обязаны начинаться с единицы, и элементы могут добавляться в любом порядке.

👉 COUNT — это количество элементов,
а не максимальный индекс.

💥 Поэтому диапазон 1 .. words.COUNT корректен только если индексы плотные (1,2,3…) и начинаются с 1.
Но в реальном проекте никто не мешает изменить коллекцию, и тогда неизбежно вылетает ошибка:

ORA-01403: no data found

Да, можно сказать: “А я слежу за тем, чтобы коллекция всегда начиналась с 1 и была плотной.”
Но при совместной разработке невозможно гарантировать, что все коллеги будут поддерживать вот это всё.
Именно поэтому код становится небезопасным.

👍 Вариант 1: использовать обычную TABLE

DECLARE
  TYPE words_tab IS TABLE OF VARCHAR2(100);
  words   words_tab := words_tab();
BEGIN
  words.EXTEND;  words(words.COUNT) := 'Первый';
  words.EXTEND;  words(words.COUNT) := 'Второй';
  words.EXTEND;  words(words.COUNT) := 'Третий';

  FOR index# IN 1 .. words.COUNT LOOP
    DBMS_OUTPUT.put_line('index#=' || index# || ' -> ' || words(index#));
  END LOOP;
END;
/

Nested table плотная по определению, и здесь цикл безопасен.

👍 Вариант 2: безопасная навигация по индексам FIRST/NEXT

DECLARE
  TYPE words_tab IS TABLE OF VARCHAR2(100)
    INDEX BY PLS_INTEGER;

  words   words_tab;
  index#  PLS_INTEGER;
BEGIN
  words(1) := 'Первый';
  words(3) := 'Третий';

  index# := words.FIRST;
  WHILE index# IS NOT NULL LOOP
    DBMS_OUTPUT.put_line(words(index#));
    index# := words.NEXT(index#);
  END LOOP;
END;
/

Это гарантированный способ обхода любой неплотной коллекции.

🤔 Так в чём же прикол использовать TABLE OF вместо TABLE в таких плотных коллекциях?

Если коротко — ни в чём, если вам нужна плотная, предсказуемая коллекция.
INDEX BY — это гибкость, работа с “дырками” и ручное управление ключами.
TABLE — это надёжный массив с естественным, непрерывным диапазоном индексов.

Для большинства рабочих задач, где коллекция просто хранит список значений, удобнее и безопаснее использовать именно TABLE.

В качестве дополнения рекомендую статью на Habr: 📚 «Всё о коллекциях в Oracle».
Когда опыта было недостаточно, всегда использовал её как справочник.

👍 Хорошая тема для поста!
👎 Не годится никак!

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

💬 Комменты иногда читать интереснее чем пост! 👇
#Cases #SQL #Oracle #PLSQL

🎵 «Закройте вашу книжку,
Допейте вашу чашку,
Дожуйте ваш дежурный бутерброд…
Снимите и продайте последнюю рубашку
И купите билет на пароход.

Ну что там ваши сводки, анкеты и доклады,
Когда повсюду рядом — там и тут —
Счастливые находки, таинственные клады…
Неужели так и пропадут?!»
— Юлий Ким

📌 Практическая "банковская" задача

Интересную задачу прислал мой друг (Спасибо тебе, Ирек! 🙌).
Решить её можно по-разному, и наши решения с ним заметно расходятся.
Любопытно было бы узнать ваш подход — такие задачки отлично тренируют мышление.

Допустим, есть таблица:

CREATE TABLE things
(
  id NUMBER,
  saved_date DATE,
  thing NUMBER
);

CREATE UNIQUE INDEX THINGS_U01
  ON things (id, saved_date);

В таблице огромный объём данных — от 1 млн до 1 млрд строк.

А также есть входящая таблица:

CREATE TABLE tmp_orders (
  id NUMBER,
  processed_date DATE
);

Здесь данных может быть сколько угодно — от пусто до миллионов.

🎯 Требуется:
Для каждой записи tmp_orders найти thing при условиях:

1️⃣ things.id = tmp_orders.id
2️⃣ Если по tmp_orders.id данных нет — вернуть NULL
3️⃣ Для каждой tmp_orders.processed_date:
• выбрать первую запись справа, то есть processed_date <= saved_date
• если записей справа нет — взять первую запись слева, то есть saved_date < processed_date

💡 Решать можно любым способом, но результат должен быть именно в виде SQL-запроса:
(id, processed_date, thing)

👍 Хорошая задача!
👎 Так себе задача!

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

💬 Как бы эту задачу решали Вы? 👇
#RealInterviewTasks #Cases #SQL #Oracle #PostgreSQL #PLSQL #PLpgSQL

🎵 «Ах, знаю-знаю-знаю, порядок есть порядок
Все клады и находки учтены
Записаны в анкетах, отмечены в докладах
И законами все защищены
Они помогут делу, они послужат людям
Я знаю это, знаю наперёд
И значит мы не станем, и значит мы не будем
Покупать билет на пароход» — Юлий Ким

🔍 Доступ к временным данным или большая победа канала

Друзья, прежде всего хочу выразить благодарность Иреку Вафину за задачу и свой вариант решения,
а также читателю Алексею Онину за активное участие — и всем, кто заинтересовался задачей 🙌
Что сказать? Это тот редкий и любимый мной момент, когда комменты интереснее поста 😄

⭐ Были представлены несколько интересных вариантов решения
🟦 Вариант 1 — от Алексея Онина

select
    o.id,
    o.processed_date,
    COALESCE(
        (select
            tr.thing
            from things tr
            where tr.id = o.id and o.processed_date <= tr.saved_date
            order by tr.saved_date asc
            fetch first 1 rows only
        ), (select
            tl.thing
            from things tl
            where tl.id = o.id and tl.saved_date < o.processed_date
            order by tl.saved_date desc
            fetch first 1 rows only
        )
    ) AS thing
from tmp_orders o;

Всё логично 👍 И coalesce здесь не случайно:
в отличие от NVL, он не трогает следующие значения, если текущее IS NOT NULL.

🟦 Вариант 2 — также от Алексея Онина

with tr as (
    select
      t.id,
      coalesce(
          lag(t.saved_date) over(partition by t.id order by t.saved_date ASC) + interval '1' second,
          date '1900-01-01'
      ) as start_saved_date,
      nvl2(
          lead(t.saved_date) over(partition by t.id order by t.saved_date ASC),
          t.saved_date,
          DATE '9999-12-31'
      ) as end_saved_date,
      t.thing
    from things t
    where t.id in (select o0.id from tmp_orders o0)
      and t.saved_date is not null
)
select
  o.id,
  o.processed_date,
  tr.thing
from tmp_orders o
left join tr
  on tr.id = o.id
 and o.processed_date between tr.start_saved_date and tr.end_saved_date;

Тоже логично, но логика уже сложнее:
строим периоды [start_saved_date, end_saved_date] и ищем пересечение.

Проблема: нужно полностью прочитать things, что плохо для больших таблиц.

🟦 Вариант 3 — от Ирека Вафина

SELECT --+ use_hash(o t)
       o.id,
       o.processed_date,
       MAX(t.thing)
         KEEP (DENSE_RANK FIRST
               ORDER BY
                 CASE
                   WHEN o.processed_date <= t.saved_date THEN t.saved_date
                   ELSE NULL
                 END NULLS LAST,
                 t.saved_date DESC) AS thing
FROM tmp_orders o
LEFT OUTER JOIN things t ON t.id = o.id
GROUP BY o.Id, o.processed_date;

Очень интересный вариант через аналитику и сортировку.
Но проблема всё та же — полное чтение things + сортировка.

🟦 Вариант 4 — мой

Идея: использовать FFS или листовой доступ (FAST FULL INDEX SCAN).
Для этого создаём два метода:

🔹 get_previous_thing

CREATE OR REPLACE FUNCTION get_previous_thing (...)
...

🔹 get_next_thing

CREATE OR REPLACE FUNCTION get_next_thing (...)
...

И затем:

SELECT
  tmp_o.id,
  tmp_o.processed_date,
  COALESCE(
      get_next_thing (...),
      get_previous_thing (...)
  ) AS thing
FROM tmp_orders tmp_o;

Суть метода:
идём по листам индекса, достаём первый попавшийся элемент слева или справа.

🧪 Эксперимент
Заполняем:
things — 10 млн строк
tmp_orders — 10 тыс. строк
200k уникальных id × 50 записей в things

Все скрипты приложены к посту.

UPDATE 2025.11.23
В скрипте приложенном к посту есть ошибка (Спасибо Алексею Онину - заметил).
Исправленный скрипт в комментах.

Продолжение 👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL

Продолжение👆

🔍 Доступ к временным данным или большая победа канала

📊 Результаты
вариант 1
time: 0.002201 s, consistent gets: 250556, physical reads: 2, CPU: 149
вариант 2
time: 0.001823 s, consistent gets: 30506, physical reads: 30224, CPU: 171
вариант 3
time: 0.000877 s, consistent gets: 30491, physical reads: 30224, CPU: 82
вариант 4
time: 0.000311 s, consistent gets: 40247, physical reads: 2, CPU: 29

Все скрипты выполнялись на «разогретой» базе — блоки уже были в кэше.
Из-за этого результаты противоречивы:

Вариант 4 — самый быстрый,
но делает больше логических чтений (consistent gets).

Вариант 3 — читает меньше блоков,
но читает с диска, а не из кэша.

Выводы делайте сами!

👍 Отличный пост!
👎 Могло быть и лучше!

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

💬 Как бы эту задачу решали Вы? 👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL

🎵 «Тот мужик поклоны бьет, отвечает вежливо:
"Я не вор, я не шпион, я вообще-то - дух,-
За свободу за мою - захотите ежели вы -
Изобью за вас любого, можно даже двух!"
Тут я понял: это - джинн,- он ведь может многое -
Он же может мне сказать: "Враз озолочу!"...
"Ваше предложение,- говорю,- убогое.
Морды будем после бить - я вина хочу!» — Владимир Высоцкий

🔍 Проверка существования уникальной записи, или «Развенчание мифов»
В предыдущем опросе приняло участие 47 подписчиков — всем большое спасибо за неравнодушие 🙌

Честно сказать, я никогда глубоко не исследовал эту тему.
По наивности думал, что разницы нет, ведь самое тяжёлое в БД — это чтение с диска.
Всё остальное так или иначе оптимизировано и работает приемлемо.

Но коллеги настаивали на своём подходе — и я решил проверить и убедиться сам.

🧪 Начинаем эксперимент

Создадим тестовую таблицу и заполним её данными.
id заполнил значениями от 1 до 1 млн, но только нечётными.
Итого получилось 500 тыс. записей.

CREATE TABLE test_2025_11_23
(
  id       NUMBER PRIMARY KEY,
  email    VARCHAR2(200)
);

INSERT /*+ APPEND */
  INTO test_2025_11_23 (id, email)
  SELECT a.id, a.email
    FROM (
          SELECT LEVEL AS id,
                 'user_' || TRUNC(DBMS_RANDOM.VALUE(1, POWER(10, 9))) || '@example.com' AS email
            FROM DUAL
          CONNECT BY LEVEL <= POWER(10, 6)
         ) a
   WHERE MOD(a.id, 2) = 1;

COMMIT;

Далее исследуем «мини»-трассировкой три варианта проверки существования строки по уникальному индексу.
В цикле — 10 млн итераций, id вычисляется случайно от 1 до 1 млн.

⚙️ Вариант 1 — через EXCEPTION NO_DATA_FOUND

DECLARE
  test_id     test_2025_11_23.id%TYPE;
  random_id   test_2025_11_23.id%TYPE := TRUNC(DBMS_RANDOM.VALUE(1, POWER(10, 6)));
BEGIN
  SELECT t.id
    INTO test_id
    FROM test_2025_11_23 t
   WHERE t.id = get_count_ids.random_id;

  ids_count := ids_count + 1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    non_ids_count := non_ids_count + 1;
END;

⚙️ Вариант 2 — через EXISTS

От себя могу добавить: этот вариант я всегда считал самым логичным и читаемым.
Вообще люблю, когда код отражает логику:
проверяем существование → используем EXISTS. Логично! ✔️

DECLARE
  is_exists_id   VARCHAR2(1);
  random_id      test_2025_11_23.id%TYPE := TRUNC(DBMS_RANDOM.VALUE(1, POWER(10, 6)));
BEGIN
  SELECT CASE
           WHEN EXISTS (SELECT 1
                          FROM test_2025_11_23 t
                         WHERE t.id = get_count_ids.random_id)
           THEN 'Y'
           ELSE 'N'
         END
    INTO is_exists_id
    FROM DUAL;

  IF is_exists_id = 'Y' THEN
    ids_count := ids_count + 1;
  ELSE
    non_ids_count := non_ids_count + 1;
  END IF;
END;

⚙️ Вариант 3 — через MAX

DECLARE
  id_count    NUMBER;
  random_id   test_2025_11_23.id%TYPE := TRUNC(DBMS_RANDOM.VALUE(1, POWER(10, 6)));
BEGIN
  SELECT MAX(t.id)
    INTO id_count
    FROM test_2025_11_23 t
   WHERE t.id = get_count_ids.random_id;

  IF id_count IS NOT NULL THEN
    ids_count := ids_count + 1;
  ELSE
    non_ids_count := non_ids_count + 1;
  END IF;
END;

📊 Результаты

Эксперимент проводился на «разогретой» базе — все блоки таблицы были в кэше

variant1_with_exception_no_data_found
ids_count = 4998770, non_ids_count = 5001230
time: 00:02:23.434
consistent gets: 30000000
CPU: 14262

variant1_with_exists
ids_count = 4997912, non_ids_count = 5002088
time: 00:02:00.054
consistent gets: 30000000
CPU: 11942

variant1_with_max
ids_count = 4999842, non_ids_count = 5000158
time: 00:02:03.955
consistent gets: 30000000
CPU: 12377

🏁 Итог
Самый оптимальный вариант — через EXISTS.
Он быстрее и по времени, и по использованному CPU.
Исходники теста в приложении!

А выводы… делайте сами!

👍 Интересно или неожиданно!
👎 Скучно или ожидаемо!

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

💬 Хотите сказать - скажите!👇
#Cases #SQL #Oracle #PLSQL

🎵 «Глеб Жеглов и Володя Шарапов за столом засиделись не зря
Глеб Жеглов и Володя Шарапов ловят банду и главаря!
Расцвела буйным цветом малина, разухабилась разная тварь
Хлеба нет, а полно гуталина, да глумится горбатый главарь»
— Александр Шаганов

🕵️‍♂️ Детектив или просто интересная ошибка?

На днях получил задачу найти баг в коде. Задача оказалась нетривиальной: код незнакомый, пакет — больше 10 тысяч строк, ошибка — где-то внутри метода примерно на 1500 строк.

В общем, «развлечение» на четыре часа.
Но ошибка — интересная!

📌 Суть
Допустим, есть строка, в которой лежит форматированный SQL-запрос.
Запрос парсится на наличие подзапросов, которые сохраняются в коллекцию, а в исходной строке — временно заменяются.
Потом должны вернуться обратно.

Но что-то пошло не так.
Задача: выяснить, где ошибка, и предложить простой вариант исправления.

🧩 Код (публикую «как есть»)

DECLARE
  str CLOB := q'{(/**/select1 (/**/select2 (/**/select4) ) (/**/select3) ) }';
  i   INTEGER := 0;

   type lt_selects is table of varchar2(32000) index by pls_integer;
   v_child_selects                  lt_selects;
BEGIN
  loop
    for rec in (
       select
          childs_select,
          instr(lower(childs_select),'(/**/select') is_child
       from (
          select distinct substr(to_char(regexp_substr(str,'(+)\(([^()]*)\)',1,level)),1) childs_select
          from dual
          connect by level <= REGEXP_COUNT(str,'(+)\(([^()]*)\)')
       )
    )loop
      i := i + 1;
      str := replace(str,rec.childs_select,
        '/*' || TO_CHAR(i) || '_child_start*/' ||
        '@'|| TO_CHAR (i) ||'@' ||
        '/*' || TO_CHAR (i) || '_child_end*/');
              
      v_child_selects(v_child_selects.count+1) := rec.childs_select;
    end loop;
   if nvl(REGEXP_COUNT(str,'(+)\(([^()]*)\)'),0) = 0
   then
     exit;
   end if;
  end loop;

  -- return subselects
  if v_child_selects.count > 0
  then
  for i in 1..v_child_selects.count
    loop
    if instr(str,'@' || i || '@') > 0
    then
     str := replace(str,
                      '@'||i||'@',
                      v_child_selects(i)
                      );
    end if;
    end loop;
  end if;
  
  dbms_output.put_line ('str=' || str);
END;

📤 Результат

str=/*4_child_start*/(/**/select1 /*3_child_start*/@3@/*3_child_end*/ /*2_child_start*/@2@/*2_child_end*/ )/*4_child_end*/ 

❓ Интересен ли вам такой формат или эта ошибка понравилась только мне?

👍 Формат интересен!
👎 Своих проблем хватает!

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

💬 Есть что написать — пишите! 👇
#Cases #SQL #Oracle #PLSQL

🎵 «До утра не погаснет окошко —
Глеб Жеглов и Володя не спят.
Пресловутая «Чёрная кошка» забоится наших ребят!
Глеб Жеглов и Володя Шарапов заслужили в боях ордена —
После мирного дня трудового будь спокойна, родная страна!»
— Александр Шаганов

🕵️‍♂️ Детектив или просто интересная ошибка. Разбор

Вот такой legacy-код необходимо поддерживать. Руки чешутся всё переделать, но нельзя: при любом рефакторинге велик риск изменить логику — и тогда все труды обесценятся, а то и вовсе навредят.
А главное правило при разработке, как у врача, — «Не навреди!» ⚕️
Такой код у новичков вызывает шок, но, на самом деле, это весьма лайтовый вариант - бывает куда хуже!

Читатель Алексей Онин обладает поразительной наблюдательностью — и уже не раз это доказал.
Он правильно отметил, что ошибка в том, что в цикле коллекция наполняется подзапросами в прямом порядке:
от самого верхнего уровня до самого глубокого.
Чтобы подмену провести корректно, замену нужно делать в обратном порядке 🔄.

🎯 Задача: исправленный вариант.

🧩 Код (публикую «как есть»)

DECLARE
  str CLOB := q'{(/**/select1 (/**/select2 (/**/select4) ) (/**/select3) ) }';
  i   INTEGER := 0;

   type lt_selects is table of varchar2(32000) index by pls_integer;
   v_child_selects                  lt_selects;
BEGIN
  loop
    for rec in (
       select
          childs_select,
          instr(lower(childs_select),'(/**/select') is_child
       from (
          select distinct substr(to_char(regexp_substr(str,'(+)\(([^()]*)\)',1,level)),1) childs_select
          from dual
          connect by level <= REGEXP_COUNT(str,'(+)\(([^()]*)\)')
       )
    )loop
      i := i + 1;
      dbms_output.put_line ('rec.childs_select=' || rec.childs_select || ',' || TO_CHAR (rec.is_child));
      str := replace(str,rec.childs_select,
        '/*' || TO_CHAR(i) || '_child_start*/' ||
        '@'|| TO_CHAR (i) ||'@' ||
        '/*' || TO_CHAR (i) || '_child_end*/');
              
      v_child_selects(v_child_selects.count+1) := rec.childs_select;
    end loop;
   if nvl(REGEXP_COUNT(str,'(+)\(([^()]*)\)'),0) = 0
   then
     exit;
   end if;
  end loop;

  IF v_child_selects.COUNT > 0
  THEN
   <<replace_subselects_back>>
    DECLARE
      index#   PLS_INTEGER;
    BEGIN
      index# := v_child_selects.LAST;                           -- REVERSE is needed

      WHILE index# IS NOT NULL
      LOOP
        IF INSTR (str, '@' || TO_CHAR (index#) || '@') > 0
        THEN
          str :=
            REPLACE (str, '@' || TO_CHAR (index#) || '@', v_child_selects (index#));
        END IF;

        index# := v_child_selects.PRIOR (index#);
      END LOOP;
    END;
  END IF;

  DBMS_OUTPUT.put_line ('str=' || str);
END;

📤 Результат — ровно то, что и ожидалось:

str=/*4_child_start*/(/**/select1 /*3_child_start*/(/**/select2 /*1_child_start*/(/**/select4)/*1_child_end*/ )/*3_child_end*/ /*2_child_start*/(/**/select3)/*2_child_end*/ )/*4_child_end*/

Дополнительно я изменил проход по ассоциативному массиву. Мы уже подробно разбирали эту тему.
Объяснять, почему так, — не буду 😉.

👍 Хочу ещё разбор интересных ошибок!
👎 Давайте что-то другое!

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

💬 Пишите комменты: не для меня или себя, а просто так! 👇
#Cases #SQL #Oracle #PLSQL

🎵 «Сколько лет прошло, всё о том же гудят провода
Всё того же ждут самолёты
Девочка с глазами из самого синего льда
Тает под огнём пулемёта
Должен же растаять хоть кто-то» — Александр Васильев

🔒 Deadlock в одной сессии? Да, это возможно!

Что «нефиг делать» для одних — то «не знаю» для других.
Такой вопрос мне частенько задавали устно на собеседованиях, и, наверное, поэтому я так хорошо его запомнил.

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

Вот минимальный пример:

DECLARE
  v_id   t.id%TYPE := 1;

  PROCEDURE update_t (id IN t.id%TYPE)
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    UPDATE t
       SET t.something = 111
     WHERE t.id = update_t.id;

    COMMIT;
  END update_t;
BEGIN
  UPDATE t
     SET t.something = 222
   WHERE t.id = v_id;

  update_t (id => v_id);

  COMMIT;
END;

🧩 Что здесь происходит

Главный блок начинает транзакцию и ставит блокировку на строку t.id = 1.
Затем вызывается процедура update_t, помеченная как PRAGMA AUTONOMOUS_TRANSACTION — это новая независимая транзакция, работающая в рамках той же сессии.
Внутри автономной транзакции мы снова пытаемся обновить ту же строку t.id = 1.
Но строка уже заблокирована внешним UPDATE.
Oracle пытается получить блокировку… у самой себя.
Ждать сам себя он, конечно, не может — и возникает классический deadlock:
ORA-00060: deadlock detected while waiting for resource

🔍 Почему Oracle не ждёт сам себя?

Потому что автономная транзакция — это полноценная независимая транзакция со своим собственным контекстом блокировок (lock manager context).
Oracle видит две конкурирующие транзакции внутри одной сессии и прекращает ожидание, считая его заведомо бесперспективным.

🧠 Важный вывод

AUTONOMOUS_TRANSACTION — мощный инструмент,
но на практике оправдан, по моему опыту, только в двух случаях:
Логирование (особенно ошибок).
Выполнение DDL-операций как в «оболочке», чтобы скрыть неявный COMMIT для основной транзакции.

👍 Тема автономок интересна - хочу ещё!
👎 Тема автономок не интересна!

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

💬 Если есть время - уделите 30 секунд комменту!👇
#Cases #SQL #Oracle #PLSQL

🎵 «У леса на опушке
Жила Зима в избушке.
Она снежки солила
В берёзовой кадушке.
Она сучила пряжу,
Она ткала холсты,
Ковала ледяные
Над реками мосты» — Сергей Островой

🧵 Автономка и DDL операции

По реакции видно, что тема автономных транзакций зашла 🔥
Не отпускаем её и посмотрим на другой важный аспект — неявный коммит в DDL.

Для этого возьмём такую задачу 👇

--DROP TABLE test_2025_12_01 PURGE;

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

DECLARE
  PROCEDURE update_execute_rollback (execute_ddl IN VARCHAR2)
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    UPDATE test_2025_12_01 t
       SET t.something = NVL (t.something, 0) + 1
     WHERE t.id = 1;

    EXECUTE IMMEDIATE execute_ddl;

    ROLLBACK;
  END update_execute_rollback;
BEGIN
  INSERT INTO test_2025_12_01 (id, something)
       VALUES (1, 1);

  COMMIT;

  update_execute_rollback (
    execute_ddl   => q'{COMMENT ON TABLE test_2025_12_01 IS 'test_comment'}');

  update_execute_rollback (
    execute_ddl   =>
      q'{COMMENT ON COLUMN test_2025_12_01.id IS 'test_column_comment'}');

  update_execute_rollback (
    execute_ddl   =>
      q'{ALTER TABLE test_2025_12_01 ADD CONSTRAINT test_2025_12_01_chk01 CHECK (something > 0)}');

  update_execute_rollback (
    execute_ddl   =>
      q'{CREATE INDEX test_2025_12_01_i01 ON test_2025_12_01 (something)}');

 <<print_something>>
  DECLARE
    something   test_2025_12_01.something%TYPE;
  BEGIN
    SELECT t.something
      INTO something
      FROM test_2025_12_01 t
     WHERE t.id = 1;

    DBMS_OUTPUT.put_line ('something =' || TO_CHAR (something));
  END print_something;
END;

❓ Вопрос к вам

Как по-вашему, какое значение будет выведено в консоль?
И главное — почему именно такое?

👍 Славно, славно!
👎 Кошмар и ужас!

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

💬 Каждый имеет право высказаться в рамках приличия!👇
#Cases #SQL #Oracle #PLSQL

🎵 «Вот и ко мне пришла беда —
стартер заел, —
Теперь уж это не езда,
а ерзанье.
И надо б выйти, подтолкнуть —
но прыти нет, —
Авось подъедет кто-нибудь
и вытянет». — Владимир Высоцкий

🎯 Практическая задача получения списка уникальных символов.

Видимо, под впечатлением от вариантов решения его прошлой задачи, читатель Ирек Вафин, по совместительству мой друг, прислал новую задачу.

Формулируется она очень просто: требуется из строкового поля, по всем строкам большой таблицы, проанализировать и получить список всех уникальных символов, которые могут встречаться в этом поле.

Умение решать такие задачи — ценный навык: детали могут быть разные, но по сути техник решения подобных задача несколько, и трудно придумать новый велосипед 🚲🙂.

Итак, формальная постановка:
есть большая таблица (от 1 млн до 1 млрд записей).

CREATE TABLE test_texts (
  id   NUMBER PRIMARY KEY,
  text VARCHAR2(4000)
);

INSERT INTO test_texts (id, text)
VALUES (1, '11qqq%%11qq^^');

INSERT INTO test_texts (id, text)
VALUES (2, '7766&&^^qqEE11');

COMMIT;

Необходимо получить список уникальных символов (порядок не важен):

'1', 'q', '%', '^', '7', '6', '&', 'E'

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

👍 Задача моей мечты!
👎 Хорошо, что мне такая задача не попалась!

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

💬 С нетерпением жду ваши варианты решения!👇
#Cases #SQL #Oracle #PLSQL