## 7 полезных приёмов для Oracle SQL

Простые советы для Oracle SQL, которые помогут аналитикам данных прокачать свои запросы.

1) Фильтрованные (partial) индексы
В Oracle можно создавать индексы только для подмножества строк, чтобы ускорить выборку по популярным условиям.

CREATE INDEX idx_orders_high_value
  ON orders(order_date)
  WHERE total_amount > 1000;

CREATE INDEX idx_orders_year
  ON orders (EXTRACT(YEAR FROM order_date));

SELECT region, category, SUM(sales) AS total
FROM sales
GROUP BY ROLLUP (region, category);

CREATE MATERIALIZED VIEW mv_sales_by_month
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT TRUNC(order_date, 'MM') AS month, SUM(total_amount) AS total
FROM orders
GROUP BY TRUNC(order_date, 'MM');

CREATE OR REPLACE FUNCTION get_tax_rate(p_region VARCHAR2)
RETURN NUMBER RESULT_CACHE RELIES_ON (tax_table) IS
  v_rate NUMBER;
BEGIN
  SELECT rate INTO v_rate FROM tax_table WHERE region = p_region;
  RETURN v_rate;
END;

SELECT /*+ PARALLEL(orders, 4) */ customer_id, SUM(total_amount)
FROM orders
GROUP BY customer_id;

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'ORDERS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

Data Science. SQL hub

## 7 полезных приёмов для Oracle SQL

Простые советы для Oracle SQL, которые помогут аналитикам данных прокачать свои запросы.

1) Фильтрованные (partial) индексы
В Oracle можно создавать индексы только для подмножества строк, чтобы ускорить выборку по популярным условиям.

CREATE INDEX idx_orders_high_value
  ON orders(order_date)
  WHERE total_amount > 1000;

2) Функциональные (function-based) индексы
Если фильтруете или джойните по функции, создайте индекс прямо по выражению:

CREATE INDEX idx_orders_year
  ON orders (EXTRACT(YEAR FROM order_date));

3) GROUPING SETS, ROLLUP, CUBE
Для одновременной агрегации по нескольким группировкам без UNION ALL:

SELECT region, category, SUM(sales) AS total
FROM sales
GROUP BY ROLLUP (region, category);

4) Материализованные представления с QUERY REWRITE
В Oracle можно сделать автоматическую подмену сложного запроса предрасчитанным результатом (материализованным представлением):

CREATE MATERIALIZED VIEW mv_sales_by_month
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT TRUNC(order_date, 'MM') AS month, SUM(total_amount) AS total
FROM orders
GROUP BY TRUNC(order_date, 'MM');

Теперь запрос SELECT month, SUM(total_amount) FROM orders GROUP BY month; автоматически будет использовать mv_sales_by_month.

5) WITH PL/SQL FUNCTION RESULT CACHE
Кэшируйте результат функции, чтобы при одинаковых входных данных не пересчитывать:

CREATE OR REPLACE FUNCTION get_tax_rate(p_region VARCHAR2)
RETURN NUMBER RESULT_CACHE RELIES_ON (tax_table) IS
  v_rate NUMBER;
BEGIN
  SELECT rate INTO v_rate FROM tax_table WHERE region = p_region;
  RETURN v_rate;
END;

6) PARALLEL HINT для ускорения запросов
Явно указывайте параллельное выполнение запроса, чтобы задействовать несколько процессов:

SELECT /*+ PARALLEL(orders, 4) */ customer_id, SUM(total_amount)
FROM orders
GROUP BY customer_id;

7) DBMS_STATS.AUTO_SAMPLE_SIZE для сбора статистики
Используйте автоматический подбор размера выборки для более точной оптимизации плана выполнения:

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'ORDERS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

• Совет: проверяйте планы выполнения через DBMS_XPLAN.DISPLAY_CURSOR, чтобы видеть реальные шаги запроса, а не только предполагаемые.

@sqlhub

hottg.com/sqlhub/1869

4.9K viewsMay 4 at 19:05