【Oracle】indexをリーフ分割させながらデータを挿入する

はじめに

この記事では、Oracle DBでインデックスのリーフ分割を発生させながらデータを挿入する方法について書いています。

以前、テストデータを大量に作成するためのtips記事を書きました。replication.hatenablog.com
このときのSQLは、以下の画像のように連番を昇順にカウントアップさせながら、データを挿入していくものでした。
f:id:replication:20150917222716p:plain:w250
このように綺麗に並んだデータは本番環境のデータベースに格納されているデータとはかけ離れたものになります。

ブロック数の違い

きれいに並んだデータとそうでないデータでは、インデックスのブロック数に違いがでます。
インデックスのブロック数は、以下のクエリで調べることができます。

ANALYZE INDEX idx_test_table VALIDATE STRUCTURE;
SELECT NAME AS インデックス名,
  BLOCKS    AS ブロック数,
  LF_ROWS   AS リーフ行数,
  LF_BLKS   AS リーフブロック数,
  BR_BLKS   AS ブランチのブロック数
FROM INDEX_STATS;

検証

きれいに並んだデータとそうでないデータで、ブロック数の違いを調べます。

SQL1 連番を昇順で挿入していった場合
SQL2 連番を増分10で挿入し、その値の間にさらにデータを挿入した場合

テスト用のテーブルを作成します。

CREATE TABLE test_table(no NUMBER, data VARCHAR2(200));

noカラムにインデックスを貼ります。

CREATE INDEX idx_test_table ON test_table(no);

ここからデータ挿入です。
最初のSQLはROWNUMを使い、連番を増分1で昇順で挿入しています。以下の例は100×100=1万行挿入します。

insert /*+ APPEND */ into test_table nologging (
no, data) 
  (select 
  rownum,
  concat('テストデータＡ', TO_MULTI_BYTE(rownum))
  from (select 0 from all_catalog where rownum <= 100),
       (select 0 from all_catalog where rownum <= 100)
  );
commit;

２つ目のSQLは、1千万から増分10で5000件挿入します。その後、1千万1から増分10でさらに5000件挿入します。
こうすることで、最初に挿入したデータの間にそれぞれデータを挿入することができるようになります。
こんなイメージです。
f:id:replication:20150917225539p:plain:w300

insert /*+ APPEND */ into test_table nologging (
no, data) 
  (select 
  10000000 + 10*rownum,
  concat('テストデータＡ', TO_MULTI_BYTE(10000000 + 10*rownum))
  from (select 0 from all_catalog where rownum <= 100),
       (select 0 from all_catalog where rownum <= 100)
  );
commit;

insert /*+ APPEND */ into test_table nologging (
no, data) 
  (select 
  10000001 + 10*rownum,
  concat('テストデータＢ', TO_MULTI_BYTE(10000001 + 10*rownum))[f:id:replication:20150917234316p:plain]
  from (select 0 from all_catalog where rownum <= 100),
       (select 0 from all_catalog where rownum <= 100)
  );
commit;

検証結果

昇順で挿入した場合

レコード数	リーフ行数	ブロック数	リーフブロック数	ブランチのブロック数
10000	10000	32	21	1
100000	100000	256	199	1
1000000	1000000	2048	1999	4

データの間に挿入を繰り返した場合

レコード数	リーフ行数	ブロック数	リーフブロック数	ブランチのブロック数
10000	10000	40	33	1
100000	100000	384	326	1
1000000	1000000	3328	3253	9

データレコード数１万件の場合
f:id:replication:20150917234316p:plain
データレコード数１０万件の場合
f:id:replication:20150917234335p:plain
データレコード数１００万件の場合
f:id:replication:20150917234346p:plain
大体1.5倍くらいにブロック数が上昇しました。