С версии 11.2 фраза WITH может использоваться для формулирования рекурсивных запросов в соответствии со стандартом SQL:1999. В этом качестве она способна решать ту же задачу, что и CONNECT BY.
Конструкция с WITH имеет свое название: Common Table Expression (CTE) или обобщенное табличное выражение (OTB) – это временные результирующие наборы (т.е. результаты выполнения SQL запроса), которые не сохраняются в базе данных в виде объектов, но к ним можно обращаться.
CTE бывают рекурсивными Recursive CTE и обычными (нерекурсивными) Non-Recursive CTE.
С нерекурсивными СТЕ работали многие и не всегда правильно.
Фактически это создание временных таблиц, но существующих только для одного запроса, а не для сессии. К ним можно обращаться внутри этого запроса. Такой запрос хорошо читается, он понятен, его легко видоизменять, если потребуется.
Преимущества CTE:
- читаемость кода (Readability) - CTE способствует удобочитаемости. Вместо того, чтобы объединять всю логику запроса в один большой запрос, можно создать несколько CTE, которые будут объединены позже в основном запросе. Это наиболее частый случай использования нерекурсивных СТЕ, но не стоит забывать о цене такого удобства.
- замена представления (Substitute for a View) - если нет разрешения на создание объекта или нет необходимости его создавать, поскольку он используется только в этом одном запросе, проще использовать СТЕ. И опять нужно смотреть на цену такого решения.
- рекурсия (Recursion) - работа с иерархическими данными и не только;
- ограничения (Limitations) - преодоление ограничений оператора SELECT, таких как ссылка на себя (рекурсия) или выполнение GROUP BY с использованием недетерминированных функций.
- ранжирование (Ranking) - запросы с функциями ранжирования, такие как ROW_NUMBER (), RANK (), DENSE_RANK () и т. д.
with cte_months as (select t.*,
dense_rank() over (order by parent_id) as rnk
from table_tmp t)
SELECT * from cte_months
where rnk = 1;
Селектом выше выбраны только времена года из таблицы table_tmp.
Но удобства СТЕ могут обойтись дорого. Проблемы связаны с материализацией выражения после AS внутри конструкции WITH… AS (). Его еще называют внутренним выражением и вычисляют перед тем, как начать вычисление остального, его нельзя встроить в запрос верхнего уровня (no inlining).
Планирование этого выражения происходит без учета остальной части запроса. Такое поведение называют барьером для оптимизации, или fencing. Кроме того, для материализация требуются достаточные ресурсы памяти. И если выборка большая, то начинаются проблемы.
Создадим таблицу.
create table clients
(id int generated by default as identity,
type number,
status number,
name varchar2(250));
Создадим составной индекс.
CREATE INDEX clients_indx1
ON clients(type,status);
Наполним таблицу.
insert into clients (type, status, name) values(1, 1, 'Cat');
insert into clients (type, status, name) values(2, 1, 'Mouse');
insert into clients (type, status, name) values(3, 0, 'Dog');
insert into clients (type, status, name) values(1, 0, 'Cow');
insert into clients (type, status, name) values(1, 0, 'Horse');
insert into clients (type, status, name) values(3, 0, 'Sparrow');
insert into clients (type, status, name) values(3, 0, 'Duck');
insert into clients (type, status, name) values(3, 1, 'Bug');
insert into clients (type, status, name) values(2, 1, 'Fly');
insert into clients (type, status, name) values(1, 1, 'Spider');
Посмотрим на время выполнения и план обычного запроса к таблице.
select * from clients
where type = 1
and status = 0;
Время выполнения: 0,0017 seconds
План запроса:
Перепишем запрос на запрос с СТЕ:
with t_home as (select * from clients
where type = 1
and status = 0)
select * from t_home;
План запроса в данном случае не изменится. Но если добавим хинт /*+ materialize*/, то план запроса и время выполнения изменится.
А если усугубить ситуацию и вынести за пределы СТЕ фильтр по полю, который является частью составного индекса, то получим совсем неподходящий план запроса и время выполнения еще больше.
Произошло вот что: в случае с подзапросами оптимизатор сразу сообразил, что условия status = 0 и type = 1 можно объединить в один фильтр и искать по индексу. В случае CTE у оптимизатора нет выбора: он должен сначала разобраться с условием внутри конструкции WITH… AS, материализовать результат и только после этого работать дальше.
И здесь дело не в том, что материализация потребует ресурсов. Время тратится на последовательный поиск. Оптимизатор не может использовать поиск по индексу из-за того, что составной индекс строится по type, а уже потом по status, а о запросе с условием type = 1 он ничего не узнает, пока не выполнит внутреннее условие CTE. Оно — за барьером.
CTE материализуется по умолчанию, если к ней обращаются более одного раза, то есть оптимизатор Oracle решает, имеет ли смысл материализовать CTE или нет, но можно перезаписать оценку оптимизатора подсказкой /*+ MATERIALIZE*/ или /*+ INLINE */ для получения обратного результата.
/*+ INLINE */ позволяет в случаях, когда Oracle обязан материализовать подзапрос конструкции WITH без подсказок (согласно правилам выполнения subquery factoring, например, когда подзапрос используется в основном запросе > 1 раза), не делать этого, рассматривая подзапрос в качестве inline view.
Забегая вперед: рекурсивные запросы с WITH материализуются всегда. Хинты на это решение оптимизатора не действуют.
И все же основное назначение CTE – рекурсия: т.е. запрос, который, написанный один раз, будет повторяться многократно пока истинно некоторое условие.
Рекурсивный CTE имеет следующий вид:
От обычного CTE-запроса рекурсивный отличается только рекурсивной частью, которая вводится предложением UNION ALL. В рекурсивной части присутствует ссылка на имя CTE, т.е. внутри CTE ссылается само на себя. Это и есть рекурсия. Условие этого соединения определяет отношение родитель – ребенок. От этого зависит переходите ли вы на верхние уровни дерева или на нижние. Естественно, анкорная и рекурсивная части должны иметь одинаковый набор столбцов.
Например, нужно получить ряд чисел от 1 до12, такая задача часто встречается для расчетов за год: нужны 12 месяцев для последущего left join фактических данных. Как это решается с помощью CONNECT BY, уже было в предыдущем посте. Вот как это будет с WITH:
WITH months ( n ) AS (SELECT 1 AS n FROM dual -- исходное множество -- одна строка
UNION ALL -- символическое «объединение» строк
SELECT n + 1 AS n -- рекурсия: добавок к предыдущему результату
FROM numbers -- предыдущий результат в качестве источника данных
WHERE n <= 12 -- если не ограничить, будет бесконечная рекурсия
)
SELECT n FROM months; -- основной запрос
Очень важно ограничить запрос - WHERE n <= 12, иначе получится «бесконечное» выполнение (на деле – пока хватит ресурсов СУБД для сеанса или же пока администратор не прервет запрос или сеанс).
С фразой CONNECT BY «бесконечное» выполнение в принципе невозможно.
Практический пример: создать массив дат за последний месяц, заканчивая системной датой.
with cte (dt) as (select add_months(trunc(sysdate), -1) as dt
from dual
union all
select cte.dt + 1
from cte
where cte.dt + 1 < sysdate
)
select * from cte;
Как и в случае с CONNECT BY, вынесенный рекурсивный подзапрос применяется не только к иерархически организованным данным. Обычно это задачи, связанные с использованием предыдущих данных при выполнении рекурсии. Прицип схож с использованием цикла while.
Для иллюстрации принципа находим первые 10 чисел Фибоначчи.
WITH fibonacci (n, fib_n, next_fib_n) AS
(
SELECT 1, 0, 1 from dual
UNION ALL
SELECT n + 1, next_fib_n, fib_n + next_fib_n
FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;
Результат:
|
n |
fib_n |
next_fib_n |
|
1 |
0 |
1 |
|
2 |
1 |
1 |
|
3 |
1 |
2 |
|
4 |
2 |
3 |
|
5 |
3 |
5 |
|
6 |
5 |
8 |
|
7 |
8 |
13 |
|
8 |
13 |
21 |
|
9 |
21 |
34 |
|
10 |
34 |
55 |
Или например, необходимо рассчитать заработную плату трех сотрудников с плановым повышением за квартал на 10%.
SID - внутренний номер сотрудника, IDX- квартал.
with t_salary(sid, amount) as (select 101 as sid, 1000 as amount from dual union all
select 102 as sid, 900 as amount from dual union all
select 103 as sid, 1100 as amount from dual),
t_new_salary (idx, sid, amount) as (select 1 as idx, sid, amount from t_salary
union all
select idx + 1, sid, amount*1.1 as amount from t_new_salary
where idx < 4)
select * from t_new_salary;
Результат:
|
idx |
sid |
amount |
|
1 |
101 |
1000.00 |
|
1 |
102 |
900.00 |
|
1 |
103 |
1100.00 |
|
2 |
101 |
1100.00 |
|
2 |
102 |
990.00 |
|
2 |
103 |
1210.00 |
|
3 |
101 |
1210.00 |
|
3 |
102 |
1089.00 |
|
3 |
103 |
1331.00 |
|
4 |
101 |
1331.00 |
|
4 |
102 |
1197.90 |
|
4 |
103 |
1464.10 |
В обоих случаях для того, чтобы создать последовательность в цикле и ограничить его, вводится столбец с нумерацией строк (n в примере с числами Фибоначчи, idx - в примере с зарплатой). Этот столбец является эквивалентом псевдостолбца LEVEL.
Но не обязательно его создавать, если есть столбец, по которому можно двигаться в цикле с определенным шагом. Ту же зарплату можно рассчитать, изменив условие на повышение раз в год, начиная с 2020 года.
with t_salary(year, sid, amount) as (select 2020 as year, 101 as sid, 1000 as amount from dual union all
select 2020 as year,102 as sid, 900 as amount from dual union all
select 2020 as year,103 as sid, 1100 as amount from dual),
t_new_salary (year, sid, amount) as (select year, sid, amount from t_salary
union all
select year + 1, sid, amount*1.1 as amount from t_new_salary
where year < 2024)
select * from t_new_salary;
Результат:
|
year |
sid |
amount |
|
2020 |
101 |
1000.00 |
|
2020 |
102 |
900.00 |
|
2020 |
103 |
1100.00 |
|
2021 |
101 |
1100.00 |
|
2021 |
102 |
990.00 |
|
2021 |
103 |
1210.00 |
|
2022 |
101 |
1210.00 |
|
2022 |
102 |
1089.00 |
|
2022 |
103 |
1331.00 |
|
2023 |
101 |
1331.00 |
|
2023 |
102 |
1197.90 |
|
2023 |
103 |
1464.10 |
|
2024 |
101 |
1464.10 |
|
2024 |
102 |
1317.69 |
|
2024 |
103 |
1610.51 |
Для построения иерархичного запроса воспользуемся таблицей table_tmp из предыдущего поста
с правильной иерархией.
with cte_year(id, tree, path, lvl) as
Результат тот же:
|
TREE |
PATH |
|
Year |
/Year |
|
Winter |
/Year/Winter |
|
December |
/Year/Winter/December |
|
January |
/Year/Winter/January |
|
February |
/Year/Winter/February |
|
Spring |
/Year/Spring |
|
March |
/Year/Spring/March |
|
April |
/Year/Spring/April |
|
May |
/Year/Spring/May |
|
Summer |
/Year/Summer |
|
June |
/Year/Summer/June |
|
July |
/Year/Summer/Julay |
|
August |
/Year/Summer/August |
|
Autumn |
/Year/Autumn |
|
September |
/Year/Autumn/September |
|
October |
/Year/Autumn/October |
Аналогом ORDER BY SIBLINGS является специальное указание SEARCH. С его помощью создаем псевдостолбец и отсортируем по нему результат в итоговом селекте через ORDER BY.
Существуют два метода сортировки строк в рекурсивном СТЕ:
- SEARCH
BREADTH FIRST BY : сначала выводятся предки (sibling rows) от самого
старшего(корня), потом по снижению уровня иерархии, и последние выводятся
потомки (child rows),
|
TREE |
PATH |
|
Year |
/Year |
|
Winter |
/Year/Winter |
|
Spring |
/Year/Spring |
|
Summer |
/Year/Summer |
|
Autumn |
/Year/Autumn |
|
December |
/Year/Winter/December |
|
January |
/Year/Winter/January |
|
February |
/Year/Winter/February |
|
March |
/Year/Spring/March |
|
April |
/Year/Spring/April |
|
May |
/Year/Spring/May |
|
June |
/Year/Summer/June |
|
Julay |
/Year/Summer/Julay |
|
August |
/Year/Summer/August |
|
September |
/Year/Autumn/September |
|
October |
/Year/Autumn/October |
|
November |
/Year/Autumn/November |
- SEARCH DEPTH FIRST BY : сначала потомки (child rows), потом предки (siblings), то есть идем вглубь иерархии и выстраиваем ветки - обходим граф (визуально см.выше).
Снова изменим иерархию - проставим второму уровню Autumn, который соотвествует временам года, parent_id равный ID сентября (September)
update table_tmp set parent_id=14 where id=13;
Получаем тот же результат - время года Autumn и его потомки, выпадут из иерархии.
Но если построить иерархию осени - Autumn, то есть where parent_id = 13, то реакция на появление цикла в рекурсивном WITH будет другая:
ORA-32044: cycle detected
while executing recursive WITH query
32044. 00000 - "cycle detected while executing recursive WITH
query"
*Cause: A recursive WITH clause query produced a cycle and
was stopped
in order to avoid
an infinite loop.
*Action: Rewrite the recursive WITH query to stop the recursion or
use
the CYCLE clause.
И, как видим, Oracle рекомендует использовать CYCLE - эквивалент NOCYCLE и CONNECT_BY_ISCYCLE.
with cte_year (id, tree, path, lvl) as
(select id, lpad(' ',1)||name as tree,'/'||name as path, 1 as lvl from table_tmp
where parent_id = 13
union all
select t.id, lpad(' ',3 * (lvl +1))||t.name, c.path||'/'||t.name as path, lvl + 1
from table_tmp t
join cte_year c on (t.parent_id = c.id)
)
CYCLE id SET cyclemark TO 1 DEFAULT 0
select id, tree, lvl, cyclemark from cte_year
В CYCLE указываем по какому столбцу надо пройтись в поиске ошибки (id), придумываем имя пвседосолбца (cyclemark), указываем каким символом отмаркировать (1), и какой будет по дефолту (0) для правильных строк.
Результат:
|
ID |
TREE |
|
14 |
September |
|
15 |
October |
|
16 |
November |
|
13 |
Autumn |
|
14 |
September |
|
15 |
October |
|
16 |
November |
Вернем иерархию в правильное положение.
update table_tmp set parent_id = 1 where id=13;
При использовании CONNECT BY были такие удобные фичи, как CONNECT_BY_ROOT, CONNECT_BY_ISLEAF и SYS_CONNECT_BY_PATH.
Рассмотрим следующий запрос.
with cte_year (id, tree, path, lvl, is_root) as
(select id,
lpad(' ',1)||name as tree,'/'||name as path, -- эквивалент CONNECT_BY_ROOT в анкорной части
1 as lvl,
id as is_root -- эквивалент CONNECT_BY_ROOT в анкорной части
from table_tmp
where parent_id is null
union all
select t.id,
lpad(' ',3 * (lvl +1))||t.name,
c.path||'/'||t.name as path, -- эквивалент CONNECT_BY_ROOT в рекурсивной части
lvl + 1,
c.is_root -- эквивалент CONNECT_BY_ROOT в рекурсивной части
from table_tmp t
join cte_year c on (t.parent_id = c.id)
)
search DEPTH FIRST BY id SET order_id
select id,
tree,
path, -- эквивалент CONNECT_BY_ROOT - результат рекурсивного запроса
lvl,
is_root, -- эквивалент CONNECT_BY_ROOT - результат рекурсивного запроса
case
when lead(lvl, 1, 1) over (order by order_id) <= lvl
then 1
else 0
end is_leaf -- эквивалент CONNECT_BY_ISLEAF
from cte_year
order by order_id
Небольшие примечания по рекурсивному WITH.
Следующие элементы недопустимы в определении CTE_query_definition рекурсивного элемента:
- SELECT DISTINCT
- GROUP BY
- PIVOT
- HAVING
- агрегатные функции (MIN, MAX, etc), но аналитические можно
- MODEL
- LEFT, RIGHT, OUTER JOIN (INNER JOIN допускается)
- вложенные запросы
В Oracle CTE является частью SELECT, к сожалению в отличие от MySQL INSERT, UPDATE, DELETE не работают с СTE. Конструкция ниже работать не будет:
with cte_year (id, tree, path, lvl) as
(select id, lpad(' ',1)||name as tree,'/'||name as path, 1 as lvl
from table_tmp
where parent_id is null
union all
select t.id, lpad(' ',3 * (lvl +1))||t.name, c.path||'/'||t.name as path, lvl + 1
from table_tmp t
join cte_year c on (t.parent_id = c.id)
)
update table_tmp tt -- ORA-00928: missing SELECT keyword
set tt.path = (select path from cte_year cc where id = cc.id)
Но можно использовать CTE с MERGE в части USING.
Улучшения, которые появились для СТЕ в Oracle 12c.
Создадим еще одну таблицу.
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS
SELECT 1 AS id
FROM dual
CONNECT BY level <= 1000000;
Functions in the WITH Clause
WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM t1
WHERE rownum = 1;
Результат:
WITH_FUNCTION(ID)
-----------------
1
Procedures in the WITH Clause
WITH
PROCEDURE with_procedure(p_id IN NUMBER) IS
BEGIN
DBMS_OUTPUT.put_line('p_id=' || p_id);
END;
SELECT id
FROM t1
WHERE rownum = 1;
Результат:
ID
----------
1
Пример вызова процедуры внутри функции внутри СТЕ...
WITH
PROCEDURE with_procedure(p_id IN NUMBER) IS
BEGIN
DBMS_OUTPUT.put_line('p_id=' || p_id);
END;
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
with_procedure(p_id);
RETURN p_id;
END;
SELECT with_function(id)
FROM t1
WHERE rownum = 1;
Результат:
WITH_FUNCTION(ID)
-----------------
1
Поддержка PL/SQL
Функция ниже не будет работать в PL/SQL.
BEGIN
FOR cur_rec IN (WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM t1
WHERE rownum = 1)
LOOP
NULL;
END LOOP;
END;
Получим ошибку:
ERROR at line 3:
ORA-06550: line 3, column 30:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 2, column 19:
PL/SQL: SQL Statement ignored
ORA-06550: line 5, column 34:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop
Использование динамического SQL поможет обойти это ограничение.
DECLARE
l_sql VARCHAR2(32767);
l_cursor SYS_REFCURSOR;
l_value NUMBER;
BEGIN
l_sql := 'WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM t1
WHERE rownum = 1';
OPEN l_cursor FOR l_sql;
FETCH l_cursor INTO l_value;
DBMS_OUTPUT.put_line('l_value=' || l_value);
CLOSE l_cursor;
END;
PL/SQL procedure successfully completed.
Результат DBMS_OUTPUT:
l_value=1
Повышение производительности
Создадим обычную функцию для сравнения.
CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
/
Запустим тест (см.ниже) и посмотрим на ожидаемое время (elapsed time) и CPU время в случае СТЕ и обычной функции.
DECLARE
l_time PLS_INTEGER;
l_cpu PLS_INTEGER;
l_sql VARCHAR2(32767);
l_cursor SYS_REFCURSOR;
TYPE t_tab IS TABLE OF NUMBER;
l_tab t_tab;
BEGIN
l_time := DBMS_UTILITY.get_time;
l_cpu := DBMS_UTILITY.get_cpu_time;
l_sql := 'WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM t1';
OPEN l_cursor FOR l_sql;
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;
DBMS_OUTPUT.put_line('WITH_FUNCTION : ' ||
'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
l_time := DBMS_UTILITY.get_time;
l_cpu := DBMS_UTILITY.get_cpu_time;
l_sql := 'SELECT normal_function(id)
FROM t1';
OPEN l_cursor FOR l_sql;
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;
DBMS_OUTPUT.put_line('NORMAL_FUNCTION: ' ||
'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
END;
WITH_FUNCTION : Time=45 hsecs CPU Time=39 hsecs
NORMAL_FUNCTION: Time=129 hsecs CPU Time=113 hsecs
PL/SQL procedure successfully completed.
SQL>
Результат: в три раза меньше.
PRAGMA UDF
В ряде презентаций перед официальным выпуском 12c выступавшие упоминали PRAGMA UDF (определяемую пользователем функцию), которая якобы дает преимущества в производительности встроенного PL/SQL, в то же время позволяя определять объект PL/SQL вне оператора SQL.
Переопределим функцию и добавим прагму.
CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
PRAGMA UDF;
BEGIN
RETURN p_id;
END;
/
После компиляции функции выполнение теста из раздела о повышении производительности дает довольно интересные результаты.
SET SERVEROUTPUT ON
DECLARE
l_time PLS_INTEGER;
l_cpu PLS_INTEGER;
l_sql VARCHAR2(32767);
l_cursor SYS_REFCURSOR;
TYPE t_tab IS TABLE OF NUMBER;
l_tab t_tab;
BEGIN
l_time := DBMS_UTILITY.get_time;
l_cpu := DBMS_UTILITY.get_cpu_time;
l_sql := 'WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM t1';
OPEN l_cursor FOR l_sql;
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;
DBMS_OUTPUT.put_line('WITH_FUNCTION : ' ||
'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
l_time := DBMS_UTILITY.get_time;
l_cpu := DBMS_UTILITY.get_cpu_time;
l_sql := 'SELECT normal_function(id)
FROM t1';
OPEN l_cursor FOR l_sql;
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;
DBMS_OUTPUT.put_line('NORMAL_FUNCTION: ' ||
'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
END;
/
PL/SQL procedure successfully completed.
Результат:
WITH_FUNCTION : Time=44 hsecs CPU Time=40 hsecs
NORMAL_FUNCTION: Time=33 hsecs CPU Time=29 hsecs
PL/SQL procedure successfully completed.
Автономная (standalone) функция, использующая PRAGMA
UDF, кажется, последовательно выполняет встроенную функцию.
Функцию с PRAGMA UDF можно вызывать из PL/SQL, это поддерживается.
DECLARE
l_number NUMBER;
BEGIN
l_number := normal_function(1);
END;
PL/SQL procedure successfully completed.
WITH_PLSQL Hint
Если запрос, содержащий раздел объявления PL/SQL, не является запросом верхнего уровня, запрос верхнего уровня должен включать подсказку /*+WITH_PLSQL*/. Без этой подсказки компиляция выдаст ошибку.
UPDATE t1 a
SET a.id = (WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(a.id)
FROM dual)
/
Результат - ошибка:
SET a.id = (WITH
*
ERROR at line 2:
ORA-32034: unsupported use of WITH clause
Добавление подсказки WITH_PLSQL позволяет оператору компилироваться и запускаться должным образом.
UPDATE /*+ WITH_PLSQL */ t1 a
SET a.id = (WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(a.id)
FROM dual)
/
1000000 rows updated.
SQL>
Хинт можно применять и в подзапросах, и DML.
DETERMINISTIC Hint
Как указал здесь Джонатан Льюис, использование детерменированных функций в WITH не оптимизируется. Поэтому использовать их надо, внимательно анализируя план запроса и время выполнения, или не использовать вообще.
SET TIMING ON ARRAYSIZE 15
WITH
FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS
BEGIN
DBMS_LOCK.sleep(1);
RETURN p_id;
END;
SELECT slow_function(id)
FROM t1
WHERE ROWNUM <= 10;
/
SLOW_FUNCTION(ID)
-----------------
1
1
1
1
1
1
1
1
1
1
10 rows selected.
Elapsed: 00:00:10.07
SQL>
Отсуствие оптимизации было исправлено в 18c.
Результат этого теста на 18с - до 1 секунды.
Elapsed: 00:00:01.071
Scalar Subquery Caching
В скалярных подзапросах вызов детерминированной функции ведет себя очень прилично - время до 1 секунды, то есть кэширование происходит.
SET TIMING ON ARRAYSIZE 15
WITH
FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS
BEGIN
DBMS_LOCK.sleep(1);
RETURN p_id;
END;
SELECT (SELECT slow_function(id) FROM dual)
FROM t1
WHERE ROWNUM <= 10;
/
(SELECTSLOW_FUNCTION(ID)FROMDUAL)
---------------------------------
1
1
1
1
1
1
1
1
1
1
10 rows selected.
Elapsed: 00:00:01.04
SQL>
На этом пока все. Спасибо за внимание!
Источники:
Recursive Subquery Factoring : Hierarchical Queries Using Recursive WITH Clauses
Проблемы материализации подзапросов в рамках глобальной транзакции
Игра в прятки с оптимизатором. Гейм овер, это CTE PostgreSQL 12
WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1)
0 Комментарии