テーブルのアクセス順はパフォーマンスに影響するのか
テーブルを結合するようなSQLを実行する際、パフォーマンスを考慮しfrom句で指定するテーブルの順番を考え直すといった場面があるかと思います。
今回はデータ件数の異なるテーブルを結合条件に指定した場合に、from句で指定するテーブルの順番によって、パフォーマンスにどれくらいの違いが出るのか検証します。
また、対象のデータベースは「Oracle」とします。
と、ここまで説明してご存知の方からはすぐに、「Oracleのバージョンやオプティマイザの種類によって結果が異なるのではないか。」とご指摘をいただきます。
後述しますが、Oracle10g以降の場合は「ルールベース・オプティマイザ（RBO）」がサポートされなくなり、「コストベース・オプティマイザ(CBO)」が主流となっています。
そうなると、SQL分のチューニングが自動的に行われ、from句の順番を考慮する機会はほとんど無くなりつつあります。
では今回の検証は取りやめに……しません！！
コストベース・オプティマイザ採用のOracle11gでオプティマイザ・ヒントを用いて、「from句の順番=Oracleが検索するテーブルの順番」によって、パフォーマンスにどれくらいの違いが出るのかを検証します。
図１：検証イメージ
Oracleのオプティマイザの種類に関して
Oracleのオプティマイザは、データベースへの問い合わせ時にデータに対して最適なアクセス方法を考えてくれる機能です。その種類として、前述しました「ルールベース・オプティマイザ」と「コストベース・オプティマイザ」についても少し触れておきます。
| ルールベース・オプティマイザ | コストベース・オプティマイザ | |
|---|---|---|
| ﾁｭｰﾆﾝｸﾞ | SQL 文を分解した情報と所定ルールに従う。 （格納データの内容は考慮しない） |
データの件数・偏り・分布などを考慮する。 |
| ﾒﾘｯﾄ | ・ルールに従うため、チューニングが容易 ・新機能が追加されないため安定して利用可能 ・実行計画の変更が少ない など・・・ |
・データの変動に対応できる ・オプティマイザの新機能を利用できる ・アクセスパスを柔軟に最適化できる など・・・ |
| ﾃﾞﾒﾘｯﾄ | ・データの変動に対応できない ・オプティマイザの新機能が利用できない ・実行SQLのメンテナンスが困難 など・・・ |
・統計情報を収集する必要がある ･無駄な結合処理により性能劣化する可能性がある など・・・ |
Oracle10g以降はサポートされていないとはいえ、初期化パラメータ次第ではまだRBOを利用できるので、利用しているところは多いかもしれません。
ルールに従うという所が見えやすくて分かりやすいからですかね。
※以降の表記は下記の通り省略します。
「ルールベース・オプティマイザ」 → 「RBO」
「コストベース・オプティマイザ」 → 「CBO」
検証内容
今回は格納されているデータ件数の異なるテーブルを用意し、そのテーブルを結合するSQL文の実行時間を、from句の指定順番を変えながら計測します。
検証詳細
・サンプルテーブルにそれぞれ100件、100万件のデータを作成
・テーブル結合を含むSELECT文の実行時間を計測
・from句の指定順を変えて実行時間を計測
・それぞれ5回実施し、平均時間を採用する
・一回の検証ごとにテーブルデータを削除し、DBバッファキャッシュをクリア
・データ生成には OBのデータ生成機能を使用
検証環境
今回の検証環境は以下の通りです。
| マシン | OS | Windows7 | |||
| CPU | Intel Core i5-2430M 2.40GHz | ||||
| メモリ | 4GB | ||||
| その他 | 特に無し | ||||
| DB ｹｰｽ1 |
対象RDBMS | Oracle11.0.2.0.1.0 | |||
| テーブル名 | TEST_FROM_100(データ件数:100件) TEST_FROM_1000000(データ件数:100万件) |
||||
| テーブル項目(共通) |
※主キー設定：COL1 |
検証準備
①データ生成(100件、100万件を用意)
まずは生成データを下記の通り設定しました。
いつものようにSI Object Browserのデータ生成機能を使って準備します。(第１回参照)
| カラム名 | データ生成方法 |
|---|---|
| COL1 | 連番。1から開始し1ずつカウントアップ |
| COL2 | 乱数値 全桁埋め |
②実行SQLを準備
実行SQLの準備ですが、前述したようにCBOでは自動的に最適なアクセスパスを判断するため、from句の順番を変えても実行計画が変わらず、パフォーマンスに影響し辛くなっています。
実際に実行計画を確認してみましょう。
確認にはSI Object Browserの実行計画画面が便利です。
→SQL実行画面の「実行計画ボタン」をクリックすると確認できます。
図２：実行計画(オプティマイザ・ヒントなし)
上記の通りfrom句の順番を変えても変化はありませんので、今回の検証ではオプティマイザ・ヒントを付けてfrom句の順番でアクセスする順番が変わるようにしました。
from句の順番と今回の検証方法について触れたところで、遅くなりましたが今回実行するSQLは以下の通りです。
■SQL①(データ件数：少→多 の順)
select
/*+ ordered */
*
from
TEST_FROM_100 A,
TEST_FROM_1000000 B
where
A.COL1 = B.COL1
/
■SQL②(データ件数：多→少 の順)
select
/*+ ordered */
*
from
TEST_FROM_1000000 B,
TEST_FROM_100 A
where
A.COL1 = B.COL1
/
検証結果
検証結果は以下の通りとなりました。
| 実行SQL | １回目 | ２回目 | ３回目 | ４回目 | ５回目 | 平均 |
|---|---|---|---|---|---|---|
| SQL① | 00:00.346 | 00:00.221 | 00:00.347 | 00:00.224 | 00:00.346 | 00:00.297 |
| SQL② | 01:13.301 | 01:12.921 | 01:08.247 | 01:12.569 | 01:08.949 | 01:11.197 |
※単位：秒
平均値での実行時間の対比は以下の通りです。
データ件数の多いテーブルに、先にアクセスするSQLの方が、かなり時間がかかる結果となりました。その差は歴然ですね。
追加検証
今回は検証を追加し、データ件数にバリエーションを増やして、様々な組み合わせで実行時間を計測します。
【データ件数バリエーション】
・100件
・1000件
・10000件
・100000件
・1000000件
各データ件数の格納されたテーブルを、from句の１つ目と2つ目に指定してSQLを実行します。
追加検証でも5回実行して平均値を採用しますが、検証結果では平均値のみ公開します。
【検証結果・平均値】
| from句の２つ目に指定するテーブル | ||||||
|---|---|---|---|---|---|---|
| 100件 | 1000件 | 10000件 | 100000件 | 1000000件 | ||
| from句の 1つ目に 指定する テーブル |
100件 | 00:00.359 | 00:00.375 | 00:00.437 | 00:00.312 | 00:00.297 |
| 1000件 | 00:00.515 | 00:00.469 | 00:00.406 | 00:00.546 | 00:00.811 | |
| 10000件 | 00:00.500 | 00:00.452 | 00:00.624 | 00:00.936 | 00:00.796 | |
| 100000件 | 00:00.671 | 00:00.468 | 00:00.422 | 00:00.718 | 00:01.077 | |
| 100000件 | 01:11.197 | 00:58.674 | 00:51.498 | 01:04.004 | 00:52.308 | |
※単位：秒
一つ目に指定したテーブルに着目して比較したグラフは以下の通りです。
特筆すべきは1つ目に1000000件を指定した場合ですね。2つ目に指定した件数に関わらず実行時間がかかってしまっています。
結論
今回の検証結果では、以下のようになりました。
・データ件数によって、アクセスする順番でパフォーマンスに違いが出る
・一つ目にアクセスするテーブルの影響が大きい
・CBOの場合はアクセス順が変わらない場合がある（オプティマイザの判断)
今回の検証では、オプティマイザ・ヒントを利用して「from句の順番=アクセスする順番」が変わることで、パフォーマンスに違いが出ることが確認できました。
データの内容、件数、処理内容などによっては結果が異なるかと思われますが、今回の検証結果ではデータ件数を考慮した実行SQLのメンテナンスが非常に重要であることがわかりました。
また、from句で指定する順番のうち、一つ目のテーブルに大きく影響を受けるということが確認できました。
今回の検証では単純なテーブルと、単純な結合方法でのSQL実行であったため、このようなパフォーマンスの差が顕著に出たものと考えますが、パフォーマンスの良い順番にアクセスするという考えは正しかったということになります。
ただ、RBOを採用しているDBでは自動的に行っている部分が大きく、なかなか意識しない部分になりますので、もしCBOを採用する場合には少しでも参考にしていただければ幸いです。
今回の検証結果は以上となります。
![いまさら聞けない Oracleの基本 [初級編]](https://products.sint.co.jp/hubfs/images/siob/thumb/ora_b.png)
![いまさら聞けない Oracleの基本 [中級編]](https://products.sint.co.jp/hubfs/images/siob/thumb/ora_b2.png)
