SQL*Loader入力ファイルのソート順による実行速度とインデックスへの影響

SQL*Loaderの従来型INSERTモードを使用してインデックスが有効なテーブルへデータをロードするとき、入力ファイルがソートされているかいないかがどのような影響を及ぼすのかを確認する。

環境

Oracle Database Express Edition 11g Release 2をWindows上で動作
- SQL*Loaderを起動するクライアントと、Oracle Databaseは物理的に異なるマシンでやっています。
Oracle SQL Developer 3.2

やったこと

データを投入していくテーブルはこんな感じ。データロードを一回やるたびにDROP＆CREATEをする。

DROP TABLE DEST PURGE;
CREATE TABLE DEST (COLUMN1 VARCHAR2(16) , CONSTRAINT DEST_PK PRIMARY KEY(COLUMN1));

SQL*Loader用の制御ファイルはこんな感じ。コミット間隔は10000で固定、このあとすぐ後述するように入力ファイル（infile）を切り替えて実行する。

OPTIONS (ROWS=10000)
load data 
infile 'data_asc.dat' "fix 16"
insert
into table DEST
(COLUMN1 CHAR(16))

入力ファイルは3つ用意する。1.昇順、2.降順、3.ランダム。

昇順はこんな感じ。ゼロ埋め16バイトで数値が昇順に並んだものを百万件。

0000000000000001
0000000000000002
0000000000000003
（以下略。また、実際には改行無し）

降順はこんな感じ。

0000000001000000
0000000000999999
0000000000999998
（以下略。また、実際には改行無し）

ランダムはこんな感じ。0000001〜1000000がランダムかつ一意になってる。

0000000000510970
0000000000817400
0000000000694564
（以下略。また、実際には改行無し）

実行結果

まず実行時間から。

ASC	00: 23.79	00: 24.53	00: 22.90
DESC	00: 36.40	00: 35.48	00: 38.99
RAND	01: 41.61	01: 28.59	01: 28.91

結果は、昇順が最も速く、降順は昇順と比べると遅くなり、ランダムは激しく遅くなる、というものになった。まず重要なのは、データのロード時間も重要だが、その時一緒に更新されるインデックス作成のコストが決して無視できないものである、ということ。インデックスがマッタク無いテーブルというのはちょっと考えにくいので、インデックスを考慮したSQL*Loaderの使い方を事前に検討する必要がある。インデックスがどのように使われるのかはそれぞの環境に依存するので一概に言えることは無さそうである。大きいところでは、データロードとインデックス作成を同時にやるか後回しにするか、ダイレクトロードを検討するか、といったところだろうか？

ソート順でなぜ実行時間に差が生じるのか。これを考える前に、まず昇順・降順・ランダムそれぞれ実行後のインデックスの使用ブロック数を確認する。

確認に使用するSQLは下記の通り。

SELECT SEGMENT_NAME, BYTES, BLOCKS, EXTENTS
  FROM USER_SEGMENTS
 WHERE SEGMENT_NAME IN ('DEST', 'DEST_PK');

ASC

SEGMENT_NAME	BYTES	BLOCKS	EXTENTS
DEST	26214400	3200	40
DEST_PK	28311552	3456	42

DESC

SEGMENT_NAME	BYTES	BLOCKS	EXTENTS
DEST	26214400	3200	40
DEST_PK	56623104	6912	69

RAND

SEGMENT_NAME	BYTES	BLOCKS	EXTENTS
DEST	26214400	3200	40
DEST_PK	40894464	4992	54

テーブルの方は使用バイト数やブロック数に変化は無い。しかし、インデックスの方は昇順にくらべて降順はバイト数・ブロック数がほぼ2倍になっている。ランダムは、昇順に比べて約1.5倍といったところ。

インデックスについてもう少し詳しい状況を調べるためにINDEX_STATSを取る。

ANALYZE INDEX DEST_PK VALIDATE STRUCTURE;
SELECT * FROM INDEX_STATS;

結果は下記の通り。Oracle Databaseリファレンス11g リリース2 (11.2) INDEX_STATSを参照。

	BTREE_SPACE	USED_SPACE	PCT_USED
ASC	27120352	27077694	100
DESC	53601088	27142929	51
RANDOM	38520544	27104016	71

BTREE_SPACEにかなりの開きがある一方、USED_SPACEにはほとんど差が無い。同じデータを挿入しているのだから、使用スペースに差が出ないのは当然の結果ではあるのだが、BTREE_SPACEが2倍も開きがあるのが恐ろしい。

昇順の場合、リーフブロック内に順々にデータを入れていき、PCTFREEに達したら次のリーフブロックを作成するだけの動作をする。降順の場合、リーフブロックにデータを入れるには常にブロックの先頭に入れなければならない……が、そのコストは実行時間を見る限りでは（入力ファイルのソート順の影響に比べると）そんなに大きくは無い。むしろリーフブロックの分割が頻発することのほうが足を引っ張っていると思われる。Oracleの場合、リーフブロックが満杯つーかPCTFREEに達すると、ブロックの分割が発生する。といっても末尾にデータを入れる分には新規ブロックを割り当てるだけだが。ブロックの真ん中*1に挿入する場合、ブロック内のデータを半分にして新規ブロックに残り半分のデータを移動する*2。なので、降順データの場合、半分だけ満たされたブロックが作成され続けることになる。PCT_USEDが51なのはその表れと言える。実行時間の差はそれによって開きが出ているように感じる。むしろ問題なのはインデックス領域のムダ遣いで、PCT_USEDが51なので半分もスカスカである。勿体無い上に、範囲スキャンをしたときの非効率さは半端無いと思われる。

次にランダムであるが、実行時間はこの中で最も遅くなる。ブロック数がある程度増えた後は、INSERTごとに追加先のリーフブロックを探しなおさなければならなくなるコストによるものと思われる。加えて、リーフブロックの分割がほどほどに発生するので、余計に遅くなる。PCT_USEDが71とDESCに比べればマシとはいえ、ASCに比べればヒドイことには変わりは無い。

まぁ要するに、SQL*Loader使うときはデータファイルをソートしておくことに越したことはない、ってことですかね。

*1:降順データの場合はブロックの先頭だが「真ん中」の範疇に入る

*2:この動きのソースが見つけられなかったがPCT_USED51から見ても大体あってると思われる

kagamihogeの日記

kagamihogeの日記です。

SQL*Loader入力ファイルのソート順による実行速度とインデックスへの影響

環境

やったこと

実行結果