Друзья, всем привет! 👋🏻
На связи Паша, ваш куратор и специалист в мире Oracle.
В понедельник мы предложили вам подумать, как оптимизатор Oracle воспринимает конструкцию WITH, и как мы можем этим управлять. Сегодня поделимся нашим мнением и примерами. Итак, поехали 🤞🏻
📌 Для чего нужен WITH ❓
В Oracle WITH — прежде всего способ "разрезать" запрос на несколько слоёв. Cам по себе CTE не гарантирует, что подзапрос выполнится один раз и результат сохранится в Temp.
⚙️ 1. CTE в плане запроса
Оптимизатор при работе с CTE может:
• "раскрыть скобки" (inline) — подставить тело CTE в основном запросе;
• материализовать — выполнить подзапрос один раз, сохранить промежуточный результат и использовать его.
При выполнении запроса оптимизатор отталкивается от актуальной статистики. Поэтому в одном случае дешевле может быть материализовать результат запроса, а в другом — раскрыть скобки.
⚠️ Важно: Если CTE материализован, в плане запроса Вы увидите шаг VIEW/TEMP TABLE TRANSFORMATION.
🤔 2. Когда использовать inline, а когда материализацию
Например, в нашем исходном запросе
WITH top_orders AS (
SELECT o.*
FROM oe.orders o
WHERE o.order_date >= DATE '2024-01-01'
)
SELECT c.cust_last_name,
(SELECT COUNT(*)
FROM top_orders t
WHERE t.customer_id = c.customer_id) cnt_orders,
(SELECT SUM(t.order_total)
FROM top_orders t
WHERE t.customer_id = c.customer_id) sum_amount
FROM oe.customers c;
таблица orders большая. Тогда логично:
• один раз отфильтровать данные в top_orders,
• потом использовать полученный результат для COUNT и SUM.
Если же CTE лёгкий, материализация даст лишнюю нагрузку на диск. В этом случае лучше позволить inline’ить.
🛠 3. Управление поведением: MATERIALIZE и INLINE
Почему вообще возникает задача управлять CTE?
• Статистика может быть неактуальной, следовательно, оптимизатор может ошибиться;
• Стоимость материализации может быть слишком высокой (из-за I/O).
В таких случаях у разработчика Oracle есть возможность применить следующие хинты:
• /*+ MATERIALIZE */ — попросить материализовать CTE;
• /*+ INLINE */ — попросить inline’ить CTE.
Пример с материализацией:
WITH top_orders AS (
SELECT /*+ MATERIALIZE */
o.*
FROM oe.orders o
WHERE o.order_date >= DATE '2024-01-01'
)
SELECT c.cust_last_name,
(SELECT COUNT(*)
FROM top_orders t
WHERE t.customer_id = c.customer_id) cnt_orders,
(SELECT SUM(t.order_total)
FROM top_orders t
WHERE t.customer_id = c.customer_id) sum_amount
FROM oe.customers c;
Здесь мы явно говорим оптимизатору: сначала посчитай top_orders, материализуй, потом используй результат.
Пример с инлайном:
WITH small_dict AS (
SELECT /*+ INLINE */
d.*
FROM dict_table d
WHERE d.type = 'X'
)
SELECT ...
FROM small_dict s
JOIN other_table o ON ...
🏁 4. Итоги
• WITH в Oracle не гарантирует однократного выполнения CTE.
• Без хинтов Oracle примет решение, опираясь на статистику.
• Если подзапрос тяжёлый и/или используется несколько раз — стоит применить /*+ MATERIALIZE */.
• В остальных случаях WITH удобен для читабельности. Важно помнить, что производительность запроса зависит не только от наличия CTE.
• План запроса однозначно показывает, раскрыл оптимизатор скобки CTE или материализовал его.
На этом всё, надеюсь, было интересно. Делитесь своими примерами в комментариях к посту.
До новых встреч!
#oracle #backendpro #sql #оптимизация #Pavel_Dolganov
Канал Oracle Developer | Чатик 💬
Мини-курс Оптимизация: Быстрый старт 🚀
Анкета предзаписи на 7 поток "Оптимизация Oracle SQL"
📱 Facebook 📱 YouTube 📱 ВКонтакте 📱 LinkedIn 📱 Threads RUTUBE
