Oracle Developer👨🏻‍💻: “with function и pragma udf: обещали быстрее, проверяем 🔍 Коллеги, все…”

with function и pragma udf: обещали быстрее, проверяем 🔍

Коллеги, всем привет! 👋
На связи Денис.

В посте про функции в SQL я вскользь написал: «С 12c: pragma udf или with function - дешевле переключение контекста». Юрий в комментах: «вот это интересно, примеров бы». Держите. С замером 😄

with function
Функция объявляется прямо в запросе, без create:

with
  function get_vat(p_sum number) return number is
  begin
    return round(p_sum * 0.2, 2);
  end;
select salary, get_vat(salary) vat
  from employees
/

Когда удобно: разовый скрипт, нет прав на create, логика нужна только этому запросу. Александр в комментах поделился кейсом: через with function собирает трейс-файл из v$diag_trace_file_contents в CLOB. Прям в одном запросе, без объектов в схеме 👍🏻

Нюансы:
🔹 Если with function не в top-level select (вложенный запрос, insert/update/merge) - нужен хинт /*+ with_plsql */, иначе ORA-32034: unsupported use of WITH clause. Хинт не оптимизаторский, без него запрос просто не разберется.

insert /*+ with_plsql */ into t_vat
with
  function get_vat(p_sum number) return number is
  begin
    return round(p_sum * 0.2, 2);
  end;
select salary, get_vat(salary) from employees
/

🔹 В SQL*Plus/SQLcl запрос заканчивается /, а не ; - внутри же PL/SQL.
🔹 Имя из with перекрывает одноименную функцию схемы.

pragma udf
Хранимая функция с пометкой «меня вызывают из SQL»:

create or replace function get_vat(p_sum number) return number is
  pragma udf;
begin
  return round(p_sum * 0.2, 2);
end;
/

В документации скромно: «might improve its performance». Might. Ну ок, проверяем.

Замер
sum(f(id)) по таблице на 1 млн строк, функция mod(p, 7) * 2, среднее из 5 прогонов, секунды. Docker на ноутбуке, так что смотрим на порядок, а не на сотые.

вариант                  26ai   18c
обычная функция          4.83   2.11
pragma udf               1.10   1.53
with function            1.38   1.00
with function + udf      1.11   1.15
чистый SQL               0.59   0.42

Что видно:
🔹 pragma udf ускорила хранимую функцию: на 26ai в 4 раза, на 18c скромнее, в 1.4. И вывела ее на уровень with function.
🔹 udf внутри with function компилируется и работает, но выигрыша не дает. Тут Александр прав: «pragma udf не пашет с inline-функциями» - в смысле эффекта, не ошибки.
🔹 Цена udf: при вызове из PL/SQL функция чуть медленнее (цикл на 1 млн вызовов: 0.59 против 0.67 с на 26ai). Если функцию дергают в основном из PL/SQL - udf не нужна.
🔹 Чистый SQL быстрее лучшего варианта с функцией еще в 2 раза. Хозяйке на заметку 🤷🏻‍♂️

Итог
Функция нужна в SQL и живет в схеме - ставьте pragma udf. Нужна одному запросу - with function. Можно без функции - пишите на SQL.

А вы with function в проде используете или только в скриптах? Пишите в чатик 💬

С вами был Денис. Всем хорошей недели 🤝

#oracle #sql #plsql #оптимизация #performance #функции #фишки #Denis_Kivilev

Канал Oracle Developer | Чатик 💬
Мини-курс Оптимизация: Быстрый старт 🚀

📱 YouTube 📱 ВКонтакте 📱 LinkedIn 📱Threads
RUTUBE