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

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

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

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

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

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

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

Шаг 1️⃣

План читаем из глубины наверх
Верхние строки: SORT GROUP BY (Id 1), VIEW (Id 19), FILTER (Id 27).
Смотрим нижний — FILTER и начинаем читать дальше и видим, что самое первое действие — чтение таблицы authors: TABLE ACCESS FULL (Id 30).

Видим * — значит есть ограничение записей
* 30 - filter("A"."FIRST_NAME" LIKE '%R%' AND "A"."LAST_NAME" LIKE '%RA%')

Всё логично! У нас нет подходящего индекса. (Вот, если бы LIKE 'R%' или LIKE 'RA%' — такое выражение позволяет использовать индекс)

Иными словами, наш “библиотекарь” начинает сначала бегать по полному списку авторов и выписывать тех, кто подходит по условию

Шаг 2️⃣

Оптимизатор знает, что в нашем WHERE три больших условия:
а) Книги ровно в 29 жанрах <=> 29 = (SELECT COUNT(DISTINCT bg.genre_id) ... )
б) Минимум 2 страны <=> 2 <= (SELECT COUNT(p.country_id) ... )
в) Время публикации между книгами автора не превышало 17592 дней <=> 17592 <= (SELECT MAX( LEAD(published)-published ) ... )

и он строит квази-таблицу:
Id 29: JOIN FILTER CREATE :BF0000

Почему он так делает? => “жираф большой — ему видней”
После мы увидим, что не сильно это ему помогло.

а) COUNT(DISTINCT bg.genre_id)
В плане:
Id 48 HASH GROUP BY
Id 49–52 NESTED LOOPS BOOK_AUTHORS_FK01 + BOOK_GENRES_U01

“Библиотекарь” по каждому автору берет книги через BOOK_AUTHORS_FK01 (Id 51).
По каждой книге идёт в таблицу-связку жанры-книги BOOK_GENRES по индексу BOOK_GENRES_U01 (Id 52).
Потом HASH GROUP BY считает distinct жанры.
Никаких чудес! Вручную мы бы делали то же самое.

б) COUNT(p.country_id)
В плане:
Id 53 SORT AGGREGATE
Id 54–62 NESTED LOOPS BOOK_AUTHORS → BOOKS → PUBLISHERS

Примерно то же самое, только на этот раз идем в таблицу издательств.
По BOOK_AUTHORS_FK01 смотрим книги автора (Id 58),
смотрим BOOKS по PK (Id 60),
смотрим PUBLISHERS по PK (Id 61),
агрегируем COUNT.
Тоже всё логично!

в) MAX( LEAD(published)-published )
В плане:
Id 63 SORT AGGREGATE
Id 64 VIEW
Id 65 WINDOW SORT
Id 66–71 NESTED LOOPS BOOK_AUTHORS → BOOKS

Смотрим все published книг автора,
считаем LEAD(published) - published (в предикатах видно, что Oracle переписал LEAD в свою внутреннюю форму),
берём MAX,
сравниваем с 17592.
И тут тоже без выкрутасов!

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

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

Где именно результат собирается?
Внутри Id 31–32:
Id 32: SORT GROUP BY
Id 31: VIEW VW_SSQ_1

Логика понятна: по каждому автору пройтись, проверить условия, посчитать и сгруппировать по всему списку.
Если квази-таблица получается слишком большой, то ORACLE всегда имеет возможность промежуточные данные положить в табличное пространство TEMP.

Общая логика выборки авторов проста. Детали могут быть не ясны.

Для примера разберем кусок (Id: 52-62)

 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

Начинаем Id: 58.
Здесь мы уже имеем author_id и по индексу BOOK_AUTHORS_FK01 методом INDEX RANGE SCAN получаем записи *58 - access("BA"."AUTHOR_ID"=:B1).
Мы из индекса BOOK_AUTHORS_FK01 получили ROWID нужных нам строк, теперь по TABLE ACCESS BY INDEX ROWID получаем уже записи из таблицы BOOK_AUTHORS (Id: 57).
Вот мы знаем и book_id, и в Id: 60 по INDEX UNIQUE SCAN вытаскиваем данные по books.
Две эти таблицы соединили по NESTED LOOPS, т.е. по каждому автору из начального списка проделываем все те же самые действия.
Получив записи книг => есть publisher_id => Id: 61 мы получаем записи из PUBLISHERS.
Опять последовательно через NESTED LOOPS и через TABLE ACCESS BY INDEX ROWID.
Всё стандартно.

Шаг 4️⃣

На 3 шаге мы получили VW_SSQ_1 и это следствие переписывания/разворачивания скалярных подзапросов:
оптимизатор пытается превратить “подзапрос на каждого автора” в один большой проход с группировкой.

Как это приклеивается обратно к AUTHORS?
После того как посчитали агрегаты “где-то там”, Oracle делает:
Id 28: HASH JOIN OUTER *28: ITEM_1(+) = A.AUTHOR_ID

То есть результат из VW_SSQ_1 подцепляется к строкам AUTHORS.
Т.е. наш библиотекарь во время поисков всех авторов параллельно аккуратно собирал и агрегировал информацию по книгам, издательствам — и всё что нужно — в огромную амбарную книгу.

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

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

Шаг 5️⃣

Для каждого автора, который подходит по условиям, мы вычисляем уже значение полей.
С полями author_id, first_name, last_name — здесь всё очевидно.

а) Как находится «2-я книга автора» для списка соавторов <=> second_book_coauthors_list
В плане ему соответствует связка:
Id 11 VIEW
Id 12 WINDOW SORT PUSHED RANK
Id 13–18 NESTED LOOPS + BOOK_AUTHORS_FK01 + BOOKS_PK

Что происходит:
По текущему автору :B1 (корреляция) берутся его строки в BOOK_AUTHORS через индекс:
Id 16 INDEX RANGE SCAN BOOK_AUTHORS_FK01 (ba_1.author_id = :B1)
Id 15 TABLE ACCESS BY INDEX ROWID BATCHED BOOK_AUTHORS

Для каждой строки через BOOKS_PK поднимается книга и её published:
Id 17 INDEX UNIQUE SCAN BOOKS_PK
Id 18 TABLE ACCESS BY INDEX ROWID BOOKS

Затем Id 12 WINDOW SORT PUSHED RANK сортирует по published и считает ROW_NUMBER().
В предикатах видно оптимизацию: ROW_NUMBER() <= 2 (* 12) — Oracle понимает, что нужна только 2-я строка и не сортирует “всю жизнь”, а ограничивает верхнюю часть.
На выходе VIEW (Id 11) остаётся ровно строка book_id второй книги (* 11).

Далее нужно собрать соавторов для этой 2-й книги.
Это верхняя половина ветки:
Id 2 FILTER
Id 3–10 NESTED LOOPS (BOOK_AUTHORS → BOOK_AUTHORS → AUTHORS)

Расклад:
Id 7 INDEX RANGE SCAN BOOK_AUTHORS_FK01: берём все строки ba данного автора (его книги).
Id 2 FILTER: пропускаем только ту строку ba, где ba.book_id = (book_id второй книги) — это как раз твой коррелированный фильтр AND ba.book_id = (SELECT ... rownumber=2).
Для найденной книги делаем self-join на BOOK_AUTHORS:
Id 8 INDEX RANGE SCAN BOOK_AUTHORS_U01 по ba1.book_id = ba.book_id, плюс фильтр ba1.author_id <> :B1 (* 8).
Потом автора-соавтора поднимаем по PK:
Id 9 INDEX UNIQUE SCAN AUTHORS_PK + фильтр (дублирующий) <> :B1.
Id 10 TABLE ACCESS BY INDEX ROWID AUTHORS чтобы взять имя/фамилию.
Дальше уже считается LISTAGG(DISTINCT ...) внутри этого scalar subquery (в плане он “растворён” внутри ветки).

Итог: столбец second_book_coauthors_list считается для каждого автора-кандидата и делает:
найти 2-ю книгу (окно + сортировка по published, но ограниченная до 2),
по этой книге пробежать соавторов.

б) Как находится «1-я книга» (title) в SELECT
Это first_book_published.
В плане:
Id 19 VIEW
Id 20 WINDOW SORT PUSHED RANK
Id 21–26 NESTED LOOPS BOOK_AUTHORS_FK01 → BOOKS_PK

Это то же самое, что «2-я книга автора», только ROW_NUMBER() = 1:
*20: ROW_NUMBER() <= 1
*19: book_published_rownumber = 1
То есть для каждого автора быстро выбирается самая ранняя книга.

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

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

в) Как находится «5-я книга» <=> fifth_book_genres_list

В плане видно два разных “куска”:

Дорогая общая агрегация по жанрам.
Самая тяжёлая часть:
Id 28 HASH JOIN OUTER
Id 30 TABLE ACCESS FULL AUTHORS + фильтр LIKE (*30)

Id 31 VIEW VW_SSQ_1
Id 32 SORT GROUP BY
Id 34 HASH JOIN
Id 37 HASH JOIN
Id 38 TABLE ACCESS FULL BOOK_AUTHORS (44,719,984)
Id 39 TABLE ACCESS FULL BOOK_GENRES (72,311,371)
Id 35 TABLE ACCESS FULL GENRES (30)

Что это значит по-человечески:
Oracle берёт отфильтрованный список авторов (Id 30) и строит FILTER :BF0000.
Затем на огромных BOOK_AUTHORS и BOOK_GENRES делает HASH JOIN (Id 37), отсекая лишнее через FILTER (Id 36) — чтобы не тащить жанры для авторов, которые не проходят LIKE.
Потом подмешивает GENRES (Id 34) и делает SORT GROUP BY (Id 32) — то есть агрегирует что-то по автору
(скорее всего подготовка данных для твоего LISTAGG DISTINCT g.genre_name по 5-й книге или для части условий).
Почему это важно: здесь полные сканы двух больших таблиц и огромный hash join — именно это самый “дорогой” шаг.

Поиск 5-й книги через окно (ROW_NUMBER = 5)
Отдельно есть знакомая ветка:
Id 40 VIEW
Id 41 WINDOW SORT PUSHED RANK
Id 42–47 NESTED LOOPS BOOK_AUTHORS_FK01 → BOOKS_PK

Ограничения выборки:
*41: ROW_NUMBER() <= 5
*40: rownumber = 5
*33: ba.book_id = (… rownumber=5) — это связь с внешним ba в subquery.
То есть механизм выбора 5-й книги такой же, как для 1-й/2-й, но берёт топ-5 по published.

Дойдя до этого места, я понимаю, почему таких разборов мало в инете. То что глазами пройтись 5 минут, для разбора на бумагу — это более 4 дней по утрам до завтрака.

Выводы

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

Годится ли такой разбор или нет — решать Вам.

Если есть затруднения с материалом, вспомните то, что уже обсуждали
✅ Советы для новичков. https://t.me/db_developers_channel/155
✅ План запроса как таковой. https://t.me/db_developers_channel/171
✅ План запроса из одной таблицы. https://t.me/db_developers_channel/182
✅ Чтение плана запроса при соединении двух и более таблиц. https://t.me/db_developers_channel/199
✅ HASH-соединения таблиц. https://t.me/db_developers_channel/205
✅ MERGE-соединения таблиц. https://t.me/db_developers_channel/210
✅ Тестовая структура. https://t.me/db_developers_channel/250

👍 Годится!
👎 Не годится!

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

💬 Жду Ваших эмоций👇
#️⃣ #SQLOptimization