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

📊 Разбор второй задачи с собеса ЦБ РФ (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

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

📌 Нейминг для Терминатора 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