ビューに対するSQLで、ヒントを指定する

はじめに

ビューに対するSQLで、ヒントを指定したいことがある。そのような場合、グローバル・ヒントという構文を使うことができる。その内容を説明する。

なお、以下の説明で使用するSQLは、全て『Oracle Database パフォーマンス・チューニング・ガイド 10gリリース2（10.2）』からの引用である。

ビューの定義

例えば、以下のように定義したビューがあるとする。

CREATE OR REPLACE VIEW v AS
SELECT
    e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1,
        ( SELECT *
          FROM employees e3) e2, job_history j
WHERE e1.employee_id = e2.manager_id
     AND e1.employee_id = j.employee_id
     AND e1.hire_date = j.start_date
     AND e1.salary = ( SELECT
                       max(e2.salary)
                       FROM employees e2
                       WHERE e2.department_id = e1.department_id)
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal

ヒントを指定したい、ビューに対するSQL

このビューに対し、以下のようなSELECT文を発行するとする。

SELECT *
  FROM v

グローバル・ヒントの指定方法

このSELECT文で、ビュー定義のSQL内のe3（employeesテーブルの別名）に対し、ヒントを指定したい場合の書き方は、以下のとおり。以下のいずれもが同じ意味となる。私が使うとしたら1つ目を使う。よって2つ目、および3つ目は未調査であり、詳細は不明。

SELECT /*+ INDEX(v.e2.e3 emp_job_ix) */  *
  FROM v;

SELECT /*+ INDEX(@SEL$2 e2.e3 emp_job_ix) */ *
  FROM v;

SELECT /*+ INDEX(@SEL$3 e3 emp_job_ix) */ *
  FROM v;

注意

説明の前提

以上の説明は、ビューの定義内でヒントを指定することは、できるだけ避けたいという大前提の下に行っている。
例えば、以下のようなビュー定義は避けたいということである。

CREATE OR REPLACE VIEW v AS
SELECT
    e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1,
        ( SELECT /*+ INDEX(e3, emp_job_ix) */ *
          FROM employees e3) e2, job_history j
WHERE e1.employee_id = e2.manager_id
     AND e1.employee_id = j.employee_id
     AND e1.hire_date = j.start_date
     AND e1.salary = ( SELECT
                       max(e2.salary)
                       FROM employees e2
                       WHERE e2.department_id = e1.department_id)
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal

それは、ビューでは、そのビューを使う側のSQLの内容が決まって初めて、発行されるSQLの全体が決まるため。例えば、以下のようなSQLでは、ビューに対する絞り込みの内容が決まって初めて、使われるべきインデックスなど、あるべき実行計画が決まるため。

SELECT 〜 FROM <ビュー> WHERE <ビューに対する絞り込み>

Oracleも、ビュー定義内でヒントを指定するのではなく、グローバル・ヒントを使うことを推奨している。

FROM句内にビューしかないときにしか使えない？

グローバル・ヒントは、FROM句内にグローバル・ヒントを使うビューしかない場合にしか使えない*1。このあたりの、「ビューの内側のアクセス・パスとヒント結合」の、「インライン・ビューでないビューでは〜」の記述が、それを示したドキュメントになるだろうか…*2。

例えば上記の例で、以下のようにしてもヒントは有効でない。なぜなら、以下のSQLでは、グローバル・ヒントを指定したSQLのFROM句内に、グローバル・ヒントを指定したビューであるv以外にも、employee_familiesが含まれているためだ。

SELECT
  /*+ INDEX(v.e2.e3 emp_job_ix) */
  *
FROM
  v JOIN
  employee_families f ON (v.last_name = f.last_name);

かと思えば、このSQLを以下のように変えると、グローバル・ヒントが有効になったかもしれない。ホントによくわからない…。時間があるときに調べてみよう…。

SELECT
  /*+ INDEX(v.e2.e3 emp_job_ix) */
  *
FROM
  v,
  employee_families f
WHERE
  v.last_name = f.last_name;

*1:はず…

*2:自信なさすぎ。間違ってたら誰か突っ込んでください

atsukanrockのブログ

Microsoft系技術を中心にぼちぼち更新します