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

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

🧠 Концепт архитектуры архивирование таблиц (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 рандомно — и появились дубли, которые требуется почистить.
Продолжение 👇

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

Тему выявления дублей мы проходили, и уже приятно ссылаться на опубликованный материал.

BEGIN
  DELETE FROM book_authors ba
   WHERE ba.ROWID IN
           (SELECT sub.book_author_rowid
              FROM (SELECT t.ROWID AS book_author_rowid,
                           ROW_NUMBER()
                             OVER (PARTITION BY book_id, author_id
                                   ORDER BY book_id) AS rn
                      FROM book_authors t) sub
             WHERE rn > 1);

  COMMIT;
END;
/
BEGIN
  DELETE FROM book_genres bg
   WHERE bg.ROWID IN
           (SELECT sub.book_genre_rowid
              FROM (SELECT t.ROWID AS book_genre_rowid,
                           ROW_NUMBER()
                             OVER (PARTITION BY book_id, genre_id
                                   ORDER BY book_id) AS rn
                      FROM book_genres t) sub
             WHERE rn > 1);

  COMMIT;
END;
/

После того как дубли почистили, уникальные индексы создались без проблем.
Далее уже на существующих индексах мы можем создать констрейны на FK.

ALTER TABLE authors
  ADD CONSTRAINT authors_fk01 FOREIGN KEY (country_id) REFERENCES countries (country_id);

ALTER TABLE publishers
  ADD CONSTRAINT publishers_fk01 FOREIGN KEY (country_id) REFERENCES countries (country_id);

ALTER TABLE books
  ADD CONSTRAINT books_fk01 FOREIGN KEY (publisher_id) REFERENCES publishers (publisher_id);

ALTER TABLE book_authors
  ADD CONSTRAINT book_authors_fk01 FOREIGN KEY (book_id) REFERENCES books (book_id);

ALTER TABLE book_authors
  ADD CONSTRAINT book_authors_fk02 FOREIGN KEY (author_id) REFERENCES authors (author_id);

ALTER TABLE book_genres
  ADD CONSTRAINT book_genres_fk01 FOREIGN KEY (book_id) REFERENCES books (book_id);

ALTER TABLE book_genres
  ADD CONSTRAINT book_genres_fk02 FOREIGN KEY (genre_id) REFERENCES genres (genre_id);

⚠️ Здесь нужно обговорить важную вещь.
Есть общее правило: если устанавливаете FK, то перед этим нужно установить индекс.

При удалении записи из таблицы, на которую установлен FK, любая СУБД должна проверить, что нет таких значений в FK.
Я думаю, не надо объяснять, что будет, если из мелкого справочника кто-то решит удалить запись, в то время как на эту таблицу установлен FK к таблице с 10 млрд строк без индекса на FK 💥

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

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

👍 Идем правильно дорогой по Арбату!
👎 Свернули не туда!

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

💬 Ваши комменты пишете тут👇
#️⃣ #SQLOptimization

🎵 «Нет, я не плачу и не рыдаю
На все вопросы я открыто отвечаю
Что наша жизнь — игра, и кто ж тому виной,
Что я увлёкся этою игрой.» — Юлий Ким.

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

Сегодня “формально” напишем запрос так, как его может написать «твердый» джуниор.
Напомню требования к запросу 👇

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

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

Так получилось, что рандомные данные не подходят под текущие требования.
Поэтому я требования чутка изменил:
а) время публикации между книгами автора не превышало 17592 дней
б) у которых были книги ровно в 29 жанрах — не больше и не меньше

Итак, у меня получился вот такой запрос

SELECT a.author_id,
       a.first_name,
       a.last_name,
       (SELECT LISTAGG (DISTINCT a1.first_name || ' ' || a1.last_name, ', ')
                 WITHIN GROUP (ORDER BY ba.author_id)  AS second_book_coauthors_list
          FROM book_authors  ba
               INNER JOIN book_authors ba1 ON (ba1.book_id = ba.book_id)
               INNER JOIN authors a1 ON (a1.author_id = ba1.author_id)
         WHERE     ba.author_id = a.author_id
               AND a1.author_id != a.author_id
               AND ba.book_id =
                   (SELECT ba_2.book_id
                      FROM (SELECT b_1.book_id,
                                   ROW_NUMBER ()
                                     OVER (ORDER BY b_1.published)  AS book_published_rownumber
                              FROM book_authors  ba_1
                                   INNER JOIN books b_1
                                     ON (b_1.book_id = ba_1.book_id)
                            WHERE ba_1.author_id = ba.author_id) ba_2
                     WHERE ba_2.book_published_rownumber = 2))
         AS second_book_coauthors_list,
       (SELECT LISTAGG (DISTINCT g.genre_name, ', ')
                 WITHIN GROUP (ORDER BY g.genre_id)  AS fifth_book_genres_list
          FROM book_authors ba
          INNER JOIN book_genres bg ON (bg.book_id = ba.book_id)
          INNER JOIN genres g ON (g.genre_id = bg.genre_id)
         WHERE     ba.author_id = a.author_id
               AND ba.book_id =
                   (SELECT ba_2.book_id
                      FROM (SELECT b_1.book_id,
                                   ROW_NUMBER ()
                                     OVER (ORDER BY b_1.published)  AS book_published_rownumber
                              FROM book_authors  ba_1
                                   INNER JOIN books b_1
                                     ON (b_1.book_id = ba_1.book_id)
                            WHERE ba_1.author_id = ba.author_id) ba_2
                     WHERE ba_2.book_published_rownumber = 5))
         AS fifth_book_genres_list,
      (SELECT ba_2.title
                      FROM (SELECT b_1.title,
                                   ROW_NUMBER ()
                                     OVER (ORDER BY b_1.published)  AS book_published_rownumber
                              FROM book_authors  ba_1
                                   INNER JOIN books b_1
                                     ON (b_1.book_id = ba_1.book_id)
                            WHERE ba_1.author_id = a.author_id) ba_2
                     WHERE ba_2.book_published_rownumber = 1)
         AS first_book_published

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

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

  FROM authors a
 WHERE     1 = 1
       -- AND a.author_id = 41
       AND a.first_name LIKE '%R%'    -- имена этих авторов содержат букву «a»
       AND a.last_name LIKE '%RA%'    -- фамилия автора содержит фразу «ra»
       -- книги ровно в 29 жанрах — не больше и не меньше
       AND 29 =
           (SELECT COUNT (DISTINCT bg.genre_id)   AS author_book_genre_amount
              FROM book_authors  ba
                   INNER JOIN book_genres bg ON (ba.book_id = bg.book_id)
             WHERE ba.author_id = a.author_id)
       -- книги этих авторов публиковались как минимум в двух странах
       AND 2 <=
           (SELECT COUNT (p.country_id)   AS author_book_country_amount
              FROM book_authors  ba
                   INNER JOIN books b ON (b.book_id = ba.book_id)
                   INNER JOIN publishers p
                     ON (b.publisher_id = p.publisher_id)
             WHERE ba.author_id = a.author_id)
       -- время публикации между книгами автора не превышало 17592 дней
       AND 17592 >=
           (SELECT MAX (b.between_published_period)  AS max_between_published_period
              FROM (SELECT   LEAD (b.published) OVER (ORDER BY b.published)
                           - b.published  AS between_published_period
                      FROM book_authors  ba
                           INNER JOIN books b ON (ba.book_id = b.book_id)
                     WHERE ba.author_id = a.author_id) b);

Он написан формально — и потому неоптимально.
Стоит ли разбирать план запроса для такого запроса? Я бы не стал… но сомневаюсь. Возможно, кому-то это будет интересно.

Здесь логика простая: даже без плана видно, что запрос считывает одну и ту же информацию несколько раз — поэтому он не оптимален.
Никакие индексы и хинты не спасут: начинать надо именно с переоформления запроса. 🛠️

Основные проблемы:
а) несколько раз читаем таблицы book_authors, books по одним и тем же авторам
б) несколько раз ранжируем книги авторов

В общем, есть чем заняться!

Если есть желание — можете оптимизировать запрос самостоятельно.
А я в следующем посте этот запрос переоформлю и расскажу логику перестройки.
Также надеюсь, что найдете ошибки и неточности)))

👍 Хороший запрос поднимает настроение!
👎 Глаза бы не видели эти запросы!

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

💬 Ваши комменты пишете тут👇
#️⃣ #SQLOptimization

🎵 «Как жили мы борясь,
И смерти не боясь,
Так и отныне жить
Тебе и мне:
В небесной вышине
И в горной тишине,
В морской волне
И в яростном огне.» — Алексей Дидуров

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

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

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

Вот усеченный план запроса:

 0 |SELECT STATEMENT
 1 | SORT GROUP BY
*2 |  FILTER
 3 |   NESTED LOOPS
 4 |    NESTED LOOPS
 5 |     NESTED LOOPS
 6 |      TABLE ACCESS BY INDEX ROWID BATC |BOOK_AUTHORS
*7 |       INDEX RANGE SCAN                |BOOK_AUTHORS_FK01
*8 |      INDEX RANGE SCAN                 |BOOK_AUTHORS_U01
*9 |     INDEX UNIQUE SCAN                 |AUTHORS_PK
 10|    TABLE ACCESS BY INDEX ROWID        |AUTHORS
*11|   VIEW
*12|    WINDOW SORT PUSHED RANK
 13|     NESTED LOOPS
 14|      NESTED LOOPS
 15|       TABLE ACCESS BY INDEX ROWID BATC|BOOK_AUTHORS
*16|        INDEX RANGE SCAN               |BOOK_AUTHORS_FK01
*17|       INDEX UNIQUE SCAN               |BOOKS_PK
 18|      TABLE ACCESS BY INDEX ROWID      |BOOKS
*19| VIEW
*20|  WINDOW SORT PUSHED RANK
 21|   NESTED LOOPS
 22|    NESTED LOOPS
 23|     TABLE ACCESS BY INDEX ROWID BATC  |BOOK_AUTHORS
*24|      INDEX RANGE SCAN                 |BOOK_AUTHORS_FK01
*25|     INDEX UNIQUE SCAN                 |BOOKS_PK
 26|    TABLE ACCESS BY INDEX ROWID        |BOOKS
*27| FILTER
*28|  HASH JOIN OUTER
 29|   JOIN FILTER CREATE                  |:BF0000
*30|    TABLE ACCESS FULL                  |AUTHORS
 31|   VIEW                                |VW_SSQ_1
 32|    SORT GROUP BY
*33|     FILTER
*34|      HASH JOIN
 35|       TABLE ACCESS FULL               |GENRES
 36|       JOIN FILTER USE                 |:BF0000
*37|        HASH JOIN
 38|         TABLE ACCESS FULL             |BOOK_AUTHORS
 39|         TABLE ACCESS FULL             |BOOK_GENRES
*40|      VIEW
*41|       WINDOW SORT PUSHED RANK
 42|        NESTED LOOPS
 43|         NESTED LOOPS
 44|          TABLE ACCESS BY INDEX ROWID  |BOOK_AUTHORS
*45|           INDEX RANGE SCAN            |BOOK_AUTHORS_FK01
*46|          INDEX UNIQUE SCAN            |BOOKS_PK
 47|         TABLE ACCESS BY INDEX ROWID   |BOOKS
 48| HASH GROUP BY
 49|   NESTED LOOPS
 50|    TABLE ACCESS BY INDEX ROWID BATC   |BOOK_AUTHORS
*51|     INDEX RANGE SCAN                  |BOOK_AUTHORS_FK01
*52|    INDEX RANGE SCAN                   |BOOK_GENRES_U01
 53|  SORT AGGREGATE
 54|   NESTED LOOPS
 55|    NESTED LOOPS
 56|     NESTED LOOPS
 57|      TABLE ACCESS BY INDEX ROWID BATC |BOOK_AUTHORS
*58|       INDEX RANGE SCAN                |BOOK_AUTHORS_FK01
 59|      TABLE ACCESS BY INDEX ROWID      |BOOKS
*60|       INDEX UNIQUE SCAN               |BOOKS_PK
*61|     INDEX UNIQUE SCAN                 |PUBLISHERS_PK
 62|    TABLE ACCESS BY INDEX ROWID        |PUBLISHERS
 63|  SORT AGGREGATE
 64|   VIEW
 65|    WINDOW SORT
 66|     NESTED LOOPS
 67|      NESTED LOOPS
 68|       TABLE ACCESS BY INDEX ROWID BATC|BOOK_AUTHORS
*69|        INDEX RANGE SCAN               |BOOK_AUTHORS_FK01
*70|       INDEX UNIQUE SCAN               |BOOKS_PK
 71|      TABLE ACCESS BY INDEX ROWID      |BOOKS

Полный план в приложении.

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