1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@fox_tail

SQLで「なぜこの順序で返るのか」を調べる方法

1
Posted at

はじめに

Oracleで ORDER BY を付けずにSELECTしたとき、結果がある順序で並んでいるように見えることがある。しかしこの順序はSQL標準上もOracle上も保証されていない。たまたまそう見えているだけで、実行計画やデータの物理配置といった内部事情の副作用にすぎない。

この記事では「なぜ今この順序で返っているのか」を調べる手順をまとめる。目的は順序の根拠を理解することであって、その順序に依存してよいという話ではない点に注意してほしい。表示順を確定させたいなら、調査結果がどうであれ ORDER BY を明示するのが唯一の正しい対処になる。

なお、ここで使う EXPLAIN PLAN ・ データディクショナリビュー ・ DBMS_XPLAN は、いずれもOracleのDBMS_XPLANが導入されたOracle 9.2以降、現役のどのバージョンでも利用できる標準機能だけで構成している。特定バージョン専用の機能は使っていない。

前提となるサンプル

以下では汎用化のため、次の構成のテーブルを例にする。自分の環境のテーブル名・カラム名に読み替えてほしい。

  • テーブル名 SAMPLE_TBL
  • key_col 画面などから入力して検索条件に使うカラム
  • sort_col 結果の並び順を確認したいカラム
  • sub_col 主キーを構成するもう1つのカラム（sort_col との複合主キーを想定）

調べたいSQLは次のような、ORDER BY のないSELECTだとする。

SELECT *
FROM SAMPLE_TBL
WHERE key_col = :input_value;

順序を決めているのは「アクセス経路」

結果の並びを左右している最大の要因は、Oracleがその行をどうやって取りに行ったかというアクセス経路である。代表的なパターンは次の2つ。

  • フルスキャン（ TABLE ACCESS FULL ）の場合、テーブルを物理的に格納されている順に読むため、おおむね登録順（ ROWID の若い順）に近い並びで返りやすい。
  • インデックススキャン（ INDEX RANGE SCAN など ）の場合、そのインデックスのキー順に並んで返りやすい。

つまり「 sort_col 順に見える」のは、 sort_col を含むインデックスが使われている可能性が高い、という見当がつく。これを実際に確認していく。

ステップ1 実行計画でアクセス経路を確認する

まず実行計画を見て、フルスキャンなのかインデックススキャンなのかを特定する。これが調査の本丸になる。

1-1 実行計画を計算する

EXPLAIN PLAN FOR の後ろに、調べたいSQLをそのまま書く。変更するのはこのSQL部分だけで、 EXPLAIN PLAN FOR はキーワードなので変えない。

EXPLAIN PLAN FOR
SELECT *
FROM SAMPLE_TBL
WHERE key_col = :input_value;

このコマンドはSQLを実際には実行せず、実行計画だけを計算して PLAN_TABLE という専用テーブルに書き込む。この時点ではまだ画面には何も出ない。

1-2 計算結果を表示する

書き込まれた計画を人が読める形に整形して表示するのが DBMS_XPLAN.DISPLAY である。次の1行は丸ごと固定で、変更する箇所はない。

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN が計画を保存するだけなのに対し、こちらがそれを読み出して表示する。2つで1セットと考えるとよい。

1-3 出力の読み方

出力の Operation 列に、次のような操作が並ぶ。順序への影響とあわせて押さえる。

  • TABLE ACCESS FULL テーブル全体を物理順に読む。登録順（ ROWID の若い順 ）に近い並びになりやすい。
  • INDEX RANGE SCAN / INDEX FULL SCAN インデックスを走査する。そのインデックスのキー順に並びやすい。
  • TABLE ACCESS BY INDEX ROWID インデックスでヒットしたキーから ROWID を取り出し、実テーブルの行を引く。上にインデックススキャンが乗る形になる。
  • SORT / HASH JOIN ソートや結合が入ると、並びはさらに撹乱される。

フルスキャンが出ていれば「登録順っぽい」、インデックス系が出ていれば「インデックスキー順っぽい」と判別できる。インデックス系だった場合は、出力に使われたインデックス名（ 例 SAMPLE_PK ）が表示されるので、次のステップでその中身を確認する。

ステップ2 使われたインデックスのキー構成を確認する

実行計画でインデックスが使われていたら、そのインデックスがどのカラムで構成されているかを調べる。 sort_col 順に見える理由が「そのインデックスのキーが sort_col だから」だと裏付けるためである。

変更するのは table_name に指定するテーブル名（大文字）だけ。 USER_IND_COLUMNS はOracleが標準で持つシステムビューで、自分で作る必要はない。

SELECT index_name, column_name, column_position
FROM   user_ind_columns
WHERE  table_name = 'SAMPLE_TBL'
ORDER  BY index_name, column_position;

column_position がキー内での順序を表す。複合インデックスなら 1, 2, … と並ぶ。例えば主キーが「 sort_col + sub_col 」の複合キーなら、ここに sort_col が column_position = 1 で出てくるはずである。

ステップ3 補足の確認用クエリ

ステップ1とステップ2で目的はほぼ達成できるが、状況に応じて次の2つも役立つ。どちらも実行計画とは独立して単独で実行でき、変更するのは table_name の値だけである。

3-1 テーブルにあるインデックスの一覧

SELECT index_name, uniqueness, index_type
FROM   user_indexes
WHERE  table_name = 'SAMPLE_TBL';

そのテーブルにどんなインデックスが存在するか、一意かどうか、種類は何かを確認できる。

3-2 主キーがどのカラムかを制約から確認する

SELECT c.constraint_name, c.constraint_type, cc.column_name, cc.position
FROM   user_constraints c
JOIN   user_cons_columns cc ON c.constraint_name = cc.constraint_name
WHERE  c.table_name = 'SAMPLE_TBL'
ORDER  BY c.constraint_type, cc.position;

constraint_type が P の行が主キーである。Oracleでは主キーを作ると自動的に一意インデックスが作られるため、主キーのカラムは必ずインデックス化されている。

調査の進め方まとめ

優先順位をはっきりさせると、次の順番が効率的である。

まずステップ1（ EXPLAIN PLAN と DBMS_XPLAN.DISPLAY のセット ）だけを実行し、アクセス経路を特定する。フルスキャンなら登録順、インデックス系ならキー順、という大枠がここで決まる。インデックスが使われていてその名前がわかったら、はじめてステップ2でそのインデックスのキー構成を確認する。ステップ3は必要に応じての裏付けでよい。

インデックスが「貼られているか」を先に調べたくなるが、貼られていても実際に使われるとは限らない。だから順序の解明という目的なら、まず実行計画で「実際に使われた経路」を押さえてから、そのインデックスの定義を見るのが近道になる。

最後に 大前提として

実行計画を調べてわかるのは、あくまで「今この実行計画だから、結果としてこう並んで見えている」という現状の説明である。実行計画は統計情報の更新やデータ量の変化、バインド変数の値などで変わりうるため、同じSQLでも将来は別の順序になる可能性がある。

順序を確定させたいなら、調査結果がどうであれ ORDER BY を明示する。これが唯一の保証された方法である。複合主キーのように、1つのカラムだけでは行が一意に定まらない場合は、行が一意に定まるところまでソートキーを指定するのが安全である。

SELECT *
FROM SAMPLE_TBL
WHERE key_col = :input_value
ORDER BY sort_col, sub_col;

参考リンク

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?