読者です 読者をやめる 読者になる 読者になる

小さい頃はエラ呼吸

いつのまにやら肺で呼吸をしています。


【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;
検証

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

  1. SQL1 連番を昇順で挿入していった場合
  2. 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('テストデータA', TO_MULTI_BYTE(rownum))
  from (select 0 from all_catalog where rownum <= 100),
       (select 0 from all_catalog where rownum <= 100)
  );
commit;

2つ目の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('テストデータA', 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('テストデータB', 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

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

ブロック数の差異の理由

ブロック数の違いは、間に挿入することによりインデックスのリーフが分割されるためです。

以下のページに詳しく書かれています。www.insight-tec.com