← Каталог
Подсказки оптимизатору (query hints) — Управление порядком выполнения
Фрагмент из «Подсказки оптимизатору (query hints)»: Управление порядком выполнения.
-- Указание порядка соединения таблиц
SELECT /*+ LEADING(c o oi p) */
c.customer_name,
p.product_name,
SUM(oi.quantity) AS total_quantity,
SUM(oi.quantity * oi.unit_price) AS total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
GROUP BY c.customer_name, p.product_name
HAVING SUM(oi.quantity) > 10
ORDER BY total_amount DESC;-- Указание порядка соединения таблиц
SELECT /*+ LEADING(c o oi p) */
c.customer_name,
p.product_name,
SUM(oi.quantity) AS total_quantity,
SUM(oi.quantity * oi.unit_price) AS total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
GROUP BY c.customer_name, p.product_name
HAVING SUM(oi.quantity) > 10
ORDER BY total_amount DESC;-- Оптимизация запроса к звездообразной схеме
SELECT /*+ STAR */
t.time_year,
p.product_category,
c.customer_region,
SUM(f.sales_amount) AS total_sales,
COUNT(f.sale_id) AS transaction_count
FROM sales_facts f
JOIN time_dimension t ON f.time_id = t.time_id
JOIN product_dimension p ON f.product_id = p.product_id
JOIN customer_dimension c ON f.customer_id = c.customer_id
WHERE t.time_year = 2025
GROUP BY t.time_year, p.product_category, c.customer_region
ORDER BY total_sales DESC;-- Оптимизация запроса к звездообразной схеме
SELECT /*+ STAR */
t.time_year,
p.product_category,
c.customer_region,
SUM(f.sales_amount) AS total_sales,
COUNT(f.sale_id) AS transaction_count
FROM sales_facts f
JOIN time_dimension t ON f.time_id = t.time_id
JOIN product_dimension p ON f.product_id = p.product_id
JOIN customer_dimension c ON f.customer_id = c.customer_id
WHERE t.time_year = 2025
GROUP BY t.time_year, p.product_category, c.customer_region
ORDER BY total_sales DESC;-- Явное указание таблиц фактов и измерений
SELECT /*+ FACT(f) DIMENSION(t p c) */
t.time_quarter,
p.product_name,
SUM(f.quantity) AS total_quantity,
SUM(f.sales_amount) AS total_sales
FROM sales_facts f
JOIN time_dimension t ON f.time_id = t.time_id
JOIN product_dimension p ON f.product_id = p.product_id
JOIN customer_dimension c ON f.customer_id = c.customer_id
WHERE t.time_year = 2025
AND c.customer_region = 'North America'
GROUP BY t.time_quarter, p.product_name
HAVING SUM(f.sales_amount) > 10000
ORDER BY total_sales DESC;-- Явное указание таблиц фактов и измерений
SELECT /*+ FACT(f) DIMENSION(t p c) */
t.time_quarter,
p.product_name,
SUM(f.quantity) AS total_quantity,
SUM(f.sales_amount) AS total_sales
FROM sales_facts f
JOIN time_dimension t ON f.time_id = t.time_id
JOIN product_dimension p ON f.product_id = p.product_id
JOIN customer_dimension c ON f.customer_id = c.customer_id
WHERE t.time_year = 2025
AND c.customer_region = 'North America'
GROUP BY t.time_quarter, p.product_name
HAVING SUM(f.sales_amount) > 10000
ORDER BY total_sales DESC;