Подсказки оптимизатору (query hints) — Поддержка в различных СУБД

-- Пример подсказок в SQL Server
SELECT 
    c.CustomerName,
    o.OrderDate,
    od.Quantity,
    od.UnitPrice,
    p.ProductName
FROM Customers c WITH (INDEX(IX_Customers_CustomerName))
JOIN Orders o WITH (FORCESEEK) ON c.CustomerID = o.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
WHERE o.OrderDate >= DATEADD(MONTH, -6, GETDATE)
  AND c.Country = 'USA'
ORDER BY o.OrderDate DESC
OPTION (HASH JOIN, FAST 1000);

-- Пример подсказок в MySQL
SELECT /*+ 
    BKA(o) 
    NO_RANGE_OPTIMIZATION(o PRIMARY) 
    JOIN_ORDER(c, o, od, p) 
    SET_VAR(optimizer_switch='index_merge=off')
*/ 
    c.customer_name,
    o.order_date,
    od.quantity,
    od.unit_price,
    p.product_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id
WHERE o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
  AND c.country = 'USA'
ORDER BY o.order_date DESC;

-- Пример подсказок в PostgreSQL с расширением pg_hint_plan
SELECT /*+ 
    HashJoin(e d) 
    SeqScan(e) 
    IndexScan(d dept_location_idx) 
    NestLoop(d l)
*/ 
    e.employee_id,
    e.employee_name,
    d.department_name,
    l.location_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE e.hire_date >= CURRENT_DATE - INTERVAL '1 year'
  AND l.country_id = 'US'
ORDER BY e.salary DESC;