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

✨ «Судьба, прошу, не пожалей добра,
Терпима будь, а значит, будь добра,
Храни ее и под своей рукою
Дай счастье ей, а значит, дай покоя
Той женщине, которую люблю.

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

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

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

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

Не приведи, судьба, на склоне дней
Ей пережить родных своих детей.
И если бед не избежать на свете,
Пошли их мне, не ей самой, не детям
Той женщины, которую люблю!» — Кайсын Кулиев.

Уважаемый читатели!
С днем влюбленных!
Любите и будьте любимы!
Влюбляйтесь и влюбляйте!
Ревнуйте и вызывайте ревность!
Если в Вашей жизни и придется страдать,
то пусть это будут либо муки творчества, либо муки любви!

🎵 «Долго, долго, долго пляшет огонёк,
Только, только, только между двух дорог.
Глаз твоих прекрасных звонкие лучи,
Светят ясно-ясно ветреной ноги.

Что со мною будет? Будет что с тобой?
Что нам скажут люди, милый ангел мой?
До самозабвенья, до земли сырой,
До изнеможенья болен я тобой.»
— Гарик Сукачёв.

✨ Мини-лексер для SQL на PL/SQL

Чем дальше в лес, тем злее партизаны!
Просидел я с задачей четыре вечера и получил кое-какие промежуточные результаты.

1. Далеко не все литералы можно заменить на bind-переменные.
Конструкции ORDER BY, GROUP BY, CONNECT BY, FETCH, PIVOT, а также множество функций, таких как JSON_VALUE, нужно обрабатывать особо.
Например, в JSON_VALUE первый параметр можно заменить на bind-переменную, а второй — нельзя.

2. Необходима поддержка CLOB.
Запросы могут быть длиннее, чем 32 767 символов.

3. Обработка комментариев тоже требует особого внимания.

"/* /**/" — такой комментарий валидный.
"/*
-- */" — такой уже нет.
А кейс со строкой '123/*456*/789' вообще портит настроение.

4. Многими любимый ИИ не хочет работать как следует.
— «Сам! Всё сам!» 🤖

Но, тем не менее, я нашёл нужное направление:
я написал небольшой лексер SQL на PL/SQL, который проходит по тексту запроса посимвольно и разбивает его на токены, сохраняя контекст.

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

🔹 Что делает алгоритм

Он превращает SQL-строку в поток токенов:
TEXT — фрагменты текста
(, ), , — структурные символы
COMMENT — блочные комментарии
ключевые слова: GROUP, ORDER, BY, HAVING, FETCH, ROWS,
DATE, TIMESTAMP, TO_DATE, TO_TIMESTAMP, …

И при этом запоминает контекст, в котором они находятся.
🔹 Как работает
Алгоритм делает один линейный проход по тексту запроса.
На каждом шаге он:
берёт текущий символ;
пропускает пробелы и переводы строк;
если видит /* ... */ — вырезает комментарий как отдельный токен;
если встречает:
( → увеличивает глубину скобок,
) → уменьшает,
, → увеличивает счётчик запятых в текущем уровне скобок;

Если находится в начале лексемы — проверяет, не начинается ли тут
одно из ключевых слов (GROUP, ORDER, DATE, TIMESTAMP и т.д.).

Всё остальное собирает в текстовые токены.

🔹 Что сохраняется в каждом токене
Каждый элемент знает:
token_type — тип (TEXT, (, ,, GROUP …)
token_start_pos — позиция в исходной строке
token_length — длина
paren_depth_index — уровень вложенности скобок
comma_index — номер аргумента в текущем уровне

🔹 Зачем это нужно
Такой поток токенов позволяет:
определить, какой параметр в SELECT идёт под каким номером;
понять, какие конструкции начались и какие закончились.
Например: если видим GROUP BY, то, если после него идёт ORDER BY,
очевидно, что здесь менять литералы уже не надо.

Безусловно, до конечного результата ещё далеко,
но уже виден свет в конце туннеля. 🌟

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

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

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

💬 Если есть мысли, анализ или критика — буду рад услышать.👇
#Cases #SQL #Oracle #PLSQL

🎵 «Я совсем не случайно
Подводил судьбе итог,
Растворился в печали,
Заблудился между строк.

Но внезапно и сильно,
Перейдя из уст в уста,
Мне открылась вакцина
Жизни с чистого листа» — Би-2

🧹 Чистка строки от "лишних" пробелов

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

Допустим, у вас есть строка:

" 1  2   3    4     "

И её нужно нормализовать по пробелам, то есть представить в виде:

" 1 2 3 4 "

Как бы вы решали такую задачу?

А что если в строке встречаются символы табуляции или перевода строки?

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

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

💬 Комменты привествуются👇
#Cases #SQL #Oracle #PLSQL

🎵 «Только когда плывёшь против течения
Понимаешь, чего стоит свободное мнение
Звенья собираются в длинные цепочки
Линия жизни становится точкой» — Сергей Шнуров

✨Разбор задачи нормализация строки!

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

Задачу о нормализации строки я взял не случайно: мне нужно найти самый быстрый способ.
Изначально было два варианта:
ручной перебор строки посимвольно;
использование регулярных выражений.

Итак, мой первый вариант:

PROCEDURE normalize_space_option_1 (p_text              IN     VARCHAR2,
                                    p_normalized_text      OUT VARCHAR2)
IS
  v_result        VARCHAR2 (4000);
  v_prev_space    BOOLEAN := FALSE;
  v_text_length   INTEGER;
BEGIN
  v_text_length := LENGTH (p_text);

  FOR index# IN 1 .. v_text_length
  LOOP
    IF SUBSTR (p_text, index#, 1) = ' '
    THEN
      IF NOT v_prev_space
      THEN
        v_result := v_result || ' ';
      END IF;

      v_prev_space := TRUE;
    ELSE
      v_result := v_result || SUBSTR (p_text, index#, 1);
      v_prev_space := FALSE;
    END IF;
  END LOOP;

  p_normalized_text := v_result;
END normalize_space_option_1;

Мой второй вариант (а также вариант Антона Петрусевича):

PROCEDURE normalize_space_option_2 (p_text              IN     VARCHAR2,
                                    p_normalized_text      OUT VARCHAR2)
IS
BEGIN
  p_normalized_text := REGEXP_REPLACE (p_text, '\s+', ' ');
END normalize_space_option_2;

А также вариант Андрея Карнаухова:

PROCEDURE normalize_space_option_3 (p_text              IN     VARCHAR2,
                                    p_normalized_text      OUT VARCHAR2)
IS
BEGIN
  p_normalized_text :=
    REPLACE (REPLACE (REPLACE (p_text, ' ', '# '), ' #', ''), '#', '');
END normalize_space_option_3;

Если первые два варианта интуитивно понятны, то третий — с изюминкой.

Осталось проверить производительность.

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

Если есть желание протестировать на 1 млрд — пожалуйста, поделитесь результатами 🙂

Итак, результаты

-- variant1_me
-- time: -000000000 00:02:47.738000000
-- session pga memory max: -65536
-- CPU used by this session: -16719

-- variant2_Anton_Petrusevich
-- time: -000000000 00:02:12.573000000
-- session pga memory max: 0
-- CPU used by this session: -13111

-- variant3_Andrey_Karnauhov
-- time: -000000000 00:00:57.341000000
-- session pga memory max: -131072
-- CPU used by this session: -5686

Андрей — браво!
Крутой вариант — снимаю шляпу, обнажаю голову!

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

Удивительно и другое: регулярка отработала быстрее посимвольного разбора — ещё один миф развеян.

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

Скрипт теста в приложении.

👍 Мы с Вами одной крови!
👎 А мы идем на север!

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

💬 Комменты нужны, как никогда👇
#Cases #SQL #Oracle #PLSQL

✨ «Весёлый мир. Все шутят. И все шутят одинаково. Даже благородный Румата.»
— Братья Стругацкие.

🔎 Задача о замене литералов в запросе на bind-переменные. Сложные кейсы.

Казалось, счастье уже близко, проблема почти решена, и Закон Парето удастся обойти стороной… Но он твёрд как скала.

«20% усилий дают 80% результата, а остальные 80% усилий — лишь 20% результата».

Продолжая реализацию парсинга запросов с заменой литералов на bind-переменные, я столкнулся с интересными кейсами.

Что интересного выяснилось за эти дни?

Например, форма записи чисел может быть весьма необычной. В тестовом примере я собрал почти все варианты:

SELECT
     123,
     100.,
     .14,
     -0.4,
     1e-1,
     +5,
     +10,
     +123.,
     -23.0,
     1.123,
     .123,
     1e2,
     -     2E  -   3,
     +  3.5e  +  4
FROM DUAL

Кто-нибудь вообще использует запись вроде + 3.5e + 4?
Но ведь это валидно!

Со строками оказалось проще

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

SELECT
/*
  '/* comment1*/' AS str1,
*/
  '123' AS str2
FROM DUAL

Но в целом со строками всё оказалось довольно аккуратно.

Вот варианты, которые я научился обрабатывать:

SELECT 
  /*1. Обычные строковые литералы*/
  'abc' || '123' || 'hello world' || '' || ' '
   AS s_simple,

  /*2. Экранирование одиночных кавычек*/
    'it''s ok'
  || 'John''s book'
  || '''quoted'''
  || ''''''
  || 'He said: ''Hello!'''
   AS s_escape_1,

  /*3. Спецсимволы*/
  'line1
  line2' || 'tab  here' || 'backslash \ here' || 'percent % underscore _'
   AS s_special_symbols,

  /*4. q-кавычки*/
    q'{simple text}'
  || q'[square brackets]'
  || q'(round brackets)'
  || q'<angle brackets>'
  || q'!exclamation!'
  || q'#hash#'
  || q'$dollar$'
   AS q1,

  /*5. q с кавычками внутри*/
    q'{it''s fine}'
  || q'[ "double quotes" inside ]'
  || q'( single '' quotes inside )'
  || q'{ mixed '' " quotes }'
  || q'{
  line 1
  line 2
  line 3
  }'
  || q'{}' || q'[]'
   AS q_escape_inside,

  /*6. Строки с SQL внутри*/
  'SELECT * FROM dual' || q'{WHERE name = 'John'}'
   AS s_sql,

  /*7. CAST / функции*/
    UPPER('hello')
  || CASE WHEN 'test' LIKE '%es%' THEN '1' ELSE '0' END
  || CASE WHEN 'abc' IN ('abc', 'def', 'ghi') THEN '1' ELSE '0' END
   AS s_func,

  /*8. JSON / XML внутри строки*/
  '{"a": "b", "c": "d"}'
   AS s_json,

  q'{<tag attr="1">text</tag>}'
   AS s_xml
FROM DUAL

А теперь самое вкусное

То, что действительно усложняет парсинг.
Из двух вариантов какой запрос валиден?

SELECT :n01 AS id FROM DUAL
ORDER BY :n02

SELECT :n01 AS id FROM DUAL
UNION ALL
SELECT :n02 AS id FROM DUAL
ORDER BY :n03

Почему первый валиден, а второй — нет? 🤔

А как обрабатывать такие кейсы?

SELECT TO_NUMBER (
  JSON_VALUE /*comment2*/ 
    ('{"user":' /*comment1*/ 
     || (SELECT /*+ hint1*/ JSON_VALUE 
          (/*comment3*/ 
           '{"a":null}',
           '$.b'
           RETURNING VARCHAR2(100)
           DEFAULT '{"name":"1"}}' ON EMPTY DEFAULT '0' ON ERROR)
        FROM DUAL),
    /*comment2*/
    '$.user.name'
    RETURNING VARCHAR2(100)
    ERROR ON ERROR
    NULL ON EMPTY     
    ) 
) AS name
FROM DUAL

Сложность в том, что первый параметр JSON_VALUE можно заменить на bind-переменную, а второй — нельзя.

А то, что язык позволяет писать таких «красавцев», явно говорит: без рекурсии в парсере не обойтись.

Ничего, неприятность эту мы переживём.
Рекурсию реализуем. 💪

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

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

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

🎵 «Выходят на арену силачи,
Не ведая, что в жизни есть печаль.
Они подковы гнут, как калачи,
И цепи рвут движением плеча.
И рукоплещет восхищенный зал,
И на арену к ним летят цветы.
Для них играет туш, горят глаза,
А мною заполняют перерыв.» — Борис Баркас

Чтение CLOB и sliding stream. Практическое применение REPLACE_CLOB. Часть 1

Я закончил разработку парсера на основе VARCHAR2 и начал переносить его на CLOB.
И довольно быстро выяснилось неприятное: если заменить обычный SUBSTR на DBMS_LOB.SUBSTR, появляется существенная деградация по времени и потреблению ресурсов.

Сказать, что это было неожиданно — нельзя. Иначе я бы и не стал проверять.
Но разница оказалась намного больше, чем я предполагал.

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

Идея
Основная мысль простая:
обработку строки делать в VARCHAR2
CLOB читать кусками
То есть мы не работаем напрямую с CLOB на каждом шаге.

Алгоритм выглядит так:

1️⃣ Читаем из CLOB максимально возможный кусок (обычно до 32767 символов).
2️⃣ Помещаем его в рабочий буфер VARCHAR2.
3️⃣ Выполняем парсинг буфера.
4️⃣ Проверяем, достаточен ли хвост строки для корректного разбора.
5️⃣ Если хвост может содержать незавершённую конструкцию — оставляем его.
6️⃣ Читаем следующий кусок из CLOB и добавляем его к хвосту.
7️⃣ Продолжаем обработку.

Получается скользящее окно (sliding stream), где хвост предыдущего блока переносится в следующий.

 ├─────────────── chunk1 ───────────────┤
                         ├────────────── chunk2 ───────────────┤
                                         ├──────────── chunk3 ───────────┤

VARCHAR2 buffer
[ chunk + tail ]

Таким образом:
CLOB читается редко и большими блоками
вся тяжёлая логика работает на быстрых VARCHAR2

Предлагаю рассмотреть практическое применение этого механизма на: REPLACE для CLOB
Кто работает с CLOB, не нужно объяснять как не хватает REPLACE для него.

Функция REPLACE работает с VARCHAR2.
Для CLOB она фактически работает только если длина меньше 32767 символов.

А что делать, если CLOB больше?
Можно применить тот же sliding stream:
читаем CLOB кусками
добавляем хвост предыдущего блока
выполняем REPLACE над VARCHAR2
пишем результат в выходной CLOB
При этом важно сохранять хвост длиной не меньше длины заменяемой строки, чтобы корректно обработать совпадения на границе блоков.

Одно плохо, чтобы корректно обрабатывать пограничные значения, приходится отсекать замену строк, у которых суффикс может быть префиксом:
1) один пробел можно, два пробела нельзя
2) 'ABC' можно, 'ABA' нельзя
Такие кейсы нечастые, но всё же алгоритм не универсальный.
Если добавить спец обработку таких кейсов, то можно сделать универсальным

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

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

Чтение CLOB и sliding stream. REPLACE_CLOB. Часть 2

Далее идет уже скрипт проверки и тест

DECLARE
  v_clob   CLOB;
  v_res    CLOB;

  PROCEDURE print_clob (v_clob IN CLOB)
  IS
    v_pos   NUMBER := 1;
  BEGIN
    DBMS_OUTPUT.put_line ('getlength = ' || TO_CHAR (DBMS_LOB.getlength (lob_loc => v_clob)));

    WHILE v_pos <= DBMS_LOB.getlength (v_clob)
    LOOP
      DBMS_OUTPUT.put_line (DBMS_LOB.SUBSTR (lob_loc => v_clob, amount => 4000, offset => v_pos));

      v_pos := v_pos + 4000;
    END LOOP;
  END print_clob;
BEGIN
 <<get_test_clob>>
  DECLARE
    v_varchar2   VARCHAR2 (4000) := RPAD ('abc test abc ', 4000, 'abc');
  BEGIN
    DBMS_LOB.createtemporary (lob_loc => v_clob, cache => TRUE);

    FOR index# IN 1 .. 10
    LOOP
      DBMS_LOB.writeappend (lob_loc => v_clob, amount => 4000, buffer => v_varchar2);
    END LOOP;
  END get_test_clob;

  print_clob (v_clob => v_clob);

  v_res := replace_clob (p_clob => v_clob, p_search => 'abc', p_replace => 'XYZ');

  print_clob (v_clob => v_res);

  IF DBMS_LOB.istemporary (v_res) = 1
  THEN
    DBMS_LOB.freetemporary (v_res);
  END IF;

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

    IF DBMS_LOB.istemporary (v_clob) = 1
    THEN
      DBMS_LOB.freetemporary (v_clob);
    END IF;

    RAISE;
END;

Кстати, всем любителя ИИ, передаю привет, так как я полдня потратил на то, чтобы объяснить электроннику, что мне надо и что за кривые алгоритмы он мне рисует.
Я написал алгоритм сам, хотя и потратил на это 2 дня.

👍 Replace для CLOB мне нравится
👎 Зачем всё это?

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

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