Oracleのクラスタさわってみる

Oracle Database管理者ガイド11g リリース2 (11.2) - クラスタの概要

クラスタとは、joinするデータをあらかじめ連続した領域に置いておくことでディスクI/Oを減らしjoinの高速化を図れる、というもの。クラスタリングとかと概念は似てるけど、機能的には別モノ。↑のリンクの概要図を見ると分かりやすい。そんなわけで、今回はコレを実際に作ってみて実行速度が改善できるかどうかやってみる。が、結論から言うと俺の試行では然程の改善を見ることが出来なかった。俺のやり方が悪いのか、そもそもそーいうものなのかはイマイチ不透明な結果となった。

環境

• Oracle Database Express Edition 11g Release 2ã‚’CentOS 6.4 x86_64上で動作
• Oracle SQL Developer 4.0
• SQL*Plus: Release 11.2.0.2.0 Production

やること

emp表とdept表のjoinの実行速度を計測する。1つは通常のテーブル、1つはクラスタ化表を用いる。挿入するデータは、deptを100件、empを100万件。dept1件につき、emp10000件を入れる。部署が100個で1部署1万人の合計従業員が100万人とか謎データだけどきにしない。

準備

クラスタ化表・通常のテーブルなどを作る。

クラスタ

クラスタの作成

DROP CLUSTER emp_dept INCLUDING TABLES;
CREATE CLUSTER emp_dept (deptno INTEGER);

クラスタ化表の作成

CREATE TABLE emp
(
  empno INTEGER,
  ename VARCHAR2(10),
  deptno INTEGER
)
CLUSTER emp_dept (deptno);
CREATE TABLE dept
(
  deptno INTEGER,
  deptname VARCHAR2(10)
)
CLUSTER emp_dept (deptno);

クラスタ索引の作成

CREATE INDEX emp_dept_index ON CLUSTER emp_dept;

データ作成。

INSERT INTO dept(deptno, deptname)
  SELECT ROWNUM, dbms_random.string('X', 10)
    FROM 
      (SELECT ROWNUM FROM all_catalog WHERE ROWNUM <= 100);
COMMIT;

INSERT INTO emp(empno, ename, deptno)
  SELECT ROWNUM, dbms_random.string('X', 10), ceil(ROWNUM/10000)
    FROM 
      (SELECT ROWNUM FROM all_catalog WHERE ROWNUM <= 1000),
      (SELECT ROWNUM FROM all_catalog WHERE ROWNUM <= 1000);
COMMIT;

ちなみにクラスタ化表に対してであっても普通にインデックスを作れる。

CREATE UNIQUE INDEX ind_dept_dept_no ON dept(deptno);
CREATE INDEX ind_emp_dept_no ON emp(deptno);

通常のテーブル

クラスタ化表の違いはクラスタを使うこと。それ以外は同一のテーブル構造・データを使用する。

テーブルを作る。

CREATE TABLE n_dept
(
  deptno INTEGER,
  deptname VARCHAR2(10)
);
CREATE TABLE n_emp
(
  empno INTEGER,
  ename VARCHAR2(10),
  deptno INTEGER
);

データを入れる。

INSERT INTO n_dept(deptno, deptname)
  SELECT ROWNUM, dbms_random.string('X', 10)
    FROM 
      (SELECT ROWNUM FROM all_catalog WHERE ROWNUM <= 100);
COMMIT;
INSERT INTO n_emp(empno, ename, deptno)
  SELECT ROWNUM, dbms_random.string('X', 10), ceil(ROWNUM/10000)
    FROM 
      (SELECT ROWNUM FROM all_catalog WHERE ROWNUM <= 1000),
      (SELECT ROWNUM FROM all_catalog WHERE ROWNUM <= 1000);
COMMIT;

ネステッドループ用のインデックスを作る。

CREATE INDEX ind_n_emp_dept_no ON n_emp(deptno);

データ準備等が完了したら、スキーマ統計の収集をしておく。

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

速度計測用のクエリ

速度計測用の各種類のクエリを流す前に毎回バッファキャッシュをクリアする。

ALTER SYSTEM FLUSH BUFFER_CACHE;

速度計測は二種類行う。1つは、二つの表をjoinして全件検索するもの。1つは、二つの表をjoinして範囲検索するもの

全件検索の方から。

クラスタ化表で使うクエリその１　ネステッドループジョインして全件取得するだけ。

SELECT /*+ USE_NL(d e) */ d.deptno, d.deptname, e.empno, e.ename
FROM   dept d
JOIN   emp e ON d.deptno = e.deptno;

クラスタ化表で使うクエリその２　マージジョインして全件取得するだけ。

SELECT /*+ USE_MERGE(d e) */ d.deptno, d.deptname, e.empno, e.ename
FROM   dept d
JOIN   emp e ON d.deptno = e.deptno;

通常のテーブルで使うクエリその１　ネステッドループジョイン。

SELECT /*+ USE_NL(d e) */ d.deptno, d.deptname, e.empno, e.ename
FROM   n_dept d
JOIN   n_emp e ON d.deptno = e.deptno;

通常のテーブルで使うクエリその２　ハッシュジョイン。

SELECT /*+ USE_HASH(d e) */ d.deptno, d.deptname, e.empno, e.ename
FROM   n_dept d
JOIN   n_emp e ON d.deptno = e.deptno;

範囲検索のクエリ。joinして表全体の10%をさらってくる。

クラスタ化表で使うクエリその１　ネステッドループジョイン。

SELECT /*+ USE_NL(d e) */ d.deptno, d.deptname, e.empno, e.ename
FROM   DEPT D
JOIN   EMP E ON D.DEPTNO = E.DEPTNO
WHERE  d.deptno <= 10;

クラスタ化表で使うクエリその２　マージジョイン。

SELECT /*+ USE_MERGE(d e) */ d.deptno, d.deptname, e.empno, e.ename
FROM   DEPT d
JOIN   EMP E ON D.DEPTNO = E.DEPTNO
WHERE  d.deptno <= 10;

通常のテーブルで使うクエリその１　ネステッドループジョイン。

SELECT /*+ USE_NL(d e) */ d.deptno, d.deptname, e.empno, e.ename
FROM   N_DEPT D
JOIN   N_EMP E ON D.DEPTNO = E.DEPTNO
WHERE  d.deptno <= 10;

通常のテーブルで使うクエリその２　ハッシュジョイン。

SELECT /*+ USE_HASH(d e) */ d.deptno, d.deptname, e.empno, e.ename
FROM   n_dept d
JOIN   N_EMP E ON D.DEPTNO = E.DEPTNO
WHERE  d.deptno <= 10;

速度計測の方法

SQL*Plusで上記のクエリを実行し終わったあとの経過時間を確認する。

また、SQL*Plusは下記のようなパラメータ設定をする。上二つはともかく、arraysizeは時間短縮のために設定した（どのていど効果があるのかは確認していないけれど）

set timing on
set autotrace on
set arraysize 100

結果

実行計画はこのエントリの末尾にまとめて載せた。

全件検索。

実行時間

種類	1	2	3
クラスタ ネステッドループ	01:10.46	01:23.03	01:15.36
クラスタ マージ	01:15.84	01:17.75	01:18.09
通常 ネステッドループ	01:17.20	01:16.81	01:19.18
通常 ハッシュ		01:15.46	01:25.00	01:15.62

autotraceの抜粋。

種類	consistent gets	physical reads
クラスタ ネステッドループ	16343	6242
クラスタ マージ	16343	6243
通常 ネステッドループ	27394	5275
通常 ハッシュ	13358	3356

範囲検索

実行時間*1

種類	1
クラスタ ネステッドループ	00:11.21
クラスタ マージ	00:10.92
通常 ネステッドループ	00:10.48
通常 ハッシュ	00:10.48

autotraceの抜粋。

種類	consistent gets	physical reads
クラスタ ネステッドループ	1649	310
クラスタ マージ	1630	310
通常 ネステッドループ	2715	530
通常 ハッシュ	2539	530

感想とか

全件検索の実行時間はほとんど差は見られなかった。

通常テーブルのハッシュジョインについて。これが最もconsistent gets, physical readsが少ない。autotraceのこの部分だけを比較すると、これが一番優位といえる。駆動表のdept表は100件と少ないのでハッシュ作成処理は一瞬で終わる。そのため、あとはemp表を順次アクセスしていけば良いだけである。よって、実質的にはemp表をフルスキャンしてるだけになるのでアクセスブロックが少なくなるのは当然である。

通常テーブルのネステッドループジョインについて。先述のハッシュジョインと異なり、インデックス経由のテーブルアクセスが必要となる。よって、アクセスブロックの差はインデックスの分だけ増加する。ただ、それが不利かというと別にそうでもない。その理由は、テストデータ作成のSQLが同一deptnoのemp行は固めて挿入しているからである。emp表をrowidでアクセスしにいっても、その次のrowideもさっきのブロックにほとんどが含まれているので、余計なブロックアクセスはほとんど発生しないハズである。

クラスタ化表のネステッドループジョインについて。実行計画は、deptをフルスキャンしながらempをclusterアクセスしている。クラスタキーはdeptのdeptnoに置いているので、deptのあるdeptnoに辿り着いたらそこからクラスタを構成するブロックを辿れば良いだけ、となる。早くなりそうな気がしたけど……そうでもなかった。

結合列の条件でブロックが並んでいるので、フルスキャンは何しても大して変わらない気がする。また、クラスタキーの範囲が粗いので、ハッシュジョインで効率良く処理できてしまっている。ここから考えられるのは、クラスタ化表が有効であるためには、少なくともハッシュジョインが効果的では無いほどにはクラスタキーが細かい粒度を取らなければならないのでは？　という点。

そこで10%を返す範囲検索の結果を見てみる。さすがに実行時間に大した差は生じないが、autotraceのconsistent gets, physical readsは興味深い。

ブロックアクセス数の差はインデックスの差にある。通常テーブルの場合、範囲スキャンにはどうしたってインデックスを使わざるを得ない。その分だけブロック数が多くなる。

クラスタ化表の場合、クラスタキーのdeptnoの位置が分かればそこから順次アクセスすればempのデータも取得できる。通常テーブルのjoinと異なり、インデックスを中継する必要が無い。また、クラスタキー内には結合条件に当てはまらないempのデータは絶対に入っていない。なのでフィルタする必要もない。通常テーブルは、（特にそういう操作をしなければ）行がどのような順序で入っているかを強制できない。データがどんな順序で入っているかを気にしないで済むのがリレーショナルな世界のメリットなのだけど、それをクラスタ化表は投げ捨てていることになる。

クラスタ化表が投げ捨てていることとは何か。RDBMSにとってそれぞれの表はそれぞれ単独で存在している。クラスタ化表は物理的に結び付けているので、それぞれの表を単独で扱おうとすると途端に不利になる。下記のような、deptテーブルのみを検索するクエリを実行してみる。

SELECT d.deptno, d.deptname FROM dept d;
SELECT d.deptno, d.deptname FROM n_dept d;

autotraceの結果はこんな感じ。

種類	consistent gets	physical reads
クラスタ	3144	3143
通常	7	6

通常の場合100件入ってるdeptを見ればそれで済むけど、クラスタはそうはいかない。クラスタ化表のどこからがdeptの行が分からないし、なお悪いのはあるブロックのほんの一部分だけがdeptに過ぎないのでアクセス効率は最悪である。最も、クラスタ化表にもインデックスは張れるので頑張ればある程度は挽回できるだろうけど、構造的に難しいものをカバーするのは悪手といえる。

なので、それぞれの表を単独で使いたい、とか、色んなテーブルとjoinする予定、とかの場合にはクラスタは使わない方が良いと言える。逆を言うと、そうした柔軟性を捨ててでもjoinの効率を上げたいケースにしか使えないのでは？　と俺は感じた。

クラスタキーの分布が重要そうである。要はjoinしたときのブロックアクセスのコストが最小になれば良いわけである。クラスタは最初からjoinした状態にしてしまうことに意義がある。ただし、クラスタキーの分布があまりに少ないとこのエントリで見たようにハッシュジョインで良いし、多いとクラスタにまとめて保持する行が少なくなり通常のjoinと相対的に差がつかなくなりそうである。それがどの程度なのかはちょっと分からないけれども……

ともかくクラスタで効果を上げるのは、向くケースが限りなく狭そうなので、使いこなすのは難しそうだなぁ……と感じたのでした。

実行計画（全件取得）

クラスタ ネステッドループ

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |   998K|    31M|  3857   (1)| 00:00:47 |
|   1 |  NESTED LOOPS         |      |   998K|    31M|  3857   (1)| 00:00:47 |
|   2 |   TABLE ACCESS FULL   | DEPT |   100 |  1400 |   855   (1)| 00:00:11 |
|*  3 |   TABLE ACCESS CLUSTER| EMP  |  9983 |   185K|    30   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("D"."DEPTNO"="E"."DEPTNO")

クラスタ マージジョイン

----------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |   998K|    31M|   957   (1)| 00:00:12 |
|   1 |  MERGE JOIN           |                |   998K|    31M|   957   (1)| 00:00:12 |
|   2 |   TABLE ACCESS CLUSTER| EMP            |   998K|    18M|   101   (0)| 00:00:02 |
|   3 |    INDEX FULL SCAN    | EMP_DEPT_INDEX |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN           |                |   100 |  1400 |   856   (1)| 00:00:11 |
|   5 |    TABLE ACCESS FULL  | DEPT           |   100 |  1400 |   855   (1)| 00:00:11 |
----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")

通常 ネステッドループジョイン

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |   999K|    31M|  6342  (1)| 00:01:17 |
|   1 |  NESTED LOOPS                |                  |       |       |     |          |
|   2 |   NESTED LOOPS               |                  |   999K|    31M|  6342  (1)| 00:01:17 |
|   3 |    TABLE ACCESS FULL         | N_DEPT           |   100 |  1400 |     3  (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IDX_N_EMP_DEPTNO | 10069 |       |    20  (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| N_EMP            |  9996 |   185K|    63  (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")

通常 ハッシュジョイン

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |   999K|    31M|   952   (3)| 00:00:12 |
|*  1 |  HASH JOIN         |        |   999K|    31M|   952   (3)| 00:00:12 |
|   2 |   TABLE ACCESS FULL| N_DEPT |   100 |  1400 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| N_EMP  |   999K|    18M|   940   (3)| 00:00:12 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("D"."DEPTNO"="E"."DEPTNO")

実行計画（範囲検索）

クラスタ ネステッドループ

----------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                | 10165 |   327K|   312   (0)| 00:00:04 |
|   1 |  NESTED LOOPS         |                | 10165 |   327K|   312   (0)| 00:00:04 |
|   2 |   TABLE ACCESS CLUSTER| DEPT           |    10 |   140 |    12   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN   | EMP_DEPT_INDEX |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS CLUSTER| EMP            |  1007 | 19133 |    30   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("D"."DEPTNO"<=10)
   4 - filter("E"."DEPTNO"<=10 AND "D"."DEPTNO"="E"."DEPTNO")

クラスタ マージジョイン

-----------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                | 10165 |   327K|    25   (4)| 00:00:01 |
|   1 |  MERGE JOIN            |                | 10165 |   327K|    25   (4)| 00:00:01 |
|   2 |   TABLE ACCESS CLUSTER | EMP            |   100K|  1869K|    12   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN    | EMP_DEPT_INDEX |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN            |                |    10 |   140 |    13   (8)| 00:00:01 |
|   5 |    TABLE ACCESS CLUSTER| DEPT           |    10 |   140 |    12   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN   | EMP_DEPT_INDEX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("E"."DEPTNO"<=10)
   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
   6 - access("D"."DEPTNO"<=10)

通常 ネステッドループジョイン

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   | 10459 |   337K|   255   (2)| 00:00:04 |
|   1 |  NESTED LOOPS                 |                   |       |       |       |          |
|   2 |   NESTED LOOPS                |                   | 10459 |   337K|   255   (2)| 00:00:04 |
|   3 |    TABLE ACCESS BY INDEX ROWID| N_DEPT            |    10 |   140 |2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_N_DEPT_DEPTNO |    10 |       |1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IDX_N_EMP_DEPTNO  |  1051 |       |    20   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | N_EMP             |  1037 | 19703 |    25   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D"."DEPTNO"<=10)
   5 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("E"."DEPTNO"<=10)

通常 ハッシュジョイン

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost(%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   | 10459 |   337K|   650   (1)| 00:00:08 |
|*  1 |  HASH JOIN                   |                   | 10459 |   337K|   650   (1)| 00:00:08 |
|   2 |   TABLE ACCESS BY INDEX ROWID| N_DEPT            |    10 |   140 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_N_DEPT_DEPTNO |    10 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| N_EMP             |   103K|  1923K|   647   (1)| 00:00:08 |
|*  5 |    INDEX RANGE SCAN          | IDX_N_EMP_DEPTNO  |   105K|       |   210   (1)| 00:00:03 |

--------------------------------------------------------------------------------

------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("D"."DEPTNO"="E"."DEPTNO")
   3 - access("D"."DEPTNO"<=10)
   5 - access("E"."DEPTNO"<=10)

*1:コチラはめんどくなって1回しか試行してない