Oracle 11g検証 Advanced Compression その1

<Oracle 11g検証 Advanced Compression その1>
ペンネーム: クリープ

今週も引き続き11gについて検証していきます。
今週は11g新機能!?であるAdvanced Compressionについてみていきます。
さてさて、Advanced Compressionとはどのような機能なのでしょうか?
じっくり見ていきましょう。

■■■■■概要■■■■■
1.Advanced Compressionとは?
2.9iR2セグメント圧縮機能について Part1

■環境
Red Hat Enterprise Linux ES Update 5
Oracle Database 9i Enterprise Edition Release 9.2.0.8 – Production

1.Advanced Compressionとは?
Advanced Compressionとはテーブルなどのデータを圧縮する機能のことです。
最近はYouTubeなどの普及によりWebサイトなどで動画データを取り扱うサイト
をよく見かけるようになりました。また、データウェアハウスの使用や日本版
JSOX法により長期間のデータ保存が義務づけられるなど、企業が保存すべきデ
ータ量は増加の一途をたどっています。
このような大量データを取り扱う為にOracleから提供された機能がAdvanced
Compressionという機能になります。
ちなみに、テーブルデータの圧縮機能は9iR2から追加された機能です。10gでは
特に変わりなく、11gでAdvanced(進化した)機能として位置づけられていま
す。
では、何がAdvancedになったのでしょうか?Advancedな機能を確認する前に、
まずは9iR2の圧縮機能についておさらいしてみましょう。

2.9iR2セグメント圧縮機能について Part1
Oracle9iR2のセグメント圧縮ではどのようにデータを圧縮しているのでしょ
うか?「Oracle9i リリース2:データ・セグメントの圧縮 オラクル・ホワイト
・ペーパー」には以下のような記載があります。

▼Oracle9i リリース2:データ・セグメントの圧縮 オラクル・ホワイト・ペ
ーパーより
======================================================================
Oracle9i リリース2 では、データベース・ブロック内でのデータの重複値を
取り除くことでデータを圧縮します。各データベース・ブロック(ディスク・
ページ)に格納された圧縮データは、それぞれ独立しています。つまり、ある
ブロック内において非圧縮データを再作成するために必要な情報はすべてその
ブロック内にあります。ブロック内のすべての行および列の重複値は、まず1
度ブロックの先頭ののシンボル表と呼ばれる場所に格納されます。そしてそれ
らの重複値は、シンボル表への短い参照に置き換えられます。
======================================================================
URL:http://otndnld.oracle.co.jp/products/iserver/oracle9i/pdf/o9ir2_c
ompression_twp-r.pdf

判りづらいので簡単に説明すると、
・圧縮データは非圧縮データと分けて管理されている
・圧縮データは重複した値をブロックの先頭に格納し、その値を参照するよ
うにする
ということで、つまり同じ値が格納されていると圧縮率が高く、主キーなどが
格納されている項目では圧縮されない、ということになります。

また、9iR2でのセグメント圧縮では、圧縮が実行される処理は以下に限られて
います。

・SQL*Loader のダイレクト・パス・モード
・CREATE TABLE … AS SELECT 文
・パラレル・インサート(またはAPPEND ヒントを用いたシリアル・インサー
ト)文
※パラレル・インサート(シリアル・インサート)
APPENDヒント句を追加することで、既存データの後ろに挿入されたデータ
を追加(APPEND)します。また、バッファキャッシュへの書き込みを行わ
ずデータファイルに直接書き込む為、高速に大量データを処理可能。

上記のような大量データを一括でロードするような処理の一部として圧縮が実
行されます。つまり、通常のINSERT処理やDELETE処理、UPDATE処理のようなデ
ータ操作では圧縮処理は実行されない、という制限があります。
これらについて簡単に確認してみることにしましょう。

圧縮属性のあるテーブルを2つ作成し、それぞれに通常のインサートとAPPEND
を使用したインサートを実行して、通常のインサートでは圧縮されないことを
確認してみます。
ちなみに、データが圧縮されているかを確認する方法ですが、データファイル
のダンプを取得する以外ない為、今回はデータをINSERT後にテーブルのサイズ
を比較して圧縮されたかどうか確認することにします。
⇒ダンプについては検証後半でじっくり確認

まずは、圧縮属性のあるテーブルを2つ作成します。作成方法はCREATE時に
COMPRESSオプションを指定するだけ。これで圧縮データが格納可能なテーブル
になります。

SQL> create table comp_table_normal
  2  ( seq_no number, msg varchar2(10), primary key(seq_no) )
  3  compress;

Table created.

SQL> create table comp_table_append
  2  ( seq_no number, msg varchar2(10), primary key(seq_no) )
  3  compress;

Table created.

これで、圧縮データが格納可能なテーブル、comp_table_normalと
comp_table_appendが作成されました。では、これらのテーブルにそれぞれ通
常のインサートとAPPENDを使用したインサートを実行し、テーブルサイズを
比較してみることにします。
とりあえず、10万件のデータを格納してみましょう。

SQL> begin
  2  for i in 1 .. 100000 loop
  3     insert into comp_table_normal
  4        values( i, '0000000000' );
  4     commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> insert /*+ append */ into comp_table_append
  2    select * from comp_table_normal;
SQL> commit;

PL/SQL procedure successfully completed.

データが作成されました。では、テーブルのサイズを比較してみることにしま
す。統計情報を取得してuser_tablesのblocksからテーブルサイズを確認して
みましょう。

SQL> exec dbms_stats.gather_schema_stats('TEST');

PL/SQL procedure successfully completed.

SQL> select t.table_name, p.value block_size,
  2         t.blocks * p.value/1024 "size(KB)"
  3    from sys.user_tables t, v$parameter p
  4   where table_name In( 'COMP_TABLE_NORMAL', 'COMP_TABLE_APPEND' )
  5     and name = 'db_block_size';

TABLE_NAME                     BLOCK_SIZE             size(KB)
------------------------------ -------------------- ----------
COMP_TABLE_NORMAL              2048                       2166
COMP_TABLE_APPEND              2048                       1276

通常のインサートでは2.11MBだったのに対してAPPENDを使用したINSERT処理で
は1.24MBと60%近く小さくなっていることから、圧縮されていることがわかり
ます。

それでは、今回インサートしたMSG列の値を一意の値で格納した場合はどのよう
になるでしょうか?
MSG列の値を一意にして、再度実行してみましょう。

SQL> truncate table comp_table_normal;

Table truncated.

SQL> truncate table comp_table_append;

Table truncated.

SQL> begin
  2  for i in 1 .. 100000 loop
  3     insert into comp_table_normal
  4        values( i, trim(to_char(i, '0000000000')));
  4     commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> insert /*+ append */ into comp_table_append
  2    select * from comp_table_normal;
SQL> commit;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats('TEST');

PL/SQL procedure successfully completed.

SQL> select t.table_name, p.value block_size,
  2         t.blocks * p.value/1024 "size(KB)"
  3    from sys.user_tables t, v$parameter p
  4   where table_name In( 'COMP_TABLE_NORMAL', 'COMP_TABLE_APPEND' )
  5     and name = 'db_block_size';

TABLE_NAME                     BLOCK_SIZE             size(KB)
------------------------------ -------------------- ----------
COMP_TABLE_NORMAL              2048                       2166
COMP_TABLE_APPEND              2048                       2212

上記のように、通常のインサートのサイズよりサイズが大きくなってしまい
ました。
このように、一意の値で構成されたテーブルに対しては圧縮されず、むしろ
基のテーブルよりも大きくなってしまうことが判りました。

まとめ
1.同じ値が格納されているテーブルを圧縮した場合の圧縮率は高い
2.一意の値が格納されているテーブルでは逆にサイズが大きくなる

今週は9iR2の基本機能を確認するところで終わってしまいました。
来週も9iR2でのセグメント圧縮の機能について確認する予定です。
11gの機能はその後を予定していますので、今しばらくお待ち下さい。

気付いたら2008年が半分終わってました
恵比寿にて