EXPLAIN PLAN в Oracle SQL — как оптимизировать запросы

EXPLAIN PLAN в Oracle SQL. Введение

EXPLAIN PLAN — мощный инструмент в Oracle SQL, который позволяет разработчикам и администраторам понять, как именно Oracle будет выполнять SQL-запрос. Он помогает выявлять потенциальные узкие места, неправильное использование индексов и неоптимальные пути выполнения. В этой статье мы покажем 50 практических примеров использования EXPLAIN PLAN, чтобы ты мог быстро и эффективно анализировать свои запросы.

🖋️ Синтаксис EXPLAIN PLAN

EXPLAIN PLAN FOR
[твой SQL-запрос];

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR подготавливает план выполнения запроса
DBMS_XPLAN.DISPLAY отображает результат анализа
План сохраняется во внутренней таблице PLAN_TABLE

🔄 Где используется EXPLAIN PLAN в Oracle SQL

Анализ и оптимизация медленных запросов
Проверка использования индексов
Подбор оптимальной стратегии соединений (Nested Loop, Hash Join)
Подготовка к настройке статистики и хинтов
Сравнение планов до и после изменений

📊 40 примеров использования EXPLAIN PLAN

Простой SELECT:

EXPLAIN PLAN FOR SELECT * FROM employees;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с WHERE:

EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с JOIN:

EXPLAIN PLAN FOR
SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с индексом:

CREATE INDEX emp_salary_idx ON employees(salary);
EXPLAIN PLAN FOR SELECT * FROM employees WHERE salary > 5000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с ORDER BY:

EXPLAIN PLAN FOR SELECT * FROM employees ORDER BY hire_date DESC;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с подзапросом:

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с функцией:

EXPLAIN PLAN FOR SELECT UPPER(first_name) FROM employees;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с агрегатной функцией:

EXPLAIN PLAN FOR SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с хинтом:

EXPLAIN PLAN FOR SELECT /*+ FULL(employees) */ * FROM employees;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с фильтрацией по дате:

EXPLAIN PLAN FOR SELECT * FROM employees WHERE hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с FETCH FIRST:

EXPLAIN PLAN FOR SELECT * FROM employees FETCH FIRST 10 ROWS ONLY;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с ROWNUM:

EXPLAIN PLAN FOR SELECT * FROM employees WHERE ROWNUM <= 5;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с CASE:

EXPLAIN PLAN FOR SELECT first_name, CASE WHEN salary > 5000 THEN 'High' ELSE 'Low' END FROM employees;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с аналитической функцией:

EXPLAIN PLAN FOR SELECT first_name, RANK() OVER (ORDER BY salary DESC) FROM employees;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с PARTITION:

EXPLAIN PLAN FOR SELECT * FROM sales PARTITION (sales_2024);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с группировкой по дате:

EXPLAIN PLAN FOR SELECT TO_CHAR(hire_date, 'YYYY-MM') AS ym, COUNT(*) FROM employees GROUP BY TO_CHAR(hire_date, 'YYYY-MM');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с вложенными подзапросами:

EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = (SELECT location_id FROM locations WHERE city = 'London'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с MATERIALIZED VIEW:

EXPLAIN PLAN FOR SELECT * FROM mv_recent_sales;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с UNION:

EXPLAIN PLAN FOR SELECT first_name FROM employees UNION SELECT department_name FROM departments;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с MINUS:

EXPLAIN PLAN FOR SELECT department_id FROM employees MINUS SELECT department_id FROM departments;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с INTERSECT:

EXPLAIN PLAN FOR SELECT department_id FROM employees INTERSECT SELECT department_id FROM departments;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с JOIN и WHERE:

EXPLAIN PLAN FOR SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.location_id = 1700;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с фильтрацией NULL:

EXPLAIN PLAN FOR SELECT * FROM employees WHERE commission_pct IS NULL;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с MULTI-TABLE INSERT:

EXPLAIN PLAN FOR INSERT ALL WHEN department_id = 10 THEN INTO hr_dept WHEN department_id = 20 THEN INTO sales_dept SELECT * FROM employees;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT из внешней таблицы:

EXPLAIN PLAN FOR SELECT * FROM external_employees;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с хинтом INDEX:

EXPLAIN PLAN FOR SELECT /*+ INDEX(employees emp_dept_ix) */ * FROM employees WHERE department_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с JSON_TABLE:

EXPLAIN PLAN FOR SELECT * FROM JSON_TABLE('[{"id":1}]', '$[*]' COLUMNS(id NUMBER PATH '$.id'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с VIEW:

EXPLAIN PLAN FOR SELECT * FROM emp_view;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с временной таблицы:

EXPLAIN PLAN FOR SELECT * FROM session_temp_data;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с FULL OUTER JOIN:

EXPLAIN PLAN FOR SELECT * FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.department_id;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT из DUAL:

EXPLAIN PLAN FOR SELECT 'Hello' FROM dual;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с синтетическим полем:

EXPLAIN PLAN FOR SELECT first_name || ' ' || last_name AS full_name FROM employees;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с TO_DATE:

EXPLAIN PLAN FOR SELECT * FROM employees WHERE hire_date > TO_DATE('2023-01-01', 'YYYY-MM-DD');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с SYSDATE:

EXPLAIN PLAN FOR SELECT * FROM employees WHERE hire_date < SYSDATE;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с TO_CHAR:

EXPLAIN PLAN FOR SELECT TO_CHAR(hire_date, 'YYYY-MM-DD') FROM employees;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с аналитикой RANK:

EXPLAIN PLAN FOR SELECT employee_id, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) FROM employees;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с DENSE_RANK:

EXPLAIN PLAN FOR SELECT employee_id, DENSE_RANK() OVER (ORDER BY hire_date ASC) FROM employees;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с GROUP BY ROLLUP:

EXPLAIN PLAN FOR SELECT department_id, COUNT(*) FROM employees GROUP BY ROLLUP(department_id);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с CUBE:

EXPLAIN PLAN FOR SELECT department_id, job_id, COUNT(*) FROM employees GROUP BY CUBE(department_id, job_id);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT с CTE (WITH):

EXPLAIN PLAN FOR
WITH recent AS (
  SELECT * FROM employees WHERE hire_date > TO_DATE('2022-01-01', 'YYYY-MM-DD')
)
SELECT * FROM recent;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

🧩 Заключение

EXPLAIN PLAN — незаменимый инструмент для тех, кто хочет писать не просто работающий, а оптимизированный SQL. Это как заглянуть под капот автомобиля, чтобы убедиться, что всё работает эффективно.

💡 Запомни:

Используется перед SELECT, INSERT, DELETE, UPDATE
План виден через DBMS_XPLAN.DISPLAY
Помогает понять, где запрос «тормозит»
Работает без выполнения запроса — только анализ