【Oracle】SQLヒント句の使い方完全ガイド｜FULL・INDEX・USE_NL・USE_HASH・PARALLEL・実行計画の制御方法

SELECT /*+ FULL(e) */ employee_id, name, salary
FROM employees e
WHERE department_id = 10;
-- employees テーブルにフルテーブルスキャンを強制する

SELECT /*+ FULL(e) */ employee_id, name
FROM employees e
WHERE department_id = 10;
-- インデックスがあっても無視し、テーブル全体をスキャンする
-- 大量の行を取得する場合にフルスキャンが有利なケースで使う

-- インデックスを1つ指定
SELECT /*+ INDEX(e idx_emp_dept) */ employee_id, name
FROM employees e
WHERE department_id = 10;

-- インデックスを複数候補として指定（オプティマイザが最適なものを選ぶ）
SELECT /*+ INDEX(e idx_emp_dept idx_emp_name) */ employee_id, name
FROM employees e
WHERE department_id = 10 AND name LIKE 'A%';

-- インデックス名を省略するとオプティマイザが任意のインデックスを選ぶ
SELECT /*+ INDEX(e) */ employee_id, name
FROM employees e
WHERE department_id = 10;

SELECT /*+ INDEX_FFS(e idx_emp_dept) */ department_id, COUNT(*)
FROM employees e
GROUP BY department_id;
-- テーブルにアクセスせずインデックスだけをスキャンする
-- SELECT する列がすべてインデックスに含まれている場合に有効

SELECT /*+ NO_INDEX(e idx_emp_dept) */ employee_id, name
FROM employees e
WHERE department_id = 10;
-- オプティマイザが idx_emp_dept を選ばないようにする
-- 他のインデックスがあればそちらが選ばれ、なければフルスキャンになる

SELECT /*+ USE_NL(d e) */ e.employee_id, e.name, d.department_name
FROM departments d
JOIN employees e ON e.department_id = d.department_id
WHERE d.department_id = 10;
-- 駆動表(d)の各行に対し、被駆動表(e)をインデックスでルックアップする
-- 少量行×インデックスありの結合に有利

SELECT /*+ USE_HASH(e d) */ e.employee_id, e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
-- 小さいテーブル(d)のハッシュテーブルをメモリに構築し、大きいテーブル(e)をスキャンして結合する
-- 等値結合（=）で両テーブルとも大量データの場合に有利

SELECT /*+ USE_MERGE(e d) */ e.employee_id, e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY e.department_id;
-- 両テーブルをソートしてからマージする
-- 結果をソート順で取得したい場合や、ハッシュ結合のメモリが足りない場合に使う

SELECT /*+ LEADING(d e s) USE_NL(e) USE_HASH(s) */
    e.employee_id, e.name, d.department_name, s.salary_grade
FROM departments d
JOIN employees e ON e.department_id = d.department_id
JOIN salary_grades s ON e.salary BETWEEN s.low_sal AND s.high_sal
WHERE d.location_id = 1700;
-- d → e → s の順で結合する
-- LEADING は最もよく使われるJOIN順序ヒント

SELECT /*+ ORDERED USE_NL(e) USE_HASH(s) */
    e.employee_id, e.name, d.department_name
FROM departments d, employees e, salary_grades s
WHERE e.department_id = d.department_id
  AND e.salary BETWEEN s.low_sal AND s.high_sal
  AND d.location_id = 1700;
-- FROM 句に書いた順序（d → e → s）で結合する
-- LEADING のほうが柔軟なので、新規SQLでは LEADING を推奨

-- 並列度 4 で SELECT を実行する
SELECT /*+ PARALLEL(e, 4) */ department_id, SUM(salary)
FROM employees e
GROUP BY department_id;

-- テーブルのデフォルト並列度を使う（テーブル定義に依存）
SELECT /*+ PARALLEL(e) */ department_id, SUM(salary)
FROM employees e
GROUP BY department_id;

-- 並列 INSERT
INSERT /*+ PARALLEL(t, 4) APPEND */ INTO target_table t
SELECT * FROM source_table;

-- テーブルにデフォルト並列度が設定されていても直列で実行する
SELECT /*+ NO_PARALLEL(e) */ employee_id, name
FROM employees e
WHERE department_id = 10;

-- INSERT ... SELECT でダイレクトパスロードを使う
INSERT /*+ APPEND */ INTO target_table
SELECT * FROM source_table;
COMMIT;
-- ハイウォーターマーク（HWM）の先にデータを追加するため高速
-- 既存の空き領域は再利用されない

-- VALUES句でのダイレクトパスロード（Oracle 11g R2+）
INSERT /*+ APPEND_VALUES */ INTO target_table
VALUES (1, 'test', SYSDATE);

SELECT /*+ NO_MERGE(v) */ v.department_id, v.total_salary
FROM (
    SELECT department_id, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department_id
) v
WHERE v.total_salary > 1000000;
-- サブクエリをマージ（展開）せず、独立した処理単位として実行させる
-- オプティマイザが展開すると遅くなるケースで使う

-- サブクエリを可能な限り早いタイミングで評価する
SELECT /*+ PUSH_SUBQ(@sq) */ e.employee_id, e.name
FROM employees e
WHERE e.department_id IN (
    SELECT /*+ QB_NAME(sq) */ d.department_id
    FROM departments d
    WHERE d.location_id = 1700
);

-- サブクエリの評価を後回しにする
SELECT /*+ NO_PUSH_SUBQ(@sq) */ e.employee_id, e.name
FROM employees e
WHERE e.department_id IN (
    SELECT /*+ QB_NAME(sq) */ d.department_id
    FROM departments d
    WHERE d.location_id = 1700
);

-- サブクエリをJOINに変換する（アンネスト）
SELECT e.employee_id, e.name
FROM employees e
WHERE e.department_id IN (
    SELECT /*+ UNNEST */ d.department_id
    FROM departments d
    WHERE d.location_id = 1700
);

-- サブクエリをJOINに変換させない
SELECT e.employee_id, e.name
FROM employees e
WHERE e.department_id IN (
    SELECT /*+ NO_UNNEST */ d.department_id
    FROM departments d
    WHERE d.location_id = 1700
);

-- ページネーション向け：最初の20行を素早く返す
SELECT /*+ FIRST_ROWS(20) */ employee_id, name, salary
FROM employees
WHERE department_id = 10
ORDER BY salary DESC;

-- バッチ処理向け：全体スループットを優先する
SELECT /*+ ALL_ROWS */ department_id, SUM(salary)
FROM employees
GROUP BY department_id;

EXPLAIN PLAN FOR
SELECT /*+ FULL(e) */ employee_id, name
FROM employees e
WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Operation に「TABLE ACCESS FULL」が表示されればヒントが効いている
-- 「INDEX RANGE SCAN」等が表示されればヒントが無視されている