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

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