🎵 «Пусть все будет так, как ты захочешь.
Пусть твои глаза, как пpежде, гоpят.
Я с тобой опять сегодня этой ночью.
Hу а впpочем, следующей ночью,
Если захочешь, я опять у тебя.» — Чайф.

📌 Случай из практики

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

CREATE TABLE something (
  id         NUMBER,
  col1       VARCHAR2(20),
  col2       VARCHAR2(10),
  col3       VARCHAR2(500),
  created    DATE,
  created_by NUMBER,
  updated    DATE,
  updated_by NUMBER
);

🔹 Таблица небольшая, но часто читаемая. Записи в ней редактируются, но не слишком активно.

Требование: изменить поле col1 → нужно перевести его в тип NUMBER и пересчитать значения.
Например:

CASE 
  WHEN col1 = 'value1' THEN 1
  WHEN col1 = 'value2' THEN 2
  ELSE 3
END

Казалось бы, задача простая:

1. Добавляем новое поле col1_new.
2. Заполняем его как нужно.
3. Переименовываем старое поле в col1_old, а новое — в col1.
4. Если всё прошло успешно — col1_old можно убрать или пометить как неиспользуемое.

❗ Но есть дополнительное условие:
Новое поле не должно сместиться в конец списка, а должно остаться на своём месте — сразу после id и перед col2.

🤔 Как бы вы справились с такой задачей?

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

💬 Ваши мысли и Ваши решения туда. 👇
#️⃣ #Cases #SQL #Oracle #PLSQL #PostgreSQL #PLpgSQL

🎵 «Дорога, а в дороге МАЗ
Который по уши увяз
В кабине тьма, напарник третий час молчит
Хоть бы кричал, аж зло берёт
Назад пятьсот, вперёд пятьсот
А он зубами танец с саблями стучит.» — Владимир Высоцкий

🛠 Как аккуратно поменять тип колонки в Oracle и сохранить порядок полей

Напомню первоначальную постановку:
есть таблица something (небольшая, но часто читаемая), где поле col1 хранится как VARCHAR2(20), но его нужно привести к типу NUMBER и при этом пересчитать значения по правилу:

CASE 
  WHEN col1 = 'value1' THEN 1
  WHEN col1 = 'value2' THEN 2
  ELSE 3
END

На первый взгляд — простая операция: добавил новую колонку, заполнил, потом переименовал. Но тут есть важное условие:
👉 новая колонка должна остаться на своём месте — сразу после id, а не уехать в конец таблицы.

В Oracle нет встроенного механизма «переместить колонку», поэтому решение чуть хитрее.

🔑 Подход
Ставим таблицу в READ ONLY, чтобы на момент миграции гарантировать отсуствие изменений на источнике.
Переименовываем индексы и констрейны, триггеры на старой таблице.
Создаём новую таблицу с нужной структурой — в ней уже определяем col1 как NUMBER.
Переносим данные из старой таблицы в новую с пересчётом поля col1:
Восстанавливаем объекты на новой таблице (индексы, констрейны, триггеры).
Переименовываем таблицы: старая уходит в архив (something_old), новая становится боевой (something).

📋 Полный скрипт

-- lock the table to prevent modifications
ALTER TABLE something READ ONLY;

-- rename all dependent objects on the table, if any (indexes, constraints, triggers)

-- create a new table (can be done via CTAS if there are no virtual columns)
CREATE TABLE something_new (
  id         NUMBER,
  col1       NUMBER,
  col2       VARCHAR2(10),
  col3       VARCHAR2(500),
  created    DATE,
  created_by NUMBER,
  updated    DATE,
  updated_by NUMBER
);

-- populate the new table with data from the old one
INSERT INTO something_new (id, col1, col2, col3, created, created_by, updated, updated_by)
SELECT
  s.id,
  CASE 
    WHEN s.col1 = 'value1' THEN 1
    WHEN s.col1 = 'value2' THEN 2
    ELSE 3
  END AS col1,
  s.col2,
  s.col3,
  s.created,
  s.created_by,
  s.updated,
  s.updated_by
FROM something s;

-- recreate dependent objects on new table (indexes, constraints, triggers, grants)
-- drop dependent objects on the old table

-- rename the tables
ALTER TABLE something RENAME TO something_old;
ALTER TABLE something_new RENAME TO something;

-- keep something_old for rollback scripts or to preserve original data
-- later it can be safely dropped if no longer needed

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

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

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

💬 Если есть другое решение задачи. Поделитесь! 👇
#️⃣ #Cases #SQL #Oracle #PLSQL #PostgreSQL #PLpgSQL

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

📌 Разбор задачи: Удаление N-го узла с конца связного списка
Я продолжаю разбирать задачи и решаю их исключительно процедурными методами.

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

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

CREATE OR REPLACE TYPE node IS OBJECT (VALUE NUMBER, NEXT REF node);
/
CREATE TABLE nodes OF node;
/

DECLARE
  stack   REF node;

  PROCEDURE init_stack (stack OUT REF node, amount IN INTEGER) IS
    current1   REF node;
    current2   REF node;
  BEGIN
    FOR i IN 1 .. amount LOOP
      IF current1 IS NULL THEN
        INSERT INTO nodes n
        VALUES      (node ("VALUE" => i, NEXT => NULL))
        RETURNING   REF (n)
        INTO        current1;

        stack   := current1;
      ELSE
        INSERT INTO nodes n
        VALUES      (node ("VALUE" => i, NEXT => NULL))
        RETURNING   REF (n)
        INTO        current2;

        UPDATE nodes n
        SET    n.NEXT   = current2
        WHERE  REF (n) = current1;

        current1   := current2;
        current2   := NULL;
      END IF;
    END LOOP;
  END init_stack;

  PROCEDURE print_stack (stack IN REF node) IS
    current       REF node;
    currentNode   node;
    i             INTEGER := 1;
  BEGIN
    current   := stack;

    IF current IS NULL THEN
      RETURN;
    END IF;

    WHILE current IS NOT NULL LOOP
      SELECT DEREF (current) INTO currentNode FROM DUAL;

      DBMS_OUTPUT.put_line (
           '['
        || TO_CHAR (i)
        || '] value '
        || TO_CHAR (currentNode."VALUE")
        || ', '
        || CASE WHEN currentNode.NEXT IS NULL THEN 'next is null' ELSE 'next is not null' END);

     i   := i + 1;
      current := currentNode.next;
    END LOOP;
  END print_stack;
  
  PROCEDURE delete_node_from_end (stack IN REF node, index# IN INTEGER)
  IS
    current                 REF node;
    currentNode             node;
    nodeCounter             INTEGER := 0;
    properFromBeginIndex#   INTEGER := 0;
    previousDeletedNode     REF node;
    deleted                 REF node;
    deletedNode             node;
  BEGIN
    -- считаем количество элементов
    current := stack;
    WHILE current IS NOT NULL
    LOOP
      SELECT DEREF (current) INTO currentNode FROM DUAL;
      current := currentNode.next;
      nodeCounter := nodeCounter + 1;
    END LOOP;

    properFromBeginIndex# := nodeCounter - index#;

    current := stack;
    nodeCounter := 0;
    previousDeletedNode := NULL;
    WHILE current IS NOT NULL AND nodeCounter < properFromBeginIndex#
    LOOP
      previousDeletedNode := current;
      SELECT DEREF (current) INTO currentNode FROM DUAL;
      current := currentNode.next;
      nodeCounter := nodeCounter + 1;
    END LOOP;

    SELECT DEREF (previousDeletedNode) INTO currentNode FROM DUAL;
    deleted := currentNode.next;

    SELECT DEREF (deleted) INTO deletedNode FROM DUAL;

    UPDATE nodes n
    SET    n.next = deletedNode.next
    WHERE  REF (n) = previousDeletedNode;

    DELETE FROM nodes n WHERE REF (n) = deleted;
  END delete_node_from_end;
BEGIN
  init_stack (stack => stack, amount => 10);

  DBMS_OUTPUT.put_line ('stack');
  print_stack (stack => stack);
  
  delete_node_from_end (stack => stack, index# => 6);

  DBMS_OUTPUT.put_line ('updatedStack');
  print_stack (stack => stack);

  COMMIT;
END;

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

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

💬 Обратную связь туда 👇
#️⃣ #SQL #Oracle #PLSQL

🔆«Тишина должна быть в библиотеке...» — Уральские пельмени

📌 Серия «Оптимизация SQL-запросов»
Этот пост — для тех, у кого возникают проблемы с пониманием плана запроса как такового.

Прежде чем разбирать конкретные планы запросов и их оптимизацию, давайте уясним, что такое план запроса.
Почему СУБД не всегда может гарантировать оптимальный план? И зачем нужны хинты (подсказки)?

📚 До изобретения баз данных человечество уже умело хранить и обрабатывать большие объёмы информации.
Где же? — спросите вы. Конечно же, в библиотеках!
Те, кто разрабатывал модели баз данных, имели опыт работы с библиотеками, и многие термины были заимствованы именно оттуда: индекс, ключевые слова и пр.

Аналогия с библиотекарем
Представим, что мы в самой большой библиотеке мира — Библиотеке Конгресса США в Вашингтоне.
Вы библиотекарь, и у вас нет никаких электронных устройств для помощи. Только:

📦 хранилище книг,
📑 индекс по названию,
✍️ индекс по автору,
🔑 индекс по ключевым словам.

Теперь рассмотрим примеры:

1️⃣ Самый простой запрос — «выдать все книги».
Подвозим вагоны и сгружаем в любом порядке.
Аналог: TABLE ACCESS FULL

2️⃣ Самый лёгкий запрос — найти книгу с известным местом хранения.
Просто идём и берём её, даже индекс не нужен.
Аналог: TABLE ACCESS BY ROWID

3️⃣ Запрос на книги автора — например, «Александр Сергеевич Пушкин».
Идём в индекс по авторам и получаем ссылки на его книги.
Аналог: INDEX RANGE SCAN (по диапазону).

Но что, если запрос будет таким:
автор пишет на индоевропейском языке,
фамилия начинается на «П»,
имя заканчивается на «р»,
жанр книги — сказка,
книга про рыбу.
Как быть?

Тут уже возникает несколько вариантов (планов) поиска:
искать авторов по первой букве,
искать книги по ключевым словам «сказка» и «рыба»,
пробовать разные комбинации,
или вовсе перебрать все книги.
👉 У вас уже 4 возможных плана запроса! И оптимизатору тоже приходится выбирать, какой путь будет наименее затратным.

⚙️ Оптимизатор:
каждому плану присваивает «стоимость»,
опирается на статистику (собранную заранее или из прошлых запросов),
иногда ошибается, если неправильно оценил путь поиска.

📊 Именно поэтому один и тот же запрос на разных базах может выполняться разными способами.

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

💡 Главная мысль:
Чтобы понять, что такое план запроса, поставьте себя на место библиотекаря.
Продумайте, как бы вы искали данные вручную.
Именно так «думает» оптимизатор.

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

✅ Итого:
План запроса — это последовательность шагов.
Набор приёмов ограничен и часто повторяется.
Главное — понимать логику поиска.

Надеюсь, с понятием «план запроса» мы разобрались.
Дальше будем разбирать конкретные шаги и реальные примеры.

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

👍 Палец вверх — продолжаем!
👎 Палец вниз — скучно и неинтересно.

Если есть вопросы, задавайте.
Если Вы опытный и заметили ошибку и/или неточность пишите тоже.
Если же ни то, ни другое, то всё равно пишите. 👇
#️⃣ #SQLOptimization

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

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

🖥️ Тема выпуска: Телеграм-каналы и чаты

Поскольку я блогер, то волей-неволей приходится изучать «конкурирующие фирмы».
Каналы, как и люди, — все разные:
кто-то с отличными постами, но навязчивой рекламой;
кто-то — с классным оформлением, но скучным контентом.

📢 Уважаемые читатели!
Делюсь подборкой профильных каналов на русском языке,
а также списком чатов, которые я лично не читаю,
поэтому оценить их качество не берусь — чаты просто не моё 🙂

🔧 Каналы
https://t.me/dbbooks (16.3k 👨‍💻) — крутой ресурс с множеством профильных книг
https://t.me/seniorsql (15.8k) — образовательный канал с ненавязчивой рекламой
https://t.me/sql_ready (12.4k) — профильный канал с интересным оформлением
https://t.me/sqlquestions (10.2k) — канал с интересной подачей и умеренной рекламой
https://t.me/sqlacademyofficial (8.9k) — образовательный канал с приятной подачей
https://t.me/db_in_it (8.2k) — инфо-канал с хорошим дизайном и лёгкой рекламой
https://t.me/database_info (8.1k) — образовательный канал с аккуратным оформлением
https://t.me/sqlprofi (5.2k) — интересный канал с оригинальной подачей
https://t.me/oracle_dbd (3.2k) — полезный контент, но реклама назойлива
https://t.me/pg_guru (2.8k) — профильный канал по PostgreSQL
https://t.me/sql_oracle_databases (1.8k) — образовательный ресурс по SQL и Oracle

💬 Чаты
https://t.me/sql_beginner (4.8k)
https://t.me/PostgreSQL_1C_Linux (4.1k)
https://t.me/sql_ninja (4.1k)
https://t.me/oracle_ru (1.8k)
https://t.me/oracle_dba_ru (1.5k)
https://t.me/pg_probackup (564)
https://t.me/databaselabru (273)
https://t.me/postgresprochat (79)

📚 Предлагаю всем желающим ознакомиться и найти себе контент по душе.

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

👨‍💻 Скорее всего, я что-то пропустил — какие интересные профильные ресурсы на русском знаете Вы? А может, есть классные источники на других языках? Делитесь в комментариях. 👇
#️⃣ #Tools

🎵 «И посредине этого разгула
Я прошептал на ухо жениху -
И жениха, как будто ветром сдуло,-
Невеста, вон, рыдает наверху.» — Владимир Высоцкий

⚡ Накат и откат изменений в Oracle

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

🔹 Модель действий

1. Считаем количество инвалидных объектов до наката
2. Запускаем скрипт наката/отката изменений
3. Перекомпилируем всех "инвалидов"
4. Считаем количество инвалидов после наката и сравниваем
5. Если есть ошибки — протоколируем их в отдельный журнал, например: INVALID_ERRORS$TASK_NUMBER

📌 Эта модель подходит как для наката изменений, так и для отката.
Таблица фиксирует все текущие ошибки и позволяет легко отслеживать изменения.

🔹 Создание таблицы ошибок

CREATE TABLE INVALID_ERRORS$TASK_NUMBER AS 
SELECT 
    o.owner,
    o.object_type,
    o.object_name,
    e.sequence,
    e.line,
    e.position,
    e.text
FROM 
    dba_objects o
INNER JOIN 
    dba_errors e
    ON o.object_name = e.name AND o.owner = e.owner
WHERE 
    o.status = 'INVALID'
    AND o.object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'VIEW', 'TRIGGER')
ORDER BY 
    o.owner,
    o.object_type,
    o.object_name,
    e.sequence;

🔹 Параллельная перекомпиляция объектов
После наката/отката рекомендуется использовать UTL_RECOMP для перекомпиляции всех объектов в параллели, чтобы база была в рабочем состоянии:

BEGIN
  SYS.UTL_RECOMP.recomp_parallel(
    threads => 4
  );
END;
/

Вот ссылка на официальную документацию UTL_RECOMP.

🔹 Итог
Всегда фиксируйте текущее состояние базы перед накатом.
Используйте таблицу ошибок для логирования ошибок.
Имейте скрипт отката на случай непредвиденных проблем.
После наката/отката запускайте параллельную перекомпиляцию объектов, чтобы минимизировать INVALID.

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

👍 Палец вверх — интересно!
👎 Палец вниз — мне это не нужно, и потому скучно!

Интересна Ваша обратная связь - пишите тут 👇.
#️⃣ #Cases #SQL #Oracle #PLSQL

🚀 Хотите прокачать SQL, но базы под рукой нет?
У моего коллеги Славы Рожнева — автора курса SQL в Яндексе — есть два ресурса:

🔹 sqltest.online — готовые задачи и тесты для практики.
🔹 sqlize.online — онлайн-песочница для запросов прямо в браузере.

📊 Отличный вариант, чтобы тренироваться в любом месте и в любое время!

🎵 «Один говорил: "Нам свобода - награда:
Мы поезд, куда надо ведем".
Другой говорил: "Задаваться не надо.
Как сядем в него, так и сойдем".» — Андрей Макаревич

📌 Разбор задачи: Вычисление n-го числа Фибоначчи через динамическое программирование
Представьте себе, что вы строите лестницу чисел.
Каждый новый шаг — это сумма двух предыдущих.
И вот приходит администратор и говорит: «Посчитайте десятый Фибоначчи!»

Что делает скрипт?
1. Создаёт «лестницу» чисел (массив).
2. Заполняет первые два шага единицами.
3. Каждый следующий шаг — это сумма двух предыдущих.
4. В конце вы получаете нужное число и всю последовательность целиком.

DECLARE
    TYPE matrix_tab IS TABLE OF INTEGER;

    fibMatrix   matrix_tab := matrix_tab ();
    
    fibonachiAmount INTEGER;

    PROCEDURE get_fibonachi_matrix (index# IN INTEGER, matrix IN OUT NOCOPY matrix_tab, fibonachi_amount OUT INTEGER) IS
    BEGIN
        IF matrix.COUNT >= index# + 1 THEN
            fibonachi_amount   := matrix (index# + 1);
            RETURN;
        END IF;

        IF index# >= 0 THEN
            matrix.EXTEND;
            matrix (matrix.COUNT)   := 1;

            IF index# = 0 THEN
                fibonachi_amount := 1;
                RETURN;
            END IF;
        END IF;

        IF index# >= 1 THEN
            matrix.EXTEND;
            matrix (matrix.COUNT)   := 1;

            IF index# = 1 THEN
                fibonachi_amount := 1;
                RETURN;
            END IF;
        END IF;

        FOR i IN 3 .. index# + 1 LOOP
            matrix.EXTEND;
            matrix (matrix.COUNT)   := matrix (matrix.COUNT - 1) + matrix (matrix.COUNT - 2);
        END LOOP;

        fibonachi_amount   := matrix (index# + 1);
    END get_fibonachi_matrix;

    PROCEDURE print_matrix (matrix IN OUT NOCOPY matrix_tab) IS
        str   VARCHAR2 (100);
    BEGIN
        FOR i IN 1 .. matrix.COUNT LOOP
            str   := str || ' ' || TO_CHAR (matrix (i));
        END LOOP;

        DBMS_OUTPUT.put_line (str);
    END print_matrix;
BEGIN
    get_fibonachi_matrix (index# => 10, matrix => fibMatrix, fibonachi_amount => fibonachiAmount);

    DBMS_OUTPUT.put_line ('fibonachiAmount = ' || TO_CHAR (fibonachiAmount));

    print_matrix (matrix => fibMatrix);
END;

💡 Как работает:
Первые два числа всегда 1.
Каждый новый элемент — это сумма двух предыдущих.
В итоге получаем как конкретное число, так и всю последовательность.

👍 Палец вверх — есть желание изучать алгоритмы.
👎 Палец вниз — хватит.

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

💬 Что скажет "купечество"?
#️⃣ #SQL #Oracle #PLSQL

🎵 «А я иду, шагаю по Москве,
И я ещё пройти смогу —
Солёный Тихий океан
И тундру, и тайгу…» — Сергей Никитин

📌 Разбор задачи: Разделение строки на слова

Иногда жизнь — как длинная дорога,
и каждое слово в строке — это шаг.
Одни шаги короткие, другие — длинные,
а между ними бывают остановки — пробелы.

🧠 Что делает скрипт?
1. Берёт исходную строку с кучей пробелов: ' 1 123 333 4567 '.
2. Проходит по ней символ за символом, как по дороге.
3. Когда встречает пробел — понимает: «О! шаг закончился».
4. Запоминает всё, что было между пробелами, — это и есть слово.
5. В конце выводит, сколько «шагов» пройдено и какие именно.

DECLARE
    str              VARCHAR2 (4000) := ' 1 123    333 4567  ';

    TYPE str_tab IS TABLE OF VARCHAR2 (4000);

    strs             str_tab := str_tab ();

    indexStartWord   INTEGER := 0;
BEGIN
    FOR index# IN 1 .. LENGTH (str)
    LOOP
        IF SUBSTR (str, index#, 1) = ' ' AND indexStartWord > 0
        THEN
            --DBMS_OUTPUT.put_line ('HERE');
            strs.EXTEND;
            strs (strs.COUNT) :=
                SUBSTR (str, indexStartWord, index# - indexStartWord);
            indexStartWord := 0;
        END IF;

        IF     SUBSTR (str, index#, 1) != ' '
           AND SUBSTR (str, index# - 1, 1) = ' '
        THEN
            indexStartWord := index#;
        END IF;

        --DBMS_OUTPUT.put_line (TO_CHAR (indexStartWord));
    END LOOP;

    -- print result
    DBMS_OUTPUT.put_line (' ');
    DBMS_OUTPUT.put_line ('word amount = ' || TO_CHAR (strs.COUNT));

    FOR i IN 1 .. strs.COUNT
    LOOP
        DBMS_OUTPUT.put_line (strs (i));
    END LOOP;

    strs.delete;
END;

💡 Как работает:
Код идёт по строке, как путешественник по маршруту.
Каждый пробел — это остановка,
а всё между остановками — очередное слово.

Из ' 1 123 333 4567 ' получается маршрут:

1  
123  
333  
4567

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

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

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

🎵 «А не спеть ли мне песню о любви
А не выдумать ли новый жанр
Попопсовей мотив и стихи
И всю жизнь получать гонорар» — Чиж

📚 Серия «Оптимизация SQL-запросов». Планы запросов (одна таблица): часть 1

Продолжим аналогию с библиотекой — ведь она интуитивно понятна.
Я создал таблицу BOOKS, спроектированную весьма плохо — и это намеренно.

Зачем? Чтобы показать, как работает оптимизатор и какие бывают планы выполнения запросов при обращении к одной таблице.

CREATE TABLE books (
    inventory_number NUMBER NOT NULL,
    book_name        VARCHAR2(500) NOT NULL,
    language         VARCHAR2(100) NOT NULL,
    book_genre       VARCHAR2(100) NOT NULL,
    author_fullname  VARCHAR2(100),
    author_firstname VARCHAR2(100),
    author_lastname  VARCHAR2(100),
    author_birthday  DATE,
    status_book      VARCHAR2(50),
    book_text        CLOB NOT NULL
);

📖 Таблица денормализована, индексов слишком много, констрейнов нет — всё специально.
Цель — рассмотреть, как Oracle строит планы на одной таблице.

🔍 Что у нас есть

Мы — библиотекарь (оптимизатор).
Перед нами хранилище из миллиарда книг 📦

Созданы индексы:

CREATE UNIQUE INDEX books_u01 ON books (inventory_number);              -- уникальный номер книги
CREATE INDEX books_i01 ON books (book_name);                            -- по названию
CREATE INDEX books_i02 ON books (language);                             -- по языку
CREATE INDEX books_i03 ON books (book_genre);                           -- по жанру
CREATE INDEX books_i04 ON books (book_name, book_genre, language);      -- составной
CREATE INDEX books_i05 ON books (author_fullname);                      -- по ФИО автора
CREATE INDEX books_i06 ON books (author_lastname, author_firstname);    -- по фамилии и имени
CREATE INDEX books_i07 ON books (author_birthday);                      -- по дате рождения
CREATE INDEX books_i08 ON books (status_book);                          -- по статусу (Available, Checked Out, Reserved, Repaired)

Смотрите, в ORACLE много различных индексов и все они так или иначе применяются, но база это B-tree индексы (B - это не значит бинарный, а значит сбалансированный).
Абсолютное большинство индексов, которые Вы будете встречать, это нормальные B-tree индексы.
Экзотика полезна, но её надо изучать в контексте.

Заполним таблицу тестовыми данными:

INSERT ALL
    INTO books (inventory_number, book_name, language, book_genre, author_fullname, author_firstname, author_lastname, author_birthday, status_book, book_text) 
 VALUES (1, 'War and Peace', 'English', 'Historical Novel', 'Leo Tolstoy', 'Leo', 'Tolstoy',
              TO_DATE('09-09-1828', 'DD-MM-YYYY'), 'Available',
              'Long philosophical novel about war and human destiny.')
    INTO books VALUES (2, 'Crime and Punishment', 'English', 'Psychological Fiction',
              'Fyodor Dostoevsky', 'Fyodor', 'Dostoevsky',
              TO_DATE('11-11-1821', 'DD-MM-YYYY'), 'Checked Out',
              'Story of guilt, morality, and redemption.')
    INTO books VALUES (3, 'Pride and Prejudice', 'English', 'Romance',
              'Jane Austen', 'Jane', 'Austen',
              TO_DATE('16-12-1775', 'DD-MM-YYYY'), 'Available',
              'A classic story about manners, marriage, and social class.')
    INTO books VALUES (4, 'Les Misérables', 'French', 'Historical Novel',
              'Victor Hugo', 'Victor', 'Hugo',
              TO_DATE('26-02-1802', 'DD-MM-YYYY'), 'Reserved',
              'Epic tale of injustice, revolution, and redemption.')
    INTO books VALUES (5, 'Faust', 'German', 'Tragedy',
              'Johann Wolfgang von Goethe', 'Johann', 'Goethe',
              TO_DATE('28-08-1749', 'DD-MM-YYYY'), 'Available',
              'A scholar makes a pact with the devil in search of knowledge.')
    INTO books VALUES (6, 'Don Quixote', 'Spanish', 'Adventure',
              'Miguel de Cervantes', 'Miguel', 'Cervantes',
              TO_DATE('29-09-1547', 'DD-MM-YYYY'), 'Checked Out',
              'A nobleman loses his sanity and becomes a wandering knight.')
SELECT * FROM dual;

#️⃣ #SQLOptimization #SQL #Oracle

📚 Планы запросов (одна таблица): часть 2

1️⃣ Полное чтение таблицы

SELECT * FROM books;

 Plan Hash Value  : 2688610195 

---------------------------------------------------------------------
| Id | Operation           | Name  | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT    |       |    1 |  2563 |    3 | 00:00:01 |
|  1 |   TABLE ACCESS FULL | BOOKS |    1 |  2563 |    3 | 00:00:01 |
---------------------------------------------------------------------

Notes
-----
- Dynamic sampling used for this statement ( level = 2 )

📄 План: TABLE ACCESS FULL — читаем всю таблицу от начала до конца.
Oracle оценивает объём, строки и стоимость выполнения (Cost = 3).

💡 Если нет статистики — оптимизатор делает dynamic sampling.
Чтобы помочь ему, собираем статистику:

BEGIN 
DBMS_STATS.GATHER_TABLE_STATS (USER, 'BOOKS');
END;

После этого оценки становятся точнее - 6 записей против 1.

 Plan Hash Value  : 2688610195 

---------------------------------------------------------------------
| Id | Operation           | Name  | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT    |       |    6 |  2928 |    3 | 00:00:01 |
|  1 |   TABLE ACCESS FULL | BOOKS |    6 |  2928 |    3 | 00:00:01 |
---------------------------------------------------------------------

2️⃣ Покрытые запросы (INDEX FULL SCAN)
Нужно получить все инвентарные номера:
SELECT inventory_number FROM books;

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

⚠️ Поэтому избегайте SELECT * — всегда указывайте только нужные колонки!

3️⃣ Самый быстрый запрос 🚀

SELECT * FROM books WHERE rowid = ...;

 Plan Hash Value  : 2667597667 

------------------------------------------------------------------------------
| Id | Operation                    | Name  | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |       |    1 |   244 |    1 | 00:00:01 |
|  1 |   TABLE ACCESS BY USER ROWID | BOOKS |    1 |   244 |    1 | 00:00:01 |
------------------------------------------------------------------------------

🪶 ROWID хранит физическое местоположение строки.
Oracle идёт прямо к нужному блоку, минуя индексы.

#️⃣ #SQLOptimization #SQL #Oracle

📚 Планы запросов (одна таблица): часть 3

4️⃣ Доступ по уникальному индексу

SELECT * FROM books WHERE inventory_number = 1;

 Plan Hash Value  : 6300684 

------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |    1 |   244 |    1 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID | BOOKS     |    1 |   244 |    1 | 00:00:01 |
| * 2 |    INDEX UNIQUE SCAN          | BOOKS_U01 |    1 |       |    0 | 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("INVENTORY_NUMBER"=1)

📘 План показывает:
INDEX UNIQUE SCAN по BOOKS_U01
затем TABLE ACCESS BY INDEX ROWID
То есть сначала Oracle ищет ROWID в индексе, потом берёт саму книгу.

5️⃣ Доступ по неуникальному индексу

SELECT * FROM books WHERE book_name = 'War and Peace';

 Plan Hash Value  : 480843298 

--------------------------------------------------------------------------------------------
| Id  | Operation                             | Name      | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |           |    1 |   244 |    2 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | BOOKS     |    1 |   244 |    2 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | BOOKS_I01 |    1 |       |    1 | 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("BOOK_NAME"='War and Peace')

🧭 План: INDEX RANGE SCAN
Даже если значение одно, Oracle предполагает, что книг с таким названием может быть несколько.
💬 Разница между INDEX UNIQUE SCAN и INDEX RANGE SCAN колоссальная по скорости.
Если можете писать точные фильтры по уникальному индексу — делайте это!

Пример:

SELECT * FROM books
WHERE inventory_number IN (1, 2, 3);
SELECT * FROM books
WHERE inventory_number >= 1 AND inventory_number <= 3;

Результат этих запросов идентичен, но план запросов по скорости отличается "как небо и земля".
В одном случае будет INDEX UNIQUE SCAN, в другом INDEX RANGE SCAN.
Если используется INDEX UNIQUE SCAN — это значительно быстрее, чем диапазонное условие.

6️⃣ Доступ по неполному индексу
SELECT * FROM books
WHERE language = 'Russian' AND book_genre = 'Novel';

 Plan Hash Value  : 1417276700 

--------------------------------------------------------------------------------------------
| Id  | Operation                             | Name      | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |           |    1 |   244 |    2 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | BOOKS     |    1 |   244 |    2 | 00:00:01 |
| * 2 |    INDEX SKIP SCAN                    | BOOKS_I04 |    1 |       |    1 | 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("BOOK_GENRE"='Novel' AND "LANGUAGE"='Russian')
* 2 - filter("LANGUAGE"='Russian' AND "BOOK_GENRE"='Novel')

📊 План: INDEX SKIP SCAN
Oracle использует составной индекс (book_name, book_genre, language),
пропуская первое поле (book_name).

⚠️ Если видите в плане INDEX SKIP SCAN, это сигнал проверить:
Правильные ли созданы индексы на таблице?
Не в том ли порядке созданы поля составного индекса?

7⃣ Напоследок: есть ещё INDEX FAST FULL SCAN,
но о нём как-нибудь в другой раз. Это уже разбор не для новичков.

#️⃣ #SQLOptimization #SQL #Oracle

📚 Планы запросов (одна таблица): часть 4.

📚 Сейчас же несколько советов и логику рассуждения первичной оптимизации.

🔍 Пример 1. Поиск по шаблону
Если у Вас нет нужного конкретного функционального индекса и Вам надо искать данные по шаблону в строке, используете LIKE.

SELECT * FROM books
WHERE author_lastname LIKE '%Tol';

🔧 Пример 2. Выборка по нескольким условиям
Часто ли книги отправляют на реставрацию? Ну, не часто! Из миллиарда книг, может быть 50 или 500, но не больше.
Если же Вам требуется найти все книги, которые на русском языке и отправлены на реставрацию.
Оптимизатор может использовать несколько планов.
1) Выбрать все книги на русском, а потом среди них искать, тех кто на реставрации.
2) Выбрать все книги на реставрации, а потом уже на русском.
Очевидно, что 2 вариант проще. Но бывают ситуации, что оптимизатор принимает неочевидное решение.

SELECT * FROM books b WHERE b.language = 'Russian' AND b.status_book = 'Repaired'

 Plan Hash Value  : 4070381338 

--------------------------------------------------------------------------------------------
| Id  | Operation                             | Name      | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |           |    1 |  2563 |    1 | 00:00:01 |
| * 1 |   TABLE ACCESS BY INDEX ROWID BATCHED | BOOKS     |    1 |  2563 |    1 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | BOOKS_I02 |    2 |       |    1 | 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("B"."STATUS_BOOK"='Repaired')
* 2 - access("B"."LANGUAGE"='Russian')

Смотрите, здесь оптимизатор ошибся! Он доступ по индексу использует язык access("B"."LANGUAGE"='Russian'), а потом уже фильтрует по статусу filter("B"."STATUS_BOOK"='Repaired')
Задача разработчика это прочитать и исправить. Например, явно указать какой индекс использовать для поиска нужных записей.
Для этого можно использовать хинт INDEX.

SELECT /*+ INDEX (b BOOKS_I08)*/ * FROM books b WHERE b.language = 'Russian' AND b.status_book = 'Repaired'

Теперь мы получим, то что и надо - индекс access("B"."STATUS_BOOK"='Repaired') и фильтр filter("B"."LANGUAGE"='Russian').
Оптимизатор сделал оценку плана и ошибся, в этом случае, оценка оптимизатора невалидна. Её можно игнорировать.

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("B"."LANGUAGE"='Russian')
* 2 - access("B"."STATUS_BOOK"='Repaired')

🎯 Вывод:
Проверяйте, как именно Oracle строит план.
Если оптимизатор ошибается — докажите это тестом, замером времени и статистикой.
Оптимизация — это не догадки, а проверка гипотез 💪.

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

👍 Палец вверх — если мои посты Вам интересны
👎 Палец вниз — если вяло или пошло.

💬 Пишите - для меня весьма важна обратная связь! 👇.
#️⃣ #SQLOptimization #SQL #Oracle

🎵 «"Правда всегда одна"
Это сказал фараон
Он был очень умен
И за это его называли
Тутанхамон» — Наутилус Помпилиус

🚀 Друзья! Опрос «Где вы храните константы?» показал: 66% определяют их прямо в пакетах с методами.
⚠️ Это неточность!

Проблема в ошибке:
ORA-04068: existing state of packages has been discarded

Она появляется, когда пакет с состоянием (stateful package) был пересоздан, а сессия держала старую версию.

🔹 Пример “неправильного” пакета

CREATE OR REPLACE PACKAGE PKG_TEST IS
  external_constant CONSTANT NUMBER := 0;
  PROCEDURE show_info;
END PKG_TEST;
/
CREATE OR REPLACE PACKAGE BODY PKG_TEST IS
  internal_constant CONSTANT NUMBER := 0;
  PROCEDURE show_info IS
  BEGIN
    DBMS_OUTPUT.put_line('internal_constant = ' || internal_constant);
    DBMS_OUTPUT.put_line('external_constant = ' || external_constant);
  END;
END PKG_TEST;
/

💥 Если изменить константы в другой сессии — следующая попытка использовать пакет вызовет ORA-04068.

✅ Точный подход

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

CREATE OR REPLACE PACKAGE PKG_CONSTANTS IS
  external_constant CONSTANT NUMBER := 0;
  internal_constant CONSTANT NUMBER := 0;
END PKG_CONSTANTS;
/

CREATE OR REPLACE PACKAGE PKG_TEST_NEW IS
  PROCEDURE show_info;
END PKG_TEST_NEW;
/
CREATE OR REPLACE PACKAGE BODY PKG_TEST_NEW IS
  PROCEDURE show_info IS
  BEGIN
    DBMS_OUTPUT.put_line('internal = ' || PKG_CONSTANTS.internal_constant);
    DBMS_OUTPUT.put_line('external = ' || PKG_CONSTANTS.external_constant);
  END;
END PKG_TEST_NEW;
/

Теперь пакет можно компилировать сколько угодно — ошибок ORA-04068 больше нет, скрипт работает стабильно! ✅

BEGIN
  PKG_TEST_NEW.show_info();
END;

💡 Вывод: Храните константы отдельно, и ваши stateful packages будут всегда безопасны и стабильны.

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

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

💬 Пишите Ваши слова туда! 👇.
#️⃣ #CodeArchitecture #SQL #Oracle #PLSQL

🎵 «Ах, эти черные глаза
Меня любили.
Куда же скрылись вы теперь,
Кто близок вам другой?» — Петр Лещенко

📌 Разбор задачи: Сжатие строки

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

💡 Идея проста:
Мы проходим по строке и считаем, сколько раз подряд встречается один и тот же символ.
Если символ один — просто записываем его.
Если повторяется — добавляем цифру с количеством.

📦 Исходная строка:
aaabbccccdaa

🔧 Сжатая версия:
a3b2c4da2

DECLARE
    str           VARCHAR2 (4000) := 'aaabbccccdaa';
    newStr        VARCHAR2 (4000);
    symbolCount   INTEGER;
    index#        INTEGER := 1;
BEGIN
    WHILE index# <= LENGTH (str)
    LOOP
        symbolCount := 1;

        WHILE index# + symbolCount <= LENGTH (str)
        LOOP
            IF SUBSTR (str, index#, 1) =
               SUBSTR (str, index# + symbolCount, 1)
            THEN
                symbolCount := symbolCount + 1;
            ELSE
                EXIT;
            END IF;
        END LOOP;

        IF symbolCount = 1
        THEN
            newStr := newStr || SUBSTR (str, index#, 1);
        ELSE
            newStr :=
                newStr || SUBSTR (str, index#, 1) || TO_CHAR (symbolCount);
        END IF;

        index# := index# + symbolCount;
    END LOOP;

    DBMS_OUTPUT.put_line (newStr);
END;

🪄 Что делает код:
Берёт строку и идёт по ней символ за символом.
Считает, сколько раз подряд встречается текущий символ.
Если повторов нет — просто добавляет символ.
Если есть — добавляет символ и число повторов.
На выходе получаем сжатую строку без потери информации.

🔎 Смысл задачи:
Это классический пример Run-Length Encoding (RLE) —
одного из самых простых и эффективных алгоритмов сжатия данных.

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

👍 Палец вверх — алгоритмы наше всё.
👎 Палец вниз — это лишнее.

💬 Хотите прокомментировать - милости просим! 👇.
#️⃣ #Oracle #PLSQL

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

🎯 Задача из собеседовании в ЦБ РФ в 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 минут.

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

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