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

🧵 Автономка и 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

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

🔬 Эксперимент: как быстрее всего получить набор уникальных символов из больших текстов?

Как я измучился с этой задачей! Кто бы знал! 😅
— «Зачем тебе это надо?» — спрашивает меня моя любимая женушка.
— «Мне это надо! Мне это нравится!» — отвечаю я ей.

Спасибо всем, кто активно и пассивно участвовал 🙏
Всем, кто присылал свои варианты, — искреннее уважение и благодарность.
И тебе, Ирек, — за три дня беспокойной жизни — тоже скромное, но честное спасибо 😄

Итак… всем розовых слонов раздали 🐘 — начинаем!

🛠 Подготовим тестовую среду

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

CREATE TABLE test_chars
(
  "CHAR" VARCHAR2 (1 CHAR)
);

DECLARE
  current_max_id INTEGER;
BEGIN
  FOR index# IN 1..100
    LOOP
      IF index# = 1 THEN
        current_max_id := 0;
      ELSE
        current_max_id := current_max_id + POWER (10, 3);
      END IF;

      INSERT INTO test_texts (id, text)
        WITH
          generated_rows
          AS
            (    SELECT current_max_id + LEVEL AS id, TRUNC (DBMS_RANDOM.VALUE (1, 1000)) AS text_length
                   FROM DUAL
             CONNECT BY LEVEL <= POWER (10, 3)),
          generated_random_text
          AS
           (
            SELECT a.id, CHR(
                            CASE
                              WHEN a.random_char_number < 55296 THEN a.random_char_number
                              ELSE a.random_char_number + 2048   -- без суррогатов D800–DFFF
                            END
                            USING NCHAR_CS
                          ) AS random_symbol
            FROM(  
            SELECT gr.id, TRUNC(DBMS_RANDOM.VALUE(1, 60000)) AS random_char_number
                   FROM generated_rows gr
             CONNECT BY     PRIOR gr.id = gr.id
                        AND PRIOR DBMS_RANDOM.VALUE () IS NOT NULL
                        AND LEVEL <= gr.text_length
              ) a
            )
          SELECT grt.id,
                 LISTAGG (grt.random_symbol, '') WITHIN GROUP (ORDER BY NULL)  AS text
            FROM generated_random_text grt
        GROUP BY grt.id;
    END LOOP;
END;

Мы наполняем test_texts случайными данными:

1️⃣ Длина строки — от 1 до 1000.
(Почему не 4000? LISTAGG снова шалит. В каждом релизе Oracle её чинят, но баги — как тараканы — всегда возвращаются.)

2️⃣ Символы — случайные CHAR 1–60000 с пропуском суррогатов.

Получаются строки вроде такой:

賔ᩆऩ뷖⧕쟀ᕺྗ畄됖ֱ䌆䳻漽㕲萓麱䷃䅠쿬...

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

🧪 Тестируемые варианты

1️⃣ CONNECT BY LEVEL + SYS_GUID()
2️⃣ CONNECT BY LEVEL + DBMS_RANDOM.VALUE
3️⃣ CROSS APPLY + CONNECT BY
4️⃣ PL/SQL + ассоциативный массив
5️⃣ То же, но с оптимизацией через REPLACE
6️⃣ Рекурсивный WITH (от «великого и ужасного» Славы Рожнего —
как говорится, «старый богатырь ещё может завинтить свой измаильский штык!» ⚔️)

📦 Вариант 1 — SYS_GUID()

SELECT DISTINCT SUBSTR (tt.text, LEVEL, 1)   AS "CHAR"
  FROM test_texts tt
CONNECT BY     LEVEL <= LENGTH (tt.text)
           AND PRIOR tt.id = tt.id
           AND PRIOR SYS_GUID () IS NOT NULL;

SYS_GUID() здесь выглядит тяжеловесно.
Честно говоря, напоминает «стрелять по воробьям из гаубицы».
И шанс переполнить TEMP очень велик.

📦 Вариант 2 — DBMS_RANDOM

SELECT DISTINCT SUBSTR (tt.text, LEVEL, 1)   AS "CHAR"
  FROM test_texts tt
CONNECT BY     LEVEL <= LENGTH (tt.text)
           AND PRIOR tt.id = tt.id
           AND PRIOR DBMS_RANDOM.value () IS NOT NULL;

То же, но чуть легче. TEMP всё равно в печали.

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

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

📦 Вариант 3 — CROSS APPLY

SELECT DISTINCT c."CHAR"
  FROM test_texts  tt
       CROSS APPLY (SELECT SUBSTR (tt.text, LEVEL, 1) AS "CHAR"
                      FROM DUAL
                    CONNECT BY LEVEL <= LENGTH (tt.text)) c;

Красиво, логично, TEMP не бомбится.
Но не чемпион.

📦 Вариант 4 — Ассоциативный массив

DECLARE
  TYPE chars_tab IS TABLE OF BOOLEAN
    INDEX BY VARCHAR2 (1 CHAR);

  chars        chars_tab;
  char_index   VARCHAR2 (1);
BEGIN
  FOR texts IN (SELECT tt.text
                  FROM test_texts tt)
  LOOP
    FOR index# IN 1 .. LENGTH (texts.text)
    LOOP
      chars (SUBSTR (texts.text, index#, 1)) := TRUE;
    END LOOP;
  END LOOP;

  chars.delete ();
EXCEPTION
  WHEN OTHERS THEN
    IF chars IS NOT NULL OR chars.COUNT > 0 THEN
      chars.delete ();
    END IF;
END;

Просто, надёжно, как автомат Калашникова.

📦 Вариант 5 — Ассоциативный массив + REPLACE

DECLARE
  TYPE chars_tab IS TABLE OF BOOLEAN
    INDEX BY VARCHAR2 (1 CHAR);

  chars           chars_tab;
  replaced_text   VARCHAR2 (4000 CHAR);

  char_index      VARCHAR2 (1 CHAR);
BEGIN
  FOR texts IN (SELECT tt.text
                  FROM test_texts tt)
  LOOP
    char_index := chars.FIRST;
    replaced_text := chars.NEXT (char_index);

    WHILE char_index IS NOT NULL AND replaced_text IS NOT NULL
    LOOP
      replaced_text := REPLACE (replaced_text, char_index, NULL);
      char_index := chars.NEXT (char_index);
    END LOOP;

    FOR index# IN 1 .. LENGTH (texts.text)
    LOOP
      chars (SUBSTR (texts.text, index#, 1)) := TRUE;
    END LOOP;
  END LOOP;

  chars.delete ();
EXCEPTION
  WHEN OTHERS THEN
    IF chars IS NOT NULL OR chars.COUNT > 0 THEN
      chars.delete ();
    END IF;
END;

Хорош, когда набор символов ограничен.
В случае Unicode-зоопарка — разницы почти нет.

📦 Вариант 6 — Рекурсивный WITH

WITH
  text_split ("CHAR", remaining_text)
  AS
    (SELECT SUBSTR (tt.text, 1, 1) AS "CHAR", SUBSTR (tt.text, 2) AS remaining_text
       FROM test_texts tt
     UNION ALL
     SELECT SUBSTR (ts.remaining_text, 1, 1)   AS "CHAR",
            SUBSTR (ts.remaining_text, 2)      AS remaining_text
       FROM text_split ts
      WHERE ts.remaining_text IS NOT NULL)
SELECT DISTINCT "CHAR"
  FROM text_split;

С самого начала подозрительный вариант.
И действительно — TEMP взрывается как дрожжевое тесто.

📊 Результаты
variant1 (SYS_GUID) 00:41:32 ❌
variant2 (RANDOM) 00:05:23 ❌
variant3 (CROSS APPLY) 00:04:22 ⚠️
variant4 (indexed table) 00:01:39 ⭐
variant5 (indexed + replace) 00:01:38 ⭐
variant6 (recursive with) ORA-01652 ❌ TEMP умер героем

🧠 Итог
❌ Варианты 1, 2, 6 — слишком прожорливы по TEMP.
⚠️ Вариант 3 — рабочий, но не лучший.
🏆 Варианты 4 и 5 — оптимальны.

Разницы почти нет, но если набор символов ограничен реальным алфавитом — REPLACE даёт небольшой бонус.

Все скрипты теста в приложенном файле.

🏁 Занавес
Я пошёл зализывать раны.
Надеюсь, «хитрый татарин» получил то, на что рассчитывал.

👍 Страшно интересно!
👎 Страшно скучно!

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

💬 Вот хотя бы что-то напишите - я же старался!👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL

🎵 «По первому взгляду и виду
Нетрудная, кажется, вещь:
Роскошную эту равнину
Верхом, не спеша, пересечь

Но нам, к сожаленью, известно,
Как ястребы рвутся с цепи,
Как до смерти может быть тесно
И в самой бескрайней степи» — Юлий Ким.

📦 Архив без партиций или жизнь полна приключений!

Есть задача архивирования данных на Oracle Standard Edition.
Тем, кто всю жизнь работал на Enterprise, трудно представить, но в этой версии нет возможности использовать партиции.
И задача по переводу таблиц в архив заиграла другими красками 🎨

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

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

Эта задача относительно легко решается через партиции, но их нет ❌
А как можно решить без них?

Вот "законный" список требований:
1️⃣ Данные из оперативного диска перенести в архивный диск.
2️⃣ В таблицах убрать лишнее, т.е. понизить HWM.
3️⃣ Предоставить чтение архивных данных.
4⃣ Без downtime, т.е. поток записи в таблицы останавливать нельзя.

Как бы Вы решали такую задачу концептуально?

👍 Задача стоит внимания!
👎 Это что, задача что ли?

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

💬 Ваши мысли туда!👇
#Cases #SQL #Oracle #PLSQL

🎵 «Механик, крути киноленту,
Дежурные лампы туши!
Вноси свою светлую лепту
В суровые будни души!
Свети нам лучом из окошка
И сам вместе с нами поверь,
Что эту равнину возможно
Проехать почти без потерь!..» — Юлий Ким.

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

🧠 Концепт архитектуры архивирование таблиц (SE Edition) или мир полон приключений.

1️⃣ Две рабочие таблицы вместо одной
Вместо одной журнальной таблицы используются две идентичные.
Текущая активная выбирается через синоним.
т.е. синоним это переключатель, который указывает, какая из таблиц активная для записи.

Зачем?
поток инсёртов всегда идёт в одну из них;
можно безопасно переключать поток;
неактивная таблица становится источником архивации;
после экспорта таблица очищается через TRUNCATE → низкий HWM.
Этот механизм позволяет организовать архивацию без блокировок и без downtime.

2️⃣ Периодический процесс архивации
Раз в период (День, Неделя, Месяц, Год):
Проверяем, не прервался ли предыдущий запуск архивации. Если прервался — продолжаем старый процесс.
Переключает синоним на другую рабочую таблицу.
Экспортируем данные.
Переносим остатки данных, которые могли попасть в таблицу во время переключения и за предыдуший период.
Полностью очищаем использованную таблицу через TRUNCATE.
Все действия фиксируются в логах и статусах.

3️⃣ Организация хранение архива
Видится 2 варианта:
Через отдельное архивное табличное пространство.
Через дамп файлы.

4️⃣ Самовосстановление после ошибок
Если произошёл сбой (например, возникла ошибка или пропало питание):
смена синонима не происходит.
при следующем запуске процесс сам определяет, на каком шаге остановился,
повторяет только незавершённый шаг,
продолжает дальше.

Ручное вмешательство практически не требуется в идеале.

5️⃣ Что это даёт?
✔ Архивация без партиций
✔ без downtime
✔ Минимальный рост рабочих таблиц
✔ Простое хранение архивов
✔ Прозрачное восстановление после ошибок
✔ Лёгкое удаление устаревших данных
✔ Доступ к архиву разный в зависимости от вариантов архивации

🎨 Итог:
Даже на Standard Edition можно построить надёжный процесс архивации,
который работает как "ручная партиционизация".
Хотя повторюсь - мир полон приключений, с партициями, это было бы проще.

👍 Толково!
👎 Мусор!

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

💬 Мне важно Ваше мнение. Может быть, я не вижу очевидных возможностей! 👇
#Cases #CodeArchitecture

Уважаемые читатели!

Хочу узнать ваше мнение.

В моём канале нет «пустых» постов ни о чём.
Я не публикую свои фото среди пальм и моря (хотя живу в курортном городе 🌴).
Я не хейчу «товарищей по опасному бизнесу» ради поднятия самооценки (с ней всё в порядке, спасибо).

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

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

Может быть, вам действительно интересно увидеть «внутреннюю кухню» канала, ретроспективу, цифры, историю, немного личного?

Поэтому прошу высказаться в комментариях:
нужны ли такие посты или нет?

Эмодзи в этот раз не считаются — только текстовые комменты.

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

С уважением,
Поликарпов Кирилл.

✨ «Все это, видите ль, слова, слова, слова.
Иные, лучшие, мне дороги права;
Иная, лучшая, потребна мне свобода:
Зависеть от царя, зависеть от народа —
Не всё ли нам равно? Бог с ними.
Никому
Отчёта не давать, себе лишь самому
Служить и угождать; для власти, для ливреи
Не гнуть ни совести, ни помыслов, ни шеи.»
— Александр Сергеевич Пушкин

Работа с текстовыми шаблонами — задача интересная от джуна до синьера 👨‍💻

Читатель Максим Феликсович меня справедливо осудил за то, что в последнее время задачи, которые я предлагаю, не для новичков.
Я подумал и нашёл тему, которая будет полезна для всех и при этом несложная для понимания

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

Решать такую задачу можно по-разному:

🔹 а) Самый интуитивно простой способ
Сделать несколько шаблонов и каждый использовать в отдельном случае.

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

🔹 б) Другой простой метод — метод «матрёшки»
или метод подшаблонов,
когда один общий шаблон разбивается на множество подшаблонов,
каждый заполняется отдельно,
а в конце всё собирается в готовый текст с помощью REPLACE.

Давайте рассмотрим на примере 👇

📌 Первый подход

DECLARE
  TYPE pupil_rec IS RECORD
  (
    pupil_id            NUMBER,
    name                VARCHAR2 (100),
    sex                 VARCHAR2 (1),
    birthday            DATE,
    average_score       NUMBER,
    favorite_subject    VARCHAR2 (100)
  );

  pupil        pupil_rec
                 := pupil_rec (1,
                     'Ivan Ivanоv',
                     'Y',
                     TO_DATE ('08.09.2012', 'DD.MM.YYYY'),
                     3.9,
                     'Maths');
  pupil_info   VARCHAR2 (4000);

  PROCEDURE get_pupil_info (pupil IN pupil_rec, pupil_info OUT VARCHAR2)
  IS
  BEGIN
    IF pupil.average_score >= 4
    THEN
      pupil_info :=
           'As of '
        || TO_CHAR (SYSDATE, 'dd.mm.yyyy')
        || ', we can report the following:
We are pleased to report that pupil '
        || pupil.name
        || ' ('
        || TO_CHAR (TRUNC (MONTHS_BETWEEN (SYSDATE, pupil.birthday) / 12))
        || ' years old) is doing very well academically. 
'
        || CASE WHEN pupil.sex = 'M' THEN 'He' ELSE 'She' END
         ' shows great interest in '  pupil.favorite_subject || '.
'
        || CASE WHEN pupil.sex = 'M' THEN 'His' ELSE 'Her' END
         ' average score in all subjects is '  TO_CHAR(pupil.average_score) || '.
The school is proud of this pupil.';
    ELSE
      pupil_info :=
           'As of '
        || TO_CHAR (SYSDATE, 'dd.mm.yyyy')
        || ', we can report the following:
We inform you that the pupil''s studies are '
        || pupil.name
        || ' ('
        || TO_CHAR (TRUNC (MONTHS_BETWEEN (SYSDATE, pupil.birthday) / 12))
        || ' years old) it causes concern. 
We noticed that the pupil shows interest in ' 
 TO_CHAR(pupil.favorite_subject)  ', but the general approach to learning is unsatisfactory.
'
        || CASE WHEN pupil.sex = 'M' THEN 'His' ELSE 'Her' END
         ' average score in all subjects is '  TO_CHAR(pupil.average_score) || '.
We ask you to pay attention.';
    END IF;
  END get_pupil_info;
BEGIN
  get_pupil_info (pupil => pupil, pupil_info => pupil_info);
  DBMS_OUTPUT.put_line (pupil_info);
END;

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

Работа с текстовыми шаблонами — задача интересная от джуна до синьера 👨‍💻. Продолжение.

📌 Второй подход — метод подшаблонов

Разбиваем текст на подшаблоны:

DECLARE
  TYPE pupil_rec IS RECORD
  (
    pupil_id            NUMBER,
    name                VARCHAR2 (100),
    sex                 VARCHAR2 (1),
    birthday            DATE,
    average_score       NUMBER,
    favorite_subject    VARCHAR2 (100)
  );

  pupil        pupil_rec
                 := pupil_rec (1,
                               'Ivan Ivanоv',
                               'Y',
                               TO_DATE ('08.09.2012', 'DD.MM.YYYY'),
                               4,
                               'Maths');
  pupil_info   VARCHAR2 (4000);

  PROCEDURE get_pupil_info (pupil IN pupil_rec, pupil_info OUT VARCHAR2)
  IS
    common_template            VARCHAR2 (4000)
      := '[HEADER_TEMPLATE][ESTIMATION_TEMPLATE][AVERAGE_SCORE_TEMPLATE][CONCLUSION_TEMPLATE]';
    header_template            VARCHAR2 (4000)
      := 'As of [SYSDATE], we can report the following:' || CHR (10);
    estimate_template          VARCHAR2 (4000)
      := '[GOOD_ESTIMATE_TEMPLATE][BAD_ESTIMATE_TEMPLATE]' || CHR (10);

    good_estimate_template     VARCHAR2 (4000)
      := 'We are pleased to report that pupil [PUPIL_NAME] ([PUPIL_AGE] years old) is doing very well academically.
[PUPIL_HE] shows great interest in [FAVOURITE_SUBJECT].';

    bad_estimate_template      VARCHAR2 (4000)
      := 'We inform you that the pupil''s studies are [PUPIL_NAME] ([PUPIL_AGE] years old) it causes concern.
We noticed that the pupil shows interest in [FAVOURITE_SUBJECT], but the general approach to learning is unsatisfactory.';

    average_score_template     VARCHAR2 (4000)
      :=    '[HIS] average score in all subjects is [AVARAGE_SCORE].'
         || CHR (10);
    conclusion_template        VARCHAR2 (4000)
      := '[GOOD_CONCLUSION_TEMPLATE][BAD_CONCLUSION_TEMPLATE]';

    good_conclusion_template   VARCHAR2 (4000)
                                 := 'The school is proud of this pupil.';
    bad_conclusion_template    VARCHAR2 (4000)
                                 := 'We ask you to pay attention.';
  BEGIN
    header_template :=
      REPLACE (header_template, '[SYSDATE]', TO_CHAR (SYSDATE, 'dd.mm.yyyy'));

    IF pupil.average_score >= 4
    THEN
      good_estimate_template :=
        REPLACE (good_estimate_template, '[PUPIL_NAME]', pupil.name);
      good_estimate_template :=
        REPLACE (
          good_estimate_template,
          '[PUPIL_AGE]',
          TO_CHAR (TRUNC (MONTHS_BETWEEN (SYSDATE, pupil.birthday) / 12)));
      good_estimate_template :=
        REPLACE (good_estimate_template,
                 '[PUPIL_HE]',
                 CASE WHEN pupil.sex = 'M' THEN 'He' ELSE 'She' END);
      good_estimate_template :=
        REPLACE (good_estimate_template,
                 '[FAVOURITE_SUBJECT]',
                 pupil.favorite_subject);

      estimate_template :=
        REPLACE (estimate_template,
                 '[GOOD_ESTIMATE_TEMPLATE]',
                 good_estimate_template);
      estimate_template :=
        REPLACE (estimate_template, '[BAD_ESTIMATE_TEMPLATE]', NULL);

      conclusion_template :=
        REPLACE (conclusion_template,
                 '[GOOD_CONCLUSION_TEMPLATE]',
                 good_conclusion_template);
      conclusion_template :=
        REPLACE (conclusion_template, '[BAD_CONCLUSION_TEMPLATE]', NULL);

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

Работа с текстовыми шаблонами — задача интересная от джуна до синьера 👨‍💻. Продолжение.

    ELSE
      bad_estimate_template :=
        REPLACE (bad_estimate_template, '[PUPIL_NAME]', pupil.name);
      bad_estimate_template :=
        REPLACE (
          bad_estimate_template,
          '[PUPIL_AGE]',
          TO_CHAR (TRUNC (MONTHS_BETWEEN (SYSDATE, pupil.birthday) / 12)));
      bad_estimate_template :=
        REPLACE (bad_estimate_template,
                 '[FAVOURITE_SUBJECT]',
                 pupil.favorite_subject);

      estimate_template :=
        REPLACE (estimate_template, '[GOOD_ESTIMATE_TEMPLATE]', NULL);
      estimate_template :=
        REPLACE (estimate_template,
                 '[BAD_ESTIMATE_TEMPLATE]',
                 bad_estimate_template);

      conclusion_template :=
        REPLACE (conclusion_template, '[GOOD_CONCLUSION_TEMPLATE]', NULL);
      conclusion_template :=
        REPLACE (conclusion_template,
                 '[BAD_CONCLUSION_TEMPLATE]',
                 bad_conclusion_template);
    END IF;

    average_score_template :=
      REPLACE (average_score_template,
               '[HIS]',
               CASE WHEN pupil.sex = 'M' THEN 'His' ELSE 'Her' END);
    average_score_template :=
      REPLACE (average_score_template,
               '[AVARAGE_SCORE]',
               TO_CHAR (pupil.average_score));

    common_template :=
      REPLACE (common_template, '[HEADER_TEMPLATE]', header_template);
    common_template :=
      REPLACE (common_template, '[ESTIMATION_TEMPLATE]', estimate_template);
    common_template :=
      REPLACE (common_template,
               '[AVERAGE_SCORE_TEMPLATE]',
               average_score_template);
    common_template :=
      REPLACE (common_template, '[CONCLUSION_TEMPLATE]', conclusion_template);

    pupil_info := common_template;
  END get_pupil_info;
BEGIN
  get_pupil_info (pupil => pupil, pupil_info => pupil_info);
  DBMS_OUTPUT.put_line (pupil_info);
END;

Метод подшаблонов простой, но неочевидный 🤔
Я, например, его не придумал —
а встретил у коллег лет 5 назад, и он мне понравился.

👍 Интересный подход!
👎 Это каждый школьник знает!

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

💬 Эмоции, мысли, страхи, анекдоты туда!👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL

✨ «Так всех нас в трусов превращает мысль,
И вянет, как цветок, решимость наша
В бесплодье умственного тупика,
Так погибают замыслы с размахом,
В начале обещавшие успех,
От долгих отлагательств.» — Вильям Шекспир (перевод Бориса Пастернака)

Задача на пять минут или есть ещё порох в пороховницах?
Сколько существует способов строки превратить в столбцы?
И какой способ наиболее оптимальный?

Вот представим: есть таблица

CREATE TABLE test
(
  name   VARCHAR2 (100),
  amount NUMBER
);

INSERT INTO test (name, amount) VALUES ('name1', 1);
INSERT INTO test (name, amount) VALUES ('name2', 2);
INSERT INTO test (name, amount) VALUES ('name3', 3);
COMMIT;

Как нам строки 'name1', 'name2', 'name3' превратить в столбцы? 🔄

name1_amount  name2_amount  name3_amount
     1              2              3

Есть же конструкция PIVOT.
Да, есть. Но если этих строк много?
Оптимальна ли она? ⚖️

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

💬 Каждый коммент идет в общую копилку!👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL

✨ «Но довольно!
Офелия! О радость! Помяни
Мои грехи в своих молитвах, нимфа.»
— Уильям Шекспир (перевод Бориса Пастернака)

Задача на 5 минут или способы строки превратить в столбцы 🔄

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

INSERT INTO test (name, amount)
     SELECT 'name' || TRUNC (DBMS_RANDOM.VALUE (1, 4)) AS name,
            LEVEL                                  AS amount
       FROM dual
 CONNECT BY LEVEL < 1000000;

COMMIT;

Миллион строк — достаточно, чтобы проверить на работоспособность варианты решения.

Итак, метод PIVOT задекларирован как решение для таких задач.

SELECT *
  FROM test t
       PIVOT (
         SUM (t.amount)
         FOR name
         IN ('name1' AS name1_amount,
             'name2' AS name2_amount,
             'name3' AS name3_amount)
       );

Изначально, на прежних версиях Oracle, результативность PIVOT была катастрофической на большом количестве строк.
Но Oracle эту проблему поборол, и сейчас PIVOT работает вполне адекватно.
Для меня это приятная неожиданность.

Классический и прозрачный метод.

Я бы для такой задачи выбрал именно его из-за простоты и надёжности

SELECT SUM (CASE WHEN t.name = 'name1' THEN t.amount END) AS name1_amount,
       SUM (CASE WHEN t.name = 'name2' THEN t.amount END) AS name2_amount,
       SUM (CASE WHEN t.name = 'name3' THEN t.amount END) AS name3_amount
  FROM test t;

Если же значений name много, и мы не знаем их всех,
то можно использовать динамику.

DECLARE
  v_case_list   VARCHAR2 (4000);
  v_sql         VARCHAR2 (4000);
BEGIN
  SELECT LISTAGG (
           '  SUM (CASE WHEN t.name = '''
        || t.name
        || ''' THEN t.amount END) AS '
        || t.name
        || '_amount',
           ',' || CHR (10)
         )
         WITHIN GROUP (ORDER BY t.name)
    INTO v_case_list
    FROM (SELECT DISTINCT t.name
            FROM test t) t;

  v_sql :=
       'SELECT '
    || CHR (10)
    || v_case_list
    || CHR (10)
    || 'FROM test t';

  DBMS_OUTPUT.put_line (v_sql);

  EXECUTE IMMEDIATE v_sql;
END;

Есть и способ через XMLTYPE получить динамический набор

SELECT XMLAGG (
         XMLELEMENT (EVALNAME (t.name), t.sum_amount)
         ORDER BY t.name
       ) AS xml_data
  FROM (SELECT t.name, SUM (t.amount) AS sum_amount
          FROM test t
      GROUP BY t.name) t;

👍 Хорошо!
👎 Могло быть и лучше!

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

💬 Категорически интересно Ваше мнение👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL

🎵 «Счастье вдруг в тишине постучалось в двери,
Неужель ты ко мне — верю и не верю.
Падал снег, плыл рассвет, осень моросила…
Столько лет, столько лет — где тебя носило?»
— Леонид Дербенёв.

Давно такого не было — и вот опять.
Работа с CLOB — наверное, самая опасная тема:
столько раз о неё обжигался 🔥

И вот опять совершил обидную ошибку.
Уважаемым читателям предлагаю на рассмотрение мою собственную ошибку.
Вопрос: где она происходит и, главное, почему?

DECLARE
   v_dest CLOB;
   v_src  VARCHAR2 (256);
BEGIN
   DBMS_LOB.createtemporary (v_dest, TRUE);

   DBMS_LOB.append (
      dest_lob => v_dest,
      src_lob  => '123'
   );

   DBMS_LOB.append (
      dest_lob => v_dest,
      src_lob  => v_src
   );

   IF DBMS_LOB.istemporary (v_dest) = 1
   THEN
      DBMS_LOB.freetemporary (v_dest);
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      IF DBMS_LOB.istemporary (v_dest) = 1
      THEN
         DBMS_LOB.freetemporary (v_dest);
      END IF;

      RAISE;
END;

Ошибка вроде бы незаметная…
Но именно такие мелочи в работе с CLOB чаще всего и происходят.

👍 Интересная ошибка!
👎 Черт знает что такое!

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

💬 Кто напишет комментов пачку, тот получит водокачку!👇
#Cases #SQL #Oracle #PLSQL

🎵 «Вдруг, как в сказке, скрипнула дверь
Все мне стало ясно теперь
Столько лет я спорил с судьбой
Ради этой встречи с тобой
Мерз я где-то, плыл за моря
Знаю - это было не зря
Все на свете было не зря
Не напрасно было.» — Леонид Дербенёв

Давно такого не было — и вот опять.
Разбираем мою ошибку с CLOB.
Многие читатели заметили: проблема в том, что мы шлем NULL в метод append.

Всё так и есть! Ошибка возникает здесь:

DBMS_LOB.append (
   dest_lob => v_dest,
   src_lob  => v_src
)

Причём падает всё вне зависимости от типа: будь то CLOB или VARCHAR2. Вот подробное описание «виновника» торжества:

ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 
ORA-06512: at line 29 
ORA-06512: at "SYS.DBMS_LOB", line 656 
ORA-06512: at line 12

Как это валидно разрешить?
TO_CLOB(v_src) — не поможет.

NVL(v_src, ' ') — можно, но как-то «не солидно».

Даже NVL(v_src, EMPTY_CLOB()) вызовет ошибку, если v_src имеет тип VARCHAR2.

Единственный по-моему солидный вариант:

NVL(TO_CLOB(v_src), EMPTY_CLOB())

Да! Да! Да!
EMPTY_CLOB IS NOT NULL <=> TRUE

Риторический вопрос 🤔
У меня возникает законный вопрос: почему любой входящий CLOB не может быть NULL?
Почему внутри DBMS_LOB нельзя автоматически выполнять подмену через тот же NVL?

Я, честно, такой логики не понимаю. Но имеем то, что имеем!

👍 Полезно!
👎 Черт знает что!

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

💬 За комменты деньги не берут! Пишите бесплатно!👇
#Cases #SQL #Oracle #PLSQL

🎵 «Если душевно ранен
Если с тобой беда,
Ты ведь пойдёшь не в баню,
Ты ведь пойдёшь сюда.
Здесь ты вздохнёшь счастливо,
Крякнешь и скажешь: да,
Губит людей не пиво,
Губит людей вода.» — Леонид Дербенёв

🛠 Джобы или распределение запуска джобов в рамках разных схем

Вот задача, которую я решаю на данный момент.

Есть БД, и в ней несколько схем, каждая из которых — отдельная самостоятельная сущность.
В каждой такой схеме работает отдельная организация.
Т.е., условно, 10 схем — это 10 отдельных «подбаз».

Таких серверов много, т.е. больше чем два.
Я не буду критиковать такой подход — просто примем это как данность: так исторически сложилось.

📦 Процесс архивации запускается для каждой схемы отдельным джобом.

В чём проблема: плохо будет, если 10 джобов запустятся одновременно ⚠️

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

❓ Вопрос дня:
Какие способы есть распределить нагрузку на общую БД и запускать джобы распределённо?
При этом какие-то структуры над этими «подбазами» не предполагаются.

👍 Задача стоит внимания!
👎 Чушь какая-то!

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

💬 Все Ваши комменты будут внимательно прочитаны!👇
#Cases #SQL #Oracle #PLSQL

🎵 «В жизни давно я понял,
Кроется гибель где,
В пиве никто не тонет,
Тонут всегда в воде.
Реки, моря, проливы,
Сколько от них вреда.
Губит людей не пиво,
Губит людей вода.» — Леонид Дербенёв

Результат моего анализа запуска джобов по разным схемам —
или как поскользнуться на скользком полу 🧊

Повторюсь кратко, в чём проблема:

Есть несколько схем на одной базе: от одной до ста
В каждой из этих схем сидят разные организации, т.е. каждый думает, что он сидит в отдельной базе.
Какой-то надструктуры или общей схемы нет.

Ограничения:
мы не можем создать job-координатор — он будет вынужден выйти за рамки схем

Читатель Михаил Голяков предложил использовать идею ресурсного окна или модели ресурсного распределения ⚙
Идея стоящая, но я боюсь, что буду отбирать хлеб у DBA
Такие предложения должны идти от DBA, как и реализация.
У меня есть доступ только в пределах тестовых баз.
В общем, идея хорошая, но я её отверг.

Моё решение 💡

Ставим одну общую блокировку на процесс архивации на все схемы
Т.е. один из job’ов возьмёт её первым, остальные уйдут в ошибку
Мы эту ошибку ловим и назначаем стартовое время, допустим, через 15 минут ⏱
И так рано или поздно все отработают последовательно,
но, возможно, с небольшими перерывами.

Если механизм отработал без ошибки,
то вызываем его уже на следующий день в базовое время

Базовое время и стартовое время можно сделать рандомными в пределах 20–30 минут,
чтобы несколько job’ов не ломились в одно и то же время

Демонстрационная модель для теста 🧪

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB (
    job_name          => 'JOB_WITH_RETRY',
    job_type          => 'PLSQL_BLOCK',
    job_action        => q'[
DECLARE
  v_random  NUMBER;
BEGIN
  v_random := TRUNC(DBMS_RANDOM.VALUE(1,1000));
  IF v_random > 500 THEN
    RAISE_APPLICATION_ERROR(-20201, 'error');
  END IF;

  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'JOB_WITH_RETRY',
    attribute => 'start_date',
    value     => SYSTIMESTAMP + INTERVAL '30' MINUTE
  );

EXCEPTION
  WHEN OTHERS THEN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
      name      => 'JOB_WITH_RETRY',
      attribute => 'start_date',
      value     => SYSTIMESTAMP + INTERVAL '2' MINUTE
    );
END;
]',
    start_date        => SYSTIMESTAMP + INTERVAL '20' SECOND,
    repeat_interval   => 'freq=minutely;interval=60',
    end_date          => TO_DATE(NULL),
    job_class         => 'DEFAULT_JOB_CLASS',
    enabled           => TRUE,
    auto_drop         => FALSE,
    comments          => '');

  SYS.DBMS_SCHEDULER.set_attribute(
    name      => 'JOB_WITH_RETRY',
    attribute => 'LOGGING_LEVEL',
    value     => DBMS_SCHEDULER.logging_full);

  SYS.DBMS_SCHEDULER.set_attribute(
    name      => 'JOB_WITH_RETRY',
    attribute => 'RESTARTABLE',
    value     => TRUE);

  SYS.DBMS_SCHEDULER.set_attribute_null(
    name      => 'JOB_WITH_RETRY',
    attribute => 'MAX_FAILURES');

  SYS.DBMS_SCHEDULER.set_attribute_null(
    name      => 'JOB_WITH_RETRY',
    attribute => 'MAX_RUNS');

  SYS.DBMS_SCHEDULER.set_attribute(
    name      => 'JOB_WITH_RETRY',
    attribute => 'STOP_ON_WINDOW_CLOSE',
    value     => FALSE);

  SYS.DBMS_SCHEDULER.set_attribute(
    name      => 'JOB_WITH_RETRY',
    attribute => 'JOB_PRIORITY',
    value     => 3);

  SYS.DBMS_SCHEDULER.set_attribute_null(
    name      => 'JOB_WITH_RETRY',
    attribute => 'SCHEDULE_LIMIT');

  SYS.DBMS_SCHEDULER.set_attribute(
    name      => 'JOB_WITH_RETRY',
    attribute => 'RESTART_ON_RECOVERY',
    value     => TRUE);

  SYS.DBMS_SCHEDULER.set_attribute(
    name      => 'JOB_WITH_RETRY',
    attribute => 'RESTART_ON_FAILURE',
    value     => FALSE);

  SYS.DBMS_SCHEDULER.set_attribute(
    name      => 'JOB_WITH_RETRY',
    attribute => 'STORE_OUTPUT',
    value     => TRUE);

  SYS.DBMS_SCHEDULER.enable(name => 'JOB_WITH_RETRY');
END;

❓ Как вам такая идея?

👍 Идея нравится!
👎 Я так далек от этого!

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

💬 Вы читатели золотые, а комменты бриллиантовые👇
#Cases #SQL #Oracle #PLSQL

Уважаемые читатели!

Поздравляю вас с Новым годом и Рождеством.
Любимый праздник детства — и никуда от него не деться.

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

Канал уходит на большие новогодние каникулы.
Увидимся в новом году!

🎵 «Взгляд от подбородка, я весел и суров,
А моя походка мелет пыль дворов.
Я исполнен страсти жаркого огня,
Не видала счастья — полюби меня.» — Гарик Сукачев.

📚 Серия «Оптимизация SQL-запросов».
Планы запросов из множества таблиц. Тестовая структура

Давным-давно я проводил опрос среди читателей о том, что они считают самым сложным в оптимизации запросов:

Самая существенная часть — 24% — ответила так:

«Простые планы я понимаю, но если план большой и состоит из множества строк — теряюсь».

Дело в том, что тема несложная, но объёмная. И самое главное — интуитивно понятная, т.е. в принципе каждый может логически прийти к результату.
Там нет хитрых секретов — всё предельно логично.

Проблема только в одном: с чего начать повествование так, чтобы это было интересно и доступно.

Вспомните пост про библиотекаря — многим он понравился.

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

Боже, это самый скучный и утомительный этап — разбираться в структуре (при том бесплатно)!
Но что поделаешь!? Реальные структуры публиковать бессмысленно — для большинства они непонятны, слишком много специфики.

Поэтому возьмём понятные всем объекты:
📘 книги
🏷 жанры книг
🏢 издательства
✍️ авторы
🌍 страны

А также две таблицы для связки многие-ко-многим:
— книги-жанры
— книги-авторы

CREATE TABLE authors
(
  author_id     NUMBER PRIMARY KEY,
  first_name    VARCHAR2 (100) NOT NULL,
  last_name     VARCHAR2 (100) NOT NULL,
  birthday      DATE,
  country_id    NUMBER
);

CREATE TABLE genres
(
  genre_id      NUMBER PRIMARY KEY,
  genre_name    VARCHAR2 (100) NOT NULL
);

CREATE TABLE publishers
(
  publisher_id      NUMBER PRIMARY KEY,
  publisher_name    VARCHAR2 (200) NOT NULL,
  country_id        NUMBER
);

CREATE TABLE books
(
  book_id         NUMBER PRIMARY KEY,
  title           VARCHAR2 (300) NOT NULL,
  published       DATE,
  publisher_id    NUMBER
);

CREATE TABLE countries
(
  country_id      NUMBER PRIMARY KEY,
  country_name    VARCHAR2 (100) NOT NULL
);

CREATE TABLE book_authors
(
  book_id      NUMBER NOT NULL,
  author_id    NUMBER NOT NULL
);

CREATE TABLE book_genres
(
  book_id     NUMBER NOT NULL,
  genre_id    NUMBER NOT NULL
);

Позже эти таблицы заполним рандомно тысячами или миллионами строк 📊

На этом этапе возникает два вопроса:

1️⃣ Какой минимальный набор индексов необходим для работы с такой структурой?
2️⃣ Какие запросы (чисто теоретически) могут быть сформулированы к данным?

Вот уже готовые две темы для обсуждения.

Если на первый вопрос я могу ответить самостоятельно, то со вторым без помощи не обойтись.

Проявите фантазию и сформулируйте самый сложный запрос к данным — в следующем посте разберём самый интересный вариант.
Переложим его на SQL и поизучаем план этого запроса

📌 Итак, мой пример сложного запроса:

Необходимо найти всех авторов:

у которых были книги ровно в трёх жанрах — не больше и не меньше
книги этих авторов публиковались как минимум в двух странах
имена этих авторов содержат букву «a»
фамилия автора содержит фразу «ra»
время публикации между книгами автора не превышало 10 лет

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

🔥 Челлендж: попробуйте сформулируйте запрос сложнее и интереснее к этим данным.
Пора автору канала порешать Ваши задачки, а не наоборот.

👍 Правильная дорога!
👎 Тупик!

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

💬 Ваши варианты пишете тут👇
#️⃣ #SQLOptimization

🎵 «Я курю отборный сборный табачок,
Пиджачок мой чёрный чешет ветерок.
И бульварным морем проплываю я,
Не видала горя — полюби меня.» — Гарик Сукачев.

📚 Серия «Оптимизация SQL-запросов». Планы запросов из множества таблиц. Заполнение тестовых таблиц. Часть 1

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

Перед созданием индексов заполню наши таблицы случайными (рандомными) данными. Опытные разработчики в курсе, а начинающим напомню: намного быстрее создавать индексы после заполнения таблиц, а не наоборот. ⚡
Постараемся, чтобы данных было достаточно, и время выполнения оптимального плана запроса существенно отличался от времени неоптимального.

Итак, создадим: 30 жанров, 1000 стран, 50 тыс. издательств, 1 млн авторов.

BEGIN
  INSERT /*+ APPEND */
         INTO countries (country_id, country_name)
        SELECT LEVEL AS country_id, 'COUNTRY_' || TO_CHAR (LEVEL) AS country_name
          FROM DUAL
    CONNECT BY LEVEL <= 1000;

  COMMIT;
END;
/

BEGIN
  INSERT /*+ APPEND */
         INTO genres (genre_id, genre_name)
        SELECT LEVEL AS genre_id, 'GENRE_' || TO_CHAR (LEVEL) AS genre_name
          FROM DUAL
    CONNECT BY LEVEL <= 30;

  COMMIT;
END;
/

BEGIN
  INSERT /*+ APPEND */
         INTO publishers (publisher_id, publisher_name, country_id)
        SELECT LEVEL                                 AS publisher_id,
               'PUBLISHER_' || TO_CHAR (LEVEL)       AS publisher_name,
               TRUNC (DBMS_RANDOM.VALUE (1, 1001))   AS country_id
          FROM DUAL
    CONNECT BY LEVEL <= 50000;

  COMMIT;
END;
/

BEGIN
  INSERT /*+ APPEND */
         INTO authors (author_id,
                       first_name,
                       last_name,
                       birthday,
                       country_id)
        SELECT LEVEL
                 AS author_id,
               UPPER (
                 DBMS_RANDOM.string ('A', TRUNC (DBMS_RANDOM.VALUE (1, 301))))
                 AS first_name,
               UPPER (
                 DBMS_RANDOM.string ('A', TRUNC (DBMS_RANDOM.VALUE (1, 301))))
                 AS last_name,
                 TO_DATE ('1500-01-01', 'yyyy-mm-dd')
               + TRUNC (DBMS_RANDOM.VALUE (0, 365 * 500))
                 AS birthday,
               TRUNC (DBMS_RANDOM.VALUE (1, 1001))
                 AS country_id
          FROM DUAL
    CONNECT BY LEVEL <= 1000000;

  COMMIT;
END;
/

Здесь скрипты простые. Единственное — можно добавить пояснение про хинт APPEND, который подсказывает Oracle, что не нужно искать свободные блоки, куда можно дописать строку.
То есть мы добавляем данные всегда в «конец», сразу смещая HWM.

У хинта есть особенность: он ставит блокировку на таблицу. В конкурентной среде с ним нужно быть осторожным. 🔒
Но у нас конкуренции нет!
```
Продолжение 👇

📚 Серия «Оптимизация SQL-запросов». Планы запросов из множества таблиц. Заполнение тестовых таблиц. Часть 2

Далее скрипты чуть другие. Мы используем чанки для вставки данных из-за иерархического запроса — так будет быстрее.

Также заполним 50 млн книг, примерно 125 млн. связок книг-жанров и примерно 75 млн. связкок книги-авторы.

DECLARE
  chunk_size     INTEGER := 10000;
  chunk_amount   INTEGER := 5000;
BEGIN
  FOR chunk_index IN 1 .. chunk_amount
  LOOP
    INSERT /*+ APPEND */
           INTO books (book_id,
                       title,
                       published,
                       publisher_id)
          SELECT chunk_index * chunk_size + LEVEL
                   AS book_id,
                 UPPER (
                   DBMS_RANDOM.string ('A', TRUNC (DBMS_RANDOM.VALUE (1, 51))))
                   AS title,
                   TO_DATE ('1500-01-01', 'yyyy-mm-dd')
                 + TRUNC (DBMS_RANDOM.VALUE (0, 365 * 500))
                   AS published,
                 TRUNC (DBMS_RANDOM.VALUE (1, 50001))
                   AS publisher_id
            FROM DUAL
      CONNECT BY LEVEL <= chunk_size;

    COMMIT;
  END LOOP;
END;
/

DECLARE
  chunk_size     INTEGER := 10000;
  chunk_amount   INTEGER := 5000;
BEGIN
  FOR chunk_index IN 1 .. chunk_amount
  LOOP
   <<fill_the_chunk>>
    DECLARE
      random_genre_amount   INTEGER := TRUNC (DBMS_RANDOM.VALUE (1, 5));
    BEGIN
      INSERT /*+ APPEND */
             INTO book_genres (book_id, genre_id)
        SELECT a.book_id, TRUNC (DBMS_RANDOM.VALUE (1, 31)) AS genre_id
          FROM (    SELECT chunk_index * chunk_size + LEVEL   AS book_id
                      FROM DUAL
                CONNECT BY LEVEL <= chunk_size) a
               CROSS JOIN (    SELECT NULL
                                 FROM DUAL
                           CONNECT BY LEVEL <= fill_the_chunk.random_genre_amount) b;
    END fill_the_chunk;

    COMMIT;
  END LOOP;
END;
/

DECLARE
  chunk_size     INTEGER := 10000;
  chunk_amount   INTEGER := 5000;
BEGIN
  FOR chunk_index IN 1 .. chunk_amount
  LOOP
   <<fill_the_chunk>>
    DECLARE
      random_author_amount   INTEGER := TRUNC (DBMS_RANDOM.VALUE (1, 3));
    BEGIN
      INSERT /*+ APPEND */
             INTO book_authors (book_id, author_id)
        SELECT a.book_id, TRUNC (DBMS_RANDOM.VALUE (1, 1000000)) AS author_id
          FROM (    SELECT chunk_index * chunk_size + LEVEL   AS book_id
                      FROM DUAL
                CONNECT BY LEVEL <= chunk_size) a
               CROSS JOIN (    SELECT NULL
                                 FROM DUAL
                           CONNECT BY LEVEL <= fill_the_chunk.random_author_amount) b;
    END fill_the_chunk;

    COMMIT;
  END LOOP;
END;
/

Последние два скрипта поинтереснее.
Предпоследний скрипт: для каждого чанка мы случайно вычисляем количество жанров (от 1 до 5), в которых книга «написана» (точнее — к которым она относится).
Последний скрипт: для каждого чанка мы случайно вычисляем количество авторов (от 1 до 3), которыми написаны книги из этого чанка.

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

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

👍 Идем дальше!
👎 Приехали уже!

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

💬 Ваши варианты пишете тут👇
#️⃣ #SQLOptimization

🎵 «Я иду Арбатом к мужикам в гараж,
У меня под глазом перманентный бланш,
А моя наколка — ветер и броня,
Не видала в жизни толка, полюби меня.» — Гарик Сукачев.

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

На первом этапе проектирования структуры таблиц — подход простой и естественный:
создаем индексы для PK и по FK
на основе индексов создаем констрейны.

В моем первоначальном скрипте есть неточность — мы обозначаем PK прямо в определении таблицы.
Синтаксис это позволяет, но есть проблема: имя констрейна/индекса назначается системным. Выглядит оно как SYS_C0010421.
Лучше такую неточность не допускать и явно указывать имя индекса/констрейна.

В моем конкретном случае я их переименую.

SELECT * FROM user_constraints
WHERE table_name IN ('AUTHORS', 'GENRES', 'PUBLISHERS', 'BOOKS', 'COUNTRIES')
  AND constraint_type = 'P';

ALTER TABLE authors RENAME CONSTRAINT SYS_C0010438 TO authors_pk;
ALTER INDEX SYS_C0010438 RENAME TO authors_pk;

ALTER TABLE genres RENAME CONSTRAINT SYS_C0010440 TO genres_pk;
ALTER INDEX SYS_C0010440 RENAME TO genres_pk;

ALTER TABLE publishers RENAME CONSTRAINT SYS_C0010442 TO publishers_pk;
ALTER INDEX SYS_C0010442 RENAME TO publishers_pk;

ALTER TABLE books RENAME CONSTRAINT SYS_C0010444 TO books_pk;
ALTER INDEX SYS_C0010444 RENAME TO books_pk;

ALTER TABLE countries RENAME CONSTRAINT SYS_C0010446 TO countries_pk;
ALTER INDEX SYS_C0010446 RENAME TO countries_pk;

Далее создадим индексы для FK:

CREATE INDEX countries_fk01
  ON authors (country_id);

CREATE INDEX publishers_fk01
  ON publishers (country_id);

CREATE INDEX books_fk01
  ON books (publisher_id);

CREATE UNIQUE INDEX book_authors_u01
  ON book_authors (book_id, author_id);

CREATE INDEX book_authors_fk01
  ON book_authors (author_id);

CREATE UNIQUE INDEX book_genres_u01
  ON book_genres (book_id, genre_id);

CREATE INDEX book_genres_fk01
  ON book_genres (genre_id);

Уникальные индексы тут нужны для 2 вещей:

Они обеспечивают поддержку для FK.
Они обеспечивают целостность данных (ведь не может автор значиться автором у книги дважды)

Но на этом этапе выяснилось, что как раз уникальность записей таблиц book_authors, book_genres нарушена
Это издержки рандомной вставки. В скриптах на заполнение таблиц мы вставляли author_id, genre_id рандомно — и появились дубли, которые требуется почистить.
Продолжение 👇