SELECTをアスタリスクと列指定したときの速度差

SQLアンチパターン18章 インプリシットカラム（暗黙の列）では、SELECT,INSERT,UPDATEするときは、*（アスタリスク）を使わず、必要な列のみを列挙したほうが良い、といったことが書かれている。その理由については色々書かれているが、このエントリでは実行速度について考える。

検証内容について。同一長の列を二つ持つテーブルを作成し、1つはアスタリスク、1つは1列だけを指定するSELECT文を実行する。このとき、二つのクエリに速度差がどのくらい出るかを確認し、その理由について考える。

環境

• Oracle Database Express Edition 11g Release 2をCentOS 6.4 x86_64上で動作
• Oracle SQL Developer 3.2
• SQL*Plus: Release 11.2.0.2.0 Production

準備

同一長の文字列型を二つ持つテーブルを作成する。

drop table two_columns purge;
create table two_columns 
(
  column1 varchar2(16),
  column2 varchar2(16)
);

100万件テストデータを作成する。どっちの列にもランダム16バイトを入れていく。

insert into two_columns
select 
  dbms_random.string('X', 16),
  dbms_random.string('X', 16)
from
  (select rownum from all_catalog where rownum <= 1000),
  (select rownum from all_catalog where rownum <= 1000);
commit;

スキーマ統計を収集しておく。

begin 
  DBMS_STATS.GATHER_SCHEMA_STATS (
    ownname => 'KAGAMIHOGE',
    estimate_percent => 1
  );
end;

計測用のクエリ

アスタリスクで全2列を取得するもの。

select * from two_columns;

列指定で1列のみを取得するもの。

select column1 from two_columns;

計測はSQL*Plusから行う。自動トレースと実行時間を取るため、下記コマンドを実行してから計測用のクエリを流す。

set timing on
set autotrace on

また、計測用のクエリは何度か流してバッファキャッシュにブロックが乗った状態にしておく。

結果

各クエリを三回ずつ行い、SQL*Plusの経過時間として表示されたものが下記の表。

	1	2	3
全列	00:03:06.73	00:03:24.85	00:03:24.90
1列	00:01:42.37	00:01:42.32	00:01:44.56

全列の自動トレース。3回ともビミョウに個別の値が違う点はあるけどほぼ同じなので1つだけ載せる。

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      72080  consistent gets
          0  physical reads
          0  redo size
   44867051  bytes sent via SQL*Net to client
     733689  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

1列の自動トレース。

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      72079  consistent gets
          0  physical reads
          0  redo size
   23867050  bytes sent via SQL*Net to client
     733689  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

感想とか

というわけで、全列より1列のほうがかなり速い結果になりました。差は、2倍にギリギリ届かず1.7倍から1.8倍といったところ。2列から1列と半分になれば、速度も半分になるかな？　と思ったけどさすがにピッタリ半分にはならないようで。

差が出る要因とは何か。自動トレースのOracleからSQL*Plusに渡されたバイト数の欄（bytes sent via SQL*Net to client）を見てみる。全列は44867051で、1列は23867050になっている。これは、SELECTでアスタリスクか1列指定か、に対応している。バイト数が、おおよそ1列の2倍が全列のそれになっており、速度差がほぼ2倍になるのはここから来ているのだと考えられる。

ブロック読み込みについてはどうか。アクセスしたブロック数はどちらも約72080前後と変わらない。よって、このケースではブロックの読み込みは実行時間を左右する要因とはならない。今回はぜんぶバッファキャッシュに載せて影響排除してるのもあるけど、キャッシュを消してディスクI/Oを発生させたとしても、どちらのクエリも全行スキャンなので左右する要因とは成りえない。

よって、二つのクエリの差の主たる要因は、Oracleデータベースからクライアントへネットワークを介して送られるバイト数の差、となる。つまり、アプリケーション側で使用する列だけ指定すべき、というのは言い換えれば、Oracleからネットワークを介してアプリケーションに送信されるバイト数を最小にして実行時間を抑えるべき、となる。

DBサーバーとアプリケーションサーバが同一マシンならば影響度は下がるだろうが、よほど小規模構成でないとそうはしないと思う。仮想化とかするとまた別かもしれないが。ただ、どちらにせよ、一般的にネットワークI/OはディスクI/Oより更に狭いので、そこを通るバイト数は少ないに越した事は無い。

O/Rマッパーの都合で使いたい列だけ指定する、ってのが難しかったりすることもある（らしい）　が、そーいうときどーしたらいいのかは知らない。

参考文献

SQLアンチパターン