📚 Серия «Оптимизация 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

🎵 «И согласитесь, какая прелесть -
Мгновенно в яблочко попасть, почти не целясь,
Орлиный взор, напор, изящный поворот,
И прямо в руки запретный плод.». — Юлий Ким.

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

В равной борьбе победили те, кому оптимизация нравится больше. Ну что, слушаюсь и повинуюсь!

Берём изначальный запрос (https://t.me/db_developers_channel/256) и разбор плана выполнения (https://t.me/db_developers_channel/259) и видим, что по списку книг по одним и тем же авторам мы пробегаем несколько раз.
При этом мы несколько раз используем ранжирование: первая книга, вторая книга, пятая книга.

Вот именно это и значит «прооптимизировать запрос».
Не надо одну и ту же работу делать несколько раз.

Логично пробежать по данным один раз, проранжировать и запомнить это множество.
Поскольку список авторов будет небольшим при условии
a.first_name LIKE '%R%' AND a.last_name LIKE '%RA%',
мы его тоже предварительно сохраним.

Итак, используя конструкцию WITH, создаём квази-таблицы selected_authors, ranged_books.
Далее эти квази-таблицы используем вместо изначальных подзапросов.

Вот мой вариант запроса

WITH
selected_authors
AS
(SELECT /*+ MATERIALIZE */ a.author_id, a.first_name, a.last_name
 FROM authors a
 WHERE
  a.first_name LIKE '%R%' -- имена этих авторов содержат букву «a»
  AND a.last_name LIKE '%RA%' -- фамилия автора содержит фразу «ra»
),
ranged_books
AS
(SELECT /*+ MATERIALIZE LEADING (sa ba b) USE_NL (sa ba b)*/
  ba.author_id,
  b.book_id,
  b.title,
  b.published,
  b.publisher_id,
  ROW_NUMBER () OVER (PARTITION BY ba.author_id ORDER BY b.published) AS row_number_book
 FROM selected_authors  sa
 INNER JOIN book_authors ba ON (ba.author_id = sa.author_id)
 INNER JOIN books b ON (b.book_id = ba.book_id))
SELECT
  sa.author_id,
  sa.first_name,
  sa.last_name,
  (SELECT /*+ LEADING (rb ba1 a1) USE_NL (rb ba1 a1)*/
    LISTAGG (DISTINCT a1.first_name || ' ' || a1.last_name, ', ') WITHIN GROUP (ORDER BY a1.author_id) AS second_book_coauthors_list
    FROM ranged_books  rb
         INNER JOIN book_authors ba1 ON (ba1.book_id = rb.book_id)
         INNER JOIN authors a1 ON (a1.author_id = ba1.author_id)
   WHERE     rb.author_id = sa.author_id
         AND a1.author_id != sa.author_id
         AND rb.row_number_book = 2) AS second_book_coauthors_list,
  (SELECT /*+ LEADING (rb bg g) USE_NL (rb bg g)*/
         LISTAGG (DISTINCT g.genre_name, ', ') WITHIN GROUP (ORDER BY g.genre_id)  AS fifth_book_genres_list
    FROM ranged_books  rb
         INNER JOIN book_genres bg ON (bg.book_id = rb.book_id)
         INNER JOIN genres g ON (g.genre_id = bg.genre_id)
   WHERE rb.author_id = sa.author_id AND rb.row_number_book = 5) AS fifth_book_genres_list,
  (SELECT rb.title
    FROM ranged_books rb
   WHERE rb.author_id = sa.author_id AND rb.row_number_book = 1)AS first_book_published
FROM selected_authors sa
WHERE
  -- книги ровно в 29 жанрах — не больше и не меньше
  29 =
 (SELECT /*+ LEADING (rg bg) USE_NL (rb bg)*/
    COUNT (DISTINCT bg.genre_id)   AS author_book_genre_amount
  FROM ranged_books  rg
    INNER JOIN book_genres bg ON (bg.book_id = rg.book_id)
  WHERE rg.author_id = sa.author_id)
  -- книги этих авторов публиковались как минимум в двух странах
  AND 2 <=
  (SELECT COUNT (p.country_id)   AS author_book_country_amount
   FROM ranged_books  rb
   INNER JOIN publishers p ON (p.publisher_id = rb.publisher_id)
   WHERE rb.author_id = sa.author_id)
  -- время публикации между книгами автора не превышало 17592 дней
  AND 17592 >=
  (SELECT MAX (rg_1.between_published_period)  AS max_between_published_period
   FROM (SELECT LEAD (rb.published) OVER (ORDER BY rb.published) - rb.published  AS between_published_period
         FROM ranged_books rb
          WHERE rb.author_id = sa.author_id) 
   rg_1);

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

📚 Серия «Оптимизация SQL-запросов». Планы запросов из множества таблиц. Оптимизация сложного запроса. Часть 2
Пояснение
1️⃣ Далее смотрим план и видим, что Oracle «глупит», так как изначально не знает, сколько таких авторов будет.
Он предполагает, что авторов много, и везде, где можно, строит HASH JOIN.

Но мы-то знаем, что авторов немного.
Поэтому вместо HASH JOIN меняем тип соединения на NESTED LOOP с помощью хинтов USE_NL и LEADING.
Как говорится, кашу маслом не испортишь.

2️⃣ Квази-таблицы selected_authors, ranged_books Oracle и так материализует автоматически,
но здесь принципиально не доверять ему полностью.
Я рекомендую в таких случаях добавлять хинт MATERIALIZE.
Хуже не будет.

Я спросил у терминатора 🤖, может ли он прооптимизировать запрос.
Да, может — и выдал интересный вариант.

WITH author_books AS (
SELECT
  ba.author_id,
  b.book_id,
  b.title,
  b.published,
  b.publisher_id,
  ROW_NUMBER() OVER (PARTITION BY ba.author_id ORDER BY b.published) AS rn
FROM book_authors ba
JOIN books b ON b.book_id = ba.book_id
),
book_gaps AS (
SELECT
  author_id,
  MAX(next_published - published) AS max_gap
FROM (
  SELECT
    author_id,
  published,
  LEAD(published) OVER (PARTITION BY author_id ORDER BY published) AS next_published
  FROM author_books)
GROUP BY author_id
),
author_genres AS (
SELECT ba.author_id,
  COUNT(DISTINCT bg.genre_id) AS genre_cnt
FROM book_authors ba
JOIN book_genres bg ON bg.book_id = ba.book_id
GROUP BY ba.author_id
),
author_countries AS (
SELECT ba.author_id,
  COUNT(DISTINCT p.country_id) AS country_cnt
FROM book_authors ba
JOIN books b ON b.book_id = ba.book_id
JOIN publishers p ON p.publisher_id = b.publisher_id
GROUP BY ba.author_id
),
second_book_coauthors AS (
SELECT ab.author_id,
  LISTAGG(DISTINCT a1.first_name || ' ' || a1.last_name, ', ') WITHIN GROUP (ORDER BY a1.author_id) AS coauthors
FROM author_books ab
JOIN book_authors ba2 ON ba2.book_id = ab.book_id
JOIN authors a1 ON a1.author_id = ba2.author_id
WHERE ab.rn = 2
  AND a1.author_id != ab.author_id
GROUP BY ab.author_id
),
fifth_book_genres AS (
SELECT ab.author_id,
  LISTAGG(DISTINCT g.genre_name, ', ') WITHIN GROUP (ORDER BY g.genre_id) AS genres
FROM author_books ab
JOIN book_genres bg ON bg.book_id = ab.book_id
JOIN genres g ON g.genre_id = bg.genre_id
WHERE ab.rn = 5
GROUP BY ab.author_id
)
SELECT
  a.author_id,
  a.first_name,
  a.last_name,
  sbc.coauthors        AS second_book_coauthors_list,
  fbg.genres           AS fifth_book_genres_list,
  fb.title             AS first_book_published
FROM authors a
JOIN author_genres ag ON ag.author_id = a.author_id
JOIN author_countries ac ON ac.author_id = a.author_id
JOIN book_gaps bgap ON bgap.author_id = a.author_id
LEFT JOIN second_book_coauthors sbc ON sbc.author_id = a.author_id
LEFT JOIN fifth_book_genres fbg ON fbg.author_id = a.author_id
LEFT JOIN author_books fb ON fb.author_id = a.author_id AND fb.rn = 1
 WHERE 
  a.first_name LIKE '%R%'
  AND a.last_name  LIKE '%RA%'
  AND ag.genre_cnt = 29
  AND ac.country_cnt >= 2
  AND bgap.max_gap <= 17592;

Дальнейшая проверка покажет, кто умнее.
Но есть вещи, которые я считаю подозрительными.

1️⃣ Он строит квази-таблицу author_books на исходных данных,
не ограничивая её уже выбранными авторами.
Возможно, это и правильно, если под условие попадает 10% и более авторов.
В нашем случае — около 2%.
Выглядит подозрительно.

2️⃣ Он строит отдельные квази-таблицы под каждый подзапрос.
Oracle, по идее, может их материализовать.
Но зачем так делать?
Возможно — для удобства анализа и чтения.

❓ А вам какой вариант больше нравится?
Может быть, вы предложите свой?

👍 Продолжаем!
👎 Стоп. Приехали!

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

💬 Комменты обязательны👇
#️⃣ #SQLOptimization

✨ «Капусточка — дело хорошее, но нужно держать и мясные закуски в доме».
— из к/ф «Ширли-мырли»

Задача о лимите пользовательских запросов.

Для любителей хороших технических задач

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

Т.е. фирма «Рога и копыта» может одновременно выполнять 30 запросов, другая — 100 и т.д.

Если непонятно, можно провести аналогию с магазином Ашан (это наша организация).
В наличии 100 касс — это и есть наше число лимитов.
Покупатели — это запросы к базе.
Если нет свободных касс, покупатели встают в очередь, пока касса не освободится.

В нашем случае мы должны выбросить специальный exception ⚠️

Конечно, это сделано не от хорошей жизни, но как техническая задача — весьма интересная.

Требования:
1️⃣ Чтобы было надёжно, т.е. количество запросов не выходило за лимит
2️⃣ Чтобы не было взаимовлияния одних сессий на другие. Не должно быть общих блокировок
3️⃣ Чтобы всё работало быстро, без деградации системы

👍 Отличная задача, мне бы такую!
👎 Черт знает что такое!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.
💬 Ваши варианты решения и все что хотите туда👇
#Cases #SQL #Oracle #PLSQL

🎵 «Дай знать мне
Где друзья, а где враги
И от морщин меня убереги
Не дай пресытиться любимым делом
Не дай отяжелеть душой и телом». — Кайсын Кулиев.

🛠️ Один из вариантов реализации. Часть. 1

Ух! Сколько эмоций вызывает просто техническая задача.
Я бы сказал — справедливого гнева и яростного негодования.

Всё так!
Я не претендую на универсальность: каждый принимает решения в меру своих знаний и своей испорченности.

Значит так — это решение технической задачи и не более того.
Оно не может применяться везде и всегда.
Это лишь повод вас расшевелить и вызвать эмоции.
И знаете что? — я своего добился.

💡 Идея простая

Каждый активный запрос = один слот в таблице.
Есть слот — работаем.
Нет слота — получаем exception ⚠️

🗄️ Таблица слотов

CREATE TABLE user_session_request_limit_slots
(
  slot NUMBER NOT NULL,
  created TIMESTAMP(6) WITH TIME ZONE NOT NULL
)
PCTFREE 0;
CREATE UNIQUE INDEX USER_SESSION_REQUEST_LIMIT_SLOTS_U01
  ON user_session_request_limit_slots (slot);
CREATE INDEX USER_SESSION_REQUEST_LIMIT_SLOTS_I01
  ON user_session_request_limit_slots (created);

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

🌍 Глобальные исключения

CREATE OR REPLACE PACKAGE SESSION_REQUEST_GLOBALS
IS
  user_session_request_exceeded_limit_code CONSTANT INTEGER := -20211;

  user_session_request_exceeded_limit EXCEPTION;

  PRAGMA EXCEPTION_INIT (
    user_session_request_exceeded_limit,
    -20211
  );
END SESSION_REQUEST_GLOBALS;
/

Константы я выношу в отдельный пакет — мы это уже обсуждали
https://t.me/db_developers_channel/186

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

🛠️ Один из вариантов реализации. Часть. 2

🎛️ Контроллер лимитов

CREATE OR REPLACE PACKAGE SESSION_REQUEST_CONTROLLER
IS
  PROCEDURE acquire_session_request_limit_slot;
  PROCEDURE release_session_request_limit_slot;
  PROCEDURE delete_user_session_request_old_limit_slots;
END SESSION_REQUEST_CONTROLLER;
/

Один метод занимает слот,
второй — освобождает,
третий нужен для джоба.

delete_user_session_request_old_limit_slots нужен потому, что если сессия зависнет, её убьют или придёт запрос на прерывание,
то слот будет занят навсегда.

⚙️ Реализация логики

CREATE OR REPLACE PACKAGE BODY SESSION_REQUEST_CONTROLLER
IS
  PROCEDURE acquire_session_request_limit_slot
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_slot_amount   INTEGER;
    v_slot_limit    INTEGER;
    v_max_slot      user_session_request_limit_slots.slot%TYPE;

    PROCEDURE get_max_slot (
      p_max_slot   OUT user_session_request_limit_slots.slot%TYPE)
    IS
      CURSOR limit_slots IS
          SELECT /*+ FIRST_ROWS */
                 usrls.slot
            FROM user_session_request_limit_slots usrls
        ORDER BY usrls.slot DESC;
    BEGIN
      OPEN limit_slots;

      FETCH limit_slots INTO p_max_slot;

      CLOSE limit_slots;
    EXCEPTION
      WHEN OTHERS
      THEN
        IF limit_slots%ISOPEN
        THEN
          CLOSE limit_slots;

          RAISE;
        END IF;
    END get_max_slot;
  BEGIN
   get_max_slot (p_max_slot => v_max_slot);

    v_max_slot := NVL (v_max_slot, 0) + 1;

    WHILE TRUE
    LOOP
     <<insert_slot>>
      BEGIN
        INSERT INTO user_session_request_limit_slots (slot, created)
             VALUES (v_max_slot, SYSTIMESTAMP);

        EXIT;
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX
        THEN
          v_max_slot := v_max_slot + 1;
      END insert_slot;
    END LOOP;

    SELECT COUNT (*)
      INTO v_slot_amount
      FROM user_session_request_limit_slots usrls;

    v_slot_limit := 10; -- лимит можно брать из параметров системы

    IF v_slot_amount > v_slot_limit THEN
      ROLLBACK;

      RAISE_APPLICATION_ERROR(
        SESSION_REQUEST_GLOBALS.user_session_request_exceeded_limit_code,
        'Rate limit exceeded. Current amount of active API requests is '
        || v_slot_amount || ', rate limit is '
        || v_slot_limit || '.'
      );
    END IF;

    COMMIT;
  END;

  PROCEDURE release_session_request_limit_slot
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;

    CURSOR limit_slots IS
      SELECT ROWID
        FROM user_session_request_limit_slots usrls
    ORDER BY usrls.spot ASC
        FOR UPDATE SKIP LOCKED;

    v_rowid ROWID;
  BEGIN
    OPEN limit_slots;
    FETCH limit_slots INTO v_rowid;

    IF v_rowid IS NOT NULL THEN
      DELETE FROM user_session_request_limit_slots usrls
       WHERE ROWID = v_rowid;
      COMMIT;
    END IF;

    CLOSE limit_slots;
  END;

  PROCEDURE delete_user_session_request_old_limit_slots
  IS
  BEGIN
    DELETE FROM user_session_request_limit_slots
     WHERE created < SYSTIMESTAMP - INTERVAL '5' MINUTE;
  END;
END SESSION_REQUEST_CONTROLLER;
/

▶ Пример использования

BEGIN
  SESSION_REQUEST_CONTROLLER.acquire_session_request_limit_slot;

  -- process_request
  DBMS_LOCK.sleep(1);

  SESSION_REQUEST_CONTROLLER.release_session_request_limit_slot;
EXCEPTION
  WHEN OTHERS THEN
    SESSION_REQUEST_CONTROLLER.release_session_request_limit_slot;
END;
/

Мы считаем количество слотов уже после вставки —
это и даёт нам надёжность.

UPDATE 02.02.2026. Большое спасибо Рустаму Абдуллаеву.
Он нашел ошибку в алгоритме. Я его переделал.

👍 Если нравится обсуждать такие задачи!
👎 Если ничего не нравится!

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

💬 Всё, что накипело 👇
#Cases #SQL #Oracle #PLSQL

🎵 «Кто ты, горе или радость?
То замёрзнешь, то растаешь
Кто ты, ласковое солнце
Или мёртвый белый снег?
Я понять тебя пытаюсь
Кто же ты на самом деле
Кто же ты на самом деле
Айсберг или человек?». — Лидия Козлова.

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

В прошлом посте я представил свой вариант оптимизации запроса,
а также вариант, который выдал ИИ.

Я был убеждён, что вариант Электроника хуже,
но результаты теста для меня стали неожиданными.

Итак, создаём временную таблицу для сохранения промежуточного результата:

-- DROP TABLE result_query_tmp PURGE;
CREATE TABLE result_query_tmp
(
  author_id                     NUMBER,
  first_name                    VARCHAR2 (50),
  last_name                     VARCHAR2 (50),
  second_book_coauthors_list    VARCHAR2 (4000),
  fifth_book_genres_list        VARCHAR2 (4000),
  first_book_published          VARCHAR2 (4000)
);
/

И использую мою мини-трассировку на основе v$mystat, v$statname
для получения статистических данных.

Результат теста:
📊 Метрика, variant_1_by_me, variant_2_by_AI, во сколько раз лучше

⏱ Elapsed time — 27:30 (1650 c) → 05:48 (348 c) → ×4.7 быстрее
📖 Consistent gets — 206 720 097 → 2 587 188 → ×80 меньше
💽 Physical reads — 2 817 947 → 4 604 297 → хуже ×1.6
✍ Physical writes — 15 697 → 2 382 815 → хуже ×150
🔁 Recursive calls — 1 667 056 → 1 647 536 → одинаково
🧠 CPU — 154 434 → 23 475 → ×6.5 меньше
🧮 PGA max — 0 → 71 MB → выше
🧾 Redo — 161 → 19 140 → выше

Удивительно! Он потратил больше пространства TEMP,
но за счёт этого резко сократил чтения блоков и работу CPU.

Вывод!
Браво, Электронник! 👏
Теперь ни одну оптимизацию без совета от ИИ я делать не буду.

Я впервые вижу такой подход,
когда все подзапросы материализуются,
а потом используются для соединения и фильтрации.
Обычно материализуется лишь общее множество. Буду знать!

Он оказался прозорливее на этот раз.
Но слепо доверять ему не следует — надо проверять.

👍 Интересно!
👎 Не интересно!

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

💬 Комменты желательны👇
#️⃣ #SQLOptimization

🎵 «Дремлет за горой, мрачный замок мой.
Душу мучает порой, царящий в нём покой.
Я твоих фантазий страждущий герой,
А любви моей живой — все образы со мной.»
— «Король и Шут».

🎯 Задача о замене в запросе литералов на bind-переменные.

Оказалось, что даже саму постановку этой задачи сформулировать не так просто.

Представим, что СУБД получает запросы напрямую, а не через вызов соответствующего API.
Ужас — скажут одни, нормальная практика — скажут другие.
Я же скажу, что оба будут правы.

Но запросы могут приходить в «неочищенном» виде 🧹
Например, вот так:

SELECT 'string'   AS str
  FROM DUAL
 WHERE 1 = MOD (11, TO_NUMBER ('10'))

Понятно, что парсинга не избежать,
но можно хотя бы не строить новый план запроса, а использовать bind-переменные.


Скрипт, который моделирует процесс

DECLARE
  p_income_query     CLOB := q'{SELECT 'string'   AS str
  FROM DUAL
 WHERE 1 = MOD (11, TO_NUMBER ('10'))}';

  p_modified_query   CLOB := q'{SELECT :s1 AS str
  FROM DUAL
 WHERE :n1 = MOD (:n2, TO_NUMBER (:s2))}';

  TYPE query_bind_vars_rec IS RECORD
  (
    bind_var_name    VARCHAR2 (10),
    literal_value    VARCHAR2 (4000)
  );

  TYPE query_bind_vars_tab IS TABLE OF query_bind_vars_rec;

  query_bind_var     query_bind_vars_rec;
  query_bind_vars    query_bind_vars_tab;
BEGIN
/*
  replace_literals_with_binds (
    p_query            => p_income_query, 
    p_modified_query   => p_modified_query, 
    query_bind_vars    => query_bind_vars);
*/
  query_bind_vars := query_bind_vars_tab ();
  query_bind_vars.EXTEND (4);

  query_bind_var.bind_var_name := ':s1';
  query_bind_var.literal_value := 'string';
  query_bind_vars (1) := query_bind_var;

  query_bind_var.bind_var_name := ':s2';
  query_bind_var.literal_value := '10';
  query_bind_vars (2) := query_bind_var;

  query_bind_var.bind_var_name := ':n1';
  query_bind_var.literal_value := '1';
  query_bind_vars (3) := query_bind_var;

  query_bind_var.bind_var_name := ':n2';
  query_bind_var.literal_value := '11';
  query_bind_vars (4) := query_bind_var;

<<check_syntax>>
  DECLARE
    l_cursor_id   INTEGER;
  BEGIN
    l_cursor_id := DBMS_SQL.open_cursor;

    DBMS_SQL.parse (c               => l_cursor_id,
                    statement       => p_modified_query,
                    language_flag   => DBMS_SQL.native);

    FOR index# IN 1 .. query_bind_vars.COUNT
    LOOP
      DBMS_SQL.bind_variable (c       => l_cursor_id,
                              name    => query_bind_vars (index#).bind_var_name,
                              VALUE   => query_bind_vars (index#).literal_value);
    END LOOP;

    DBMS_SQL.close_cursor (l_cursor_id);
  EXCEPTION
    WHEN OTHERS
    THEN
      IF DBMS_SQL.is_open (c => l_cursor_id)
      THEN
        DBMS_SQL.close_cursor (c => l_cursor_id);
      END IF;
  END check_syntax;

  query_bind_vars.delete ();
EXCEPTION
  WHEN OTHERS
  THEN
    IF query_bind_vars IS NOT NULL
    THEN
      query_bind_vars.delete ();
    END IF;

    RAISE;
END;

🎯 Суть задачи
Реализовать метод replace_literals_with_binds,
то есть получить тот же запрос, но уже на основе bind-переменных.

👍 Интересно, что будет дальше!
👎 Ерунда!

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

💬 Ваши мысли — как вы бы решали данную задачу 👇
#Cases #SQL #Oracle #PLSQL

✨ «Палач не знает роздыха!..
Но всё же, чёрт возьми,
Работа-то на воздухе,
Работа-то с людьми.»
— Владимир Вишневский.

🎯 Задача о замене в запросе литералов на bind-переменные. Размышления

Так вот, просто бросился я на эту задачу, и было ощущение, что за день, максимум за два я её решу.
Но по мере погружения в задачу, тем больше тараканов начали вылезать в моей голове.
Вопрос первый: как правильно хранить значения bind-переменных?

Первоначальный способ хранения

TYPE query_bind_vars_rec IS RECORD
(
 bind_var_name    VARCHAR2 (10),
 literal_value    VARCHAR2 (4000)
);

TYPE query_bind_vars_tab IS TABLE OF query_bind_vars_rec;

На каждую переменную выходит 8000 байтов из-за особенностей RECORD, а если 100 переменных и в 100 сессиях — т.е. 80 MB в оперативке на какую-то ерунду.
Блин, на такую реализацию смотреть больно — слишком много. 😬

Какой вывод? Переменные разных типов, и логично хранить их по-разному?! Логично!
Тогда нужно создавать тип OBJECT.
Вот этим и надо заняться.

CREATE OR REPLACE TYPE t_variant_value FORCE AS OBJECT
(
  number_value NUMBER,
  varchar2_value VARCHAR2 (4000 CHAR),
  date_value DATE,
  timestamp_value TIMESTAMP,
  timestamp_with_time_zone_value TIMESTAMP WITH TIME ZONE,

  CONSTRUCTOR FUNCTION t_variant_value (VALUE IN NUMBER)
    RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION t_variant_value (VALUE IN VARCHAR2)
    RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION t_variant_value (VALUE IN DATE)
    RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION t_variant_value (VALUE IN TIMESTAMP)
    RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION t_variant_value (VALUE IN TIMESTAMP WITH TIME ZONE)
    RETURN SELF AS RESULT,

  MEMBER PROCEDURE clear,
  MEMBER FUNCTION is_not_null
    RETURN BOOLEAN,

  MEMBER PROCEDURE set_value (VALUE IN NUMBER),
  MEMBER PROCEDURE set_value (VALUE IN VARCHAR2),
  MEMBER PROCEDURE set_value (VALUE IN DATE),
  MEMBER PROCEDURE set_value (VALUE IN TIMESTAMP),
  MEMBER PROCEDURE set_value (VALUE IN TIMESTAMP WITH TIME ZONE),

  MEMBER FUNCTION get_number_value
    RETURN NUMBER,
  MEMBER FUNCTION get_varchar2_value
    RETURN VARCHAR2,
  MEMBER FUNCTION get_date_value
    RETURN DATE,
  MEMBER FUNCTION get_timestamp_value
    RETURN TIMESTAMP,
  MEMBER FUNCTION get_timestamp_with_time_zone_value
    RETURN TIMESTAMP WITH TIME ZONE
)

Тело типа t_variant_value — в приложении.

В чём выигрыш в использовании OBJECT?
Не выделяется память заранее, а только та, что по факту используется. ⚙️

Далее займусь уже непосредственно алгоритмом.

👍 Интересно, что будет дальше!
👎 Ерунда!

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

💬 Комменты туда 👇
#Cases #SQL #Oracle #PLSQL