kagamihogeの日記

kagamihogeの日記です。

ジョインしてgroup byとgroup byをインラインビューにしてジョインの差を見たかった

DB Oracle SQL

SQLのパフォーマンスの本を読んでいると、なるべくジョインの回数は抑えるべし、とある。ここでは、カンタンな集計クエリを通してそのことを確認してみ……たかった。結論から書くと、下記環境でのカンタンなクエリでは差を見ることは出来なかった。

環境

準備

まずテーブルを作る。

create table employee
  (
    emp_id   integer not null ,
    emp_name varchar2(10 byte) ,
    dept_id  integer not null
  );
create table dept
  ( 
    dept_id integer not null ,
    dept_name varchar2(10 byte)
  );

データを入れる。

insert into dept(dept_id, dept_name) values(0, 'あ');
insert into dept(dept_id, dept_name) values(1, 'い');
insert into dept(dept_id, dept_name) values(2, 'う');
insert into dept(dept_id, dept_name) values(3, 'え');
insert into dept(dept_id, dept_name) values(4, 'お');
commit;

insert into employee
select 
  rownum,
  dbms_random.string('X', 10),
  mod(rownum, 5)
from
  (select rownum from all_catalog where rownum <= 1000),
  (select rownum from all_catalog where rownum <= 1000);
commit;

従業員が100万人いて部署が5個しかない企業ってどんなよって感じだが……深く考えない。

インデックスを貼る。

create unique index ind_dept_id on dept (dept_id);
create unique index ind_emp_id on employee (emp_id);

集計クエリ・実行計画

従業員テーブル（employee）を部署テーブル（dept）の部署名（dept_name）ごとの人数を集計するクエリを考える。employee.dept_idでgroup byすればcountは出るが、表示結果はdept.dept_nameごとに出力する、なのでそこでジョインがいる、って感じ。

その１．特にひねらず、ジョインしてgroup byするクエリ。

select 
  d.dept_name,
  count(*)
from employee e
join dept d
on   e.dept_id = d.dept_id
group by d.dept_name;

その２．まずemployeeのみでgroup byを終わらせ、それをインラインビューにしてジョインする。

select 
  d.dept_name,
  v_emp.d_count
from
  (
    select
      e.dept_id d_id,
      count(*) d_count
    from employee e
    group by e.dept_id
  ) v_emp
join dept d
on v_emp.d_id = d.dept_id;

その１．の実行計画。

その２．の実行計画

実行結果

変わらねぇ！　上記の実行計画を見ればわかるとおり、ほぼ同一の実行計画なので、速度は変わらないのは当然である。

感想とか

まず、俺の知ってる教科書知識から予想していた結果から書く。その１．のクエリは、employee100万とdeptをjoinして100万件の中間結果を作り、それをgroup byして5件に絞り込む。その２．のクエリは、employee100万をgropy byして5件の中間結果（＝インラインビュー）を作り、それをdeptとjoinする。最終的に返す結果はどちらも5件。ただし、その１．はjoinを100万回、その２．は5回である。１．の100万回のjoinはどーみてもムダである。なぜなら、クエリを書く人間はdeptが5件しかないことを知っているので、その5件とだけjoinすれば良いのである。ぶっちゃけこのテの集計クエリはその場限りのものなことが多いので、エクセルにコピペしたあと手動で5件分の部署名埋めちゃっても良い。つまり、100万もjoinする必要はどこにも無いってことである。

……のハズだったのだが、驚いたことに、その１．もその２．もほぼ同一の実行計画を出すじゃないっすか。つまり、oracle 11g xeのオプティマイザは、このケースに置いては充分賢い挙動する、と言える。その１．のクエリの実行計画は、まずemployeeを集計してからdeptとjoinするようになっている。これは、クエリの字面とは裏腹に、その２．のインラインビューを使用したクエリと同じ動作である。

Oracle初心者の俺としては驚愕の動作であった。これはつまり、Oracleのバージョン上げたらなんか速くなった、という現象の一つの原因はこういうことなんじゃなかろうか？　オプティマイザの動作が賢くなったおかげで、SQLはナンも変えて無いのに速くなった、というアレ。もちろん、逆もありうるだろうけど。

SQL（＝アプリケーション）は変えずに、アルゴリズム（＝オプティマイザ）を変えられる、というのがRDBMSの強みだ、と言われる所以が少しだけ分かった気がする。