kagamihogeの日記

kagamihogeの日記です。

Oracleのクラスタを範囲スキャン代わりにする

DB Oracle SQL

Oracleのクラスタさわってみる - kagamihogeのblogの続き。

Oracleのクラスタの使用例で良く見かけるのは、2つ以上のテーブルを結びつける多重クラスタが多い。ただし、別に1つのクラスタに1つのクラスタ化表しか入れなくても問題は無い。このとき受けられるメリットとして、クラスタキーで設定した列値が同一の行は一箇所にまとめられるため、その列値の行をまとめて取り出す操作は通常テーブルより有利になるハズである。このエントリではそのことを確認する。

環境

準備

クラスタ

クラスタの作成

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

クラスタ化表の作成。クラスタには、deptnoをクラスタキーにした表を1つしか作らない。

CREATE TABLE c_emp
(
  empno INTEGER,
  ename VARCHAR2(10),
  deptno INTEGER
)
CLUSTER emp_cluster (deptno);

クラスタ索引の作成

CREATE INDEX emp_cluster_index ON CLUSTER emp_cluster;

データの作成

INSERT INTO c_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 TABLE n_emp
(
  empno INTEGER,
  ename VARCHAR2(10),
  deptno INTEGER
);

データを入れる。

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);
速度計測用のクエリ

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

ALTER SYSTEM FLUSH BUFFER_CACHE;

範囲スキャンをするクエリの速度計測などを行う。
クラスタ用のもの。

SELECT empno, ename, deptno
FROM   c_emp
WHERE  deptno <= 10;

通常テーブルのもの。

SELECT empno, ename, deptno
FROM   n_emp
WHERE  deptno <= 10;
速度計測の方法

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

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

set timing on
set autotrace on
set arraysize 100

結果

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

種類 時間 consistent gets physical reads
クラスタ 08.20 1320 310
通常 08.28 2537 528

感想とか

実行時間は大して変わらないが、このくらいのデータ量だとウチの環境では差は出ないので致し方ない。

注目すべきはブロック数の方にある。2つの範囲スキャンを行うクエリは全く同じ結果を返すのだが、autotraceのブロック統計を見ると2倍近い開きが見られる。その理由は2つ考えられる。

1つ目はクラスタの特性によるもの。クラスタキーで同一値は一度しか格納されないので、同一列値の部分については一度だけしか読み込まなくて済む。このエントリでのdeptnoがそれで、例えばdeptno=1は1万行あるが、deptno=1というデータは1回しか格納されない。その節約された分だけブロック数も減るハズである。

2つ目はアクセスパスの差によるもの。通常テーブルでインデックスを使用した範囲スキャンをする場合、インデックスと実テーブルの2つのオブジェクトを使う必要がある。このエントリの例では、deptno=1という列は1万行あるため、インデックスも1万行必要となる。また、rowidで実テーブルにアクセスする必要がある。このエントリではdeptnoは順番に格納されているが、もしこれがバラバラの場合、rowidはバラバラのブロックにランダムアクセスしなければならなくなる。そうでなくとも、rowidで1レコードずつ取り出さざるを得ない。対してクラスタの場合、読み込み対象のクラスタキーのブロックを見つけたらそこからスキャンするだけでよい。

このエントリの例は元からカーディナリティ度が低い事が分かっているので、B-Treeインデックスによる範囲スキャンはどのみち不利である。実際オブティマイザは <= 10 を <= 20にするともうフルスキャンを選択してしまう。対してクラスタ化表の方は、<= 100と全レコード返す条件であってもまだ範囲スキャンを選択する。コスト的にはそんくらい軽く見積もっても構わない、ということなんだろう。

この方法のデメリットは、insert/update/deleteにある。めんどくさいので試していないが、あらかじめ決められた場所に行を配置しなければならないので、どうしたってそうした操作は不利にならざるを得ないハズである。逆を言えばそうしたデメリットを覚悟の上で検索効率を取るか、もしくは挿入更新削除が全く発生しないのならばデメリットは事実上存在しないので、適用の可能性が出てくる。

実行計画

クラスタ
------------------------------------------------------------------------------------------
| Id  | Operation            | Name              | Rows  | Bytes | Cost (%CPU)|Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |   104K|  1945K|    12   (0)|00:00:01 |
|   1 |  TABLE ACCESS CLUSTER| C_EMP             |   104K|  1945K|    12   (0)|00:00:01 |
|*  2 |   INDEX RANGE SCAN   | EMP_CLUSTER_INDEX |     1 |       |     1   (0)|00:00:01 |
------------------------------------------------------------------------------------------


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

   2 - access("DEPTNO"<=10)
通常
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |   102K|  1893K|   640 (1)| 00:00:08 |
|   1 |  TABLE ACCESS BY INDEX ROWID| N_EMP            |   102K|  1893K|   640 (1)| 00:00:08 |
|*  2 |   INDEX RANGE SCAN          | IDX_N_EMP_DEPTNO |   102K|       |   204 (1)| 00:00:03 |
------------------------------------------------------------------------------------------------


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

   2 - access("DEPTNO"<=10)

参考URL