Oracle Databaseのテーブル圧縮方法
Oracle Databaseには複数のテーブルの圧縮機能が提供されています。圧縮率について検証してみました。
| 設定 | 説明 | 使用方法 |
|---|---|---|
| ROW STORE COMPRESS BASIC | 基本表圧縮を行う | ダイレクト・パス・インサート実行時 |
| ROW STORE COMPRESS ADVANCED | 高度な行圧縮を行う | すべてのINSERT文で圧縮実行 |
| COLUMN STORE COMPRESS FOR QUERY | ハイブリッド列圧縮（圧縮率低） | すべてのINSERT文で圧縮実行 |
| COLUMN STORE COMPRESS FOR ARCHIVE | ハイブリッド列圧縮（圧縮率高） | すべてのINSERT文で圧縮実行 |
COLUMN STORE句はExadataで使用できる表領域でのみ指定できます。このためROW STORE句について検証しました。検証テーブルはNUMBER型のみのテーブルで圧縮には不向きですが、このテーブルで効果を確認します。
圧縮に使用するデータはTPC-DSに使用するINVENTORYテーブルです。
| 列名 | データ型 | NOT NULL | 主キー |
|---|---|---|---|
| INV_DATE_SK | NUMBER(38) | NOT NULL | YES |
| INV_ITEM_SK | NUMBER(38) | NOT NULL | YES |
| INV_WAREHOUSE_SK | NUMBER(38) | NOT NULL | YES |
| INV_QUANTITY_ON_HAND | NUMBER(38) | NULL | NO |
レコード数は11,745,000レコード、非圧縮時のブロック数は17,912ブロックです。
基本表圧縮
基本表圧縮を行うテーブルは、CREATE TABLE文またはALTER TABLE文にROW STORE COMPRESS BASIC句を指定します（Oracle Database 11gではCOMPRESS指定）。圧縮指定はUSER_TABLESビューで確認できます。COMPRESSION列にはENABLEDが、COMPRESS_FOR列にはBASICが出力されます。
CREATE TABLE文実行時に指定すると、自動的にPCTFREEが0に指定され、レコードの格納率が上昇します。
SQL> CREATE TABLE INVENTORY_CB ROW STORE COMPRESS BASIC AS SELECT * FROM INVENTORY WHERE 1=2;
表が作成されました。
SQL> SELECT table_name, compression, compress_for, pct_free FROM user_tables
2 WHERE table_name ='INVENTORY_CB';
TABLE_NAME COMPRESS COMPRESS_FOR PCT_FREE
------------------------------ -------- ------------------------------ ----------
INVENTORY_CB ENABLED BASIC 0
基本表圧縮実行のためにはダイレクト・パスINSERTが必要です。このためAPPENDヒントで元のテーブルからデータを格納します。
SQL> INSERT /*+ APPEND */ INTO inventory_cb SELECT * FROM inventory;
11745000行が作成されました。
SQL> COMMIT;
コミットが完了しました。
SQL> execute DBMS_STATS.GATHER_TABLE_STATS('TPCDS','INVENTORY_CB');
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT table_name, avg_space, blocks FROM user_tables WHERE table_name = 'INVENTORY_CB';
TABLE_NAME AVG_SPACE BLOCKS
------------------------------ ---------- ----------
INVENTORY_CB 0 10283
ブロック数で約43%削減できていることがわかります。V$SYSSTATビューから、INSERT文の操作によるRedoログ生成量を確認したところ、約373MBでした。
高度な行圧縮
高度な行圧縮を行うテーブルは、CREATE TABLE文またはALTER TABLE文にROW STORE COMPRESS ADVANCED句を指定します（Oracle Database 11gではCOMPRESS FOR OLTP指定）。圧縮指定はUSER_TABLESビューで確認できます。COMPRESSION列にはENABLEDが、COMPRESS_FOR列にはADVANCEDが出力されます。
基本表圧縮と異なり、PCTFREEの指定は変化しません。
SQL> CREATE TABLE INVENTORY_CA ROW STORE COMPRESS ADVANCED AS SELECT * FROM INVENTORY WHERE 1=2;
表が作成されました。
SQL> SELECT table_name, compression, compress_for, pct_free FROM user_tables
2 WHERE table_name ='INVENTORY_CA';
TABLE_NAME COMPRESS COMPRESS_FOR PCT_FREE
------------------------------ -------- ------------------------------ ----------
INVENTORY_CA ENABLED ADVANCED 10
高度な行圧縮実行のためには通常INSERT文でも圧縮が行われます。
SQL> INSERT INTO inventory_ca SELECT * FROM inventory;
11745000行が作成されました。
SQL> COMMIT;
コミットが完了しました。
SQL> execute DBMS_STATS.GATHER_TABLE_STATS('TPCDS','INVENTORY_CA');
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT table_name, avg_space, blocks FROM user_tables WHERE table_name = 'INVENTORY_CA';
TABLE_NAME AVG_SPACE BLOCKS
------------------------------ ---------- ----------
INVENTORY_CA 0 12187
圧縮率は基本表圧縮よりも低く、32%ですが、ダイレクト・パスINSERTが不要である点が利点になります。V$SYSSTATビューから、INSERT文の操作直後によるRedoログ生成量を確認したところ、約404MBでした。
高度な行圧縮機能は、データを一旦格納したあと、圧縮を非同期で行います。このためデータベースに対する書き込み操作が通常よりも大きくなります。
高度な行圧縮設定のテーブルにダイレクト・パスINSERTを行った場合も検証しました。
SQL> INSERT /*+ APPEND */ INTO inventory_ca SELECT * FROM inventory;
11745000行が作成されました。
SQL> COMMIT;
コミットが完了しました。
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('TPCDS','INVENTORY_CA');
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT table_name, avg_space, blocks FROM user_tables WHERE table_name = 'INVENTORY_CA';
TABLE_NAME AVG_SPACE BLOCKS
------------------------------ ---------- ----------
INVENTORY_CA 0 11442
圧縮率は37%になりました。高度な行圧縮とダイレクト・パスINSERTの実行でも圧縮が行われることを確認できました。
圧縮効率は高くないと思われるテーブルで検証を行いましたが、予想以上に圧縮できました。