Именованные подзапросы в Oracle: WITH clause

Помимо обычных inline подзапросов вида «(select * from some_table ) a» Oracle позволяет отдельное описание подзапросов с использованием оператора WITH. Общий синтаксис следующий:

WITH псевдоним_1 as (текст_подзапроса),

псевдоним_2 as (текст_второго подзапроса),

.....

псевдоним_n as (текст_N подзапроса)

select * from ...
/*любая комбинация таблиц и именованных подзапросов*/

Например:

with set_1 as (
select 1 as id, 'first' as value from dual
union select 2 as id, 'second' as value from dual
union select 3 as id, 'third' as value from dual)
select * from set_1

В чем преимущество такого подхода?

• Устранение дублирования кода. Единожды определенный таким образом подзапрос может использоваться неоднократно, в разных частях запроса.
• Oracle может, если сочтет это выгодным, автоматически сохранить результаты подзапроса в глобальной временной таблицы и выполнить запрос только один раз.
• Подзапросы в WITH могут использовать другие подзапросы. Так можно разбить сложную конструкцию на простые и понятные блоки.

Пример более сложного запроса, иллюстрирующий упрощение кода и использование подзапросами друг друга:

with customers as (
select 1 as customer_id, 'first' as v_name from dual
union select 2, 'second' from dual
union select 3, 'third' from dual),
orders as (
select 1 as order_id, 1 as customer_id,
sysdate as dt_complete from dual
union select 2, 2, null  from dual
union select 3 , 3 , sysdate - INTERVAL '1' DAY from dual
),
completed_orders as (
select * from orders
where dt_complete is not null)
select c.customer_id, c.v_name,
count(o.order_id), count(io.order_id)
from customers c
left join completed_orders o
on c.customer_id = o.customer_id
left join orders io
on c.customer_id = io.customer_id
group by c.customer_id, c.v_name order by 2, 1

Также существуют недокументированные возможности по управлению сохранением промежуточных данных такими подзапросами хинтами MATERIALIZE и  INLINE:

set feedback only
with set_1 as (
select 1 as id, 'first' as value from dual
union select 2 as id, 'second' as value from dual
union select 3 as id, 'third' as value from dual),
set_2 as (select /*+ materialize */ * from set_1)
select * from set_2;
set feedback on
select * from table(dbms_xplan.display_cursor);

/*

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11 (100)| |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | VIEW | | 3 | 24 | 9 (34)| 00:00:01 |
| 4 | SORT UNIQUE | | 3 | | 9 (78)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 9 | VIEW | | 3 | 24 | 2 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6603_5B6FA | 3 | 24 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

*/

set feedback only
with set_1 as (
select 1 as id, 'first' as value from dual
union select 2 as id, 'second' as value from dual
union select 3 as id, 'third' as value from dual),
set_2 as (select /*+ materialize */ * from set_1)
select * from set_2;
set feedback on
select * from table(dbms_xplan.display_cursor);

/*

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | VIEW | | 3 | 24 | 9 (34)| 00:00:01 |
| 2 | SORT UNIQUE | | 3 | | 9 (78)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |

*/

Разумеется, все подсказки оптимизитору, особенно недокументированные, должны использоваться так редко, как это возможно.

Кроме этого, в 12R1 появилась возможность описывать в блоке WITH функции и процедуры на PL/SQL, что в некоторых случаях позволяет ускорить выполнение запроса по сравнению с обычными функциями.

Пример использования функций в WITH-блоке:

WITH
FUNCTION test_t(pID IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN pID;
END;
SELECT test_t(id)
FROM dual
/