表の圧縮率を高めるために得た知見
今回は表の圧縮についてのお話。
概要
ハードディスクやSSDの価格が安くなった昨今においても、やはり無駄なスペースはそれ自体害を及ぼす。 また、諸般の事情でストレージ容量を追加することができない場合もあるだろう。 そんな時に思い浮かぶのが圧縮である。 今回はこの圧縮作業の一つである表の圧縮について色々と記す。 結論、表圧縮の際にレコードの並び順が圧縮率に影響し、 さらにカラムのカーディナリティよりはカラム自体のデータサイズが圧縮に大きく影響することが確認された。
環境
- Linux : Red Hat Enterprise Linux Server release 6.5 (Santiago)
- oracle 12c ( Single Instance )
表圧縮の基本
オラクルには表圧縮の機能がある。圧縮の仕組みはざっくり、列値の圧縮である。 同一値参照により圧縮を実現している。
ちなみに圧縮のメリット、デメリットは以下の通り(引用：20.2.6.1 表圧縮について)。
メリット
ディスク領域が節約され、データベース・バッファ・キャッシュのメモリー使用が削減されて、読込み中の問合せ実行速度が大幅に向上します。
デメリット
圧縮には、データのロードやDMLのためのCPUオーバーヘッドがかかります。
結局のところ
ただし、この負荷はI/O要件の削減によって相殺されます。圧縮された表データはメモリー内で圧縮されたままになるため、より多くの行をデータベース・バッファ・キャッシュ(および有効になっている場合はフラッシュ・キャッシュ)に収めることができ、圧縮によってDML操作のパフォーマンスを向上させることもできます。
というわけで圧縮にはメリットが多い。
次に、圧縮の流れとその効果を確認する。
下準備
-- 表の作成
create table testtbl11 as select * from dba_objects;
select count(*) from testtbl11;
-- 90979
-- データについての情報
select segment_name,bytes,blocks,extents from dba_segments
where segment_name='TESTTBL11';
-- TESTTBL11 12582912 1536 27
--圧縮の効果を見るため、同一レコードを再度INSERTする
insert into testtbl11 select * from dba_objects;
commit;
select count(*) from testtbl11;
--181958
圧縮
表を圧縮する際のコマンドは以下の通り。圧縮方法は試しにOLTP圧縮を指定する。
-- 表の圧縮
alter table testtbl11 move compress for oltp ;
-- 表のサイズの確認
select segment_name,bytes,blocks,extents from dba_segments
where segment_name='TESTTBL11';
-- TESTTBL11 7340032 896 22
| データサイズ(Byte) | ブロック数 | エクステント数 | |
|---|---|---|---|
| 圧縮前 | 12,582,912 | 1,536 | 27 |
| 圧縮後 | 7,340,032 | 896 | 22 |
上記の結果の通り、圧縮の効果が確認できる。 ちなみに、この例ではデータセットを二回INSERTしたので、同一レコードが二つずつ存在することになる。 そのため、理想的に圧縮されれば半分近くまで圧縮できるはずである。
より圧縮率を高めるために
より圧縮率を高めるためにするにはどうすれば良いか。 それは、ブロック内データの圧縮率を高めることである（オラクルの圧縮は、ブロック内での圧縮なので）。 つまるところ、同一ブロック内に重複レコードがたくさん存在するようにすればより高い圧縮率が得られる。 となればそう、圧縮前に表のデータをソート（並び替え）すれば良いのである。
しかしながら、これを試す中で、ソートの条件によっては逆に圧縮率が悪くなる結果が出た。 ここからは圧縮率が高くなる例と低くなる例を示す。
圧縮率が高くなる例
圧縮率が高くなるソートは、言うまでもなく重複レコードが隣り合うようなソートであり すなわち全カラムについてのソートである。
-- dba_objects表は18カラム
desc dba_objects;
-- 18カラムすべてでソートされた表を作成
create table testtbl_sorted_all as select * from testtbl11 order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18;
-- 表の圧縮
alter table testtbl_sorted_all move compress for oltp ;
col segment_name for a20
select segment_name,bytes,blocks,extents from dba_segments
where segment_name in (
'TESTTBL11',
'TESTTBL_SORTED_ALL'
);
-- SEGMENT_NAME BYTES BLOCKS EXTENTS
-- -------------------- ---------- ---------- ----------
-- TESTTBL11 7340032 896 22
-- TESTTBL_SORTED_ALL 6291456 768 21
| 圧縮後の結果 | データサイズ(Byte) | ブロック数 | エクステント数 |
|---|---|---|---|
| ソートなし | 7,340,032 | 896 | 22 |
| 全カラムでソート | 6,291,456 | 768 | 21 |
上記の結果から、ソートされた状態の表の方が圧縮率が高いことが分かる。 非圧縮時に比べてブロック数も1,536から768と半分になり、期待通りといえる。
圧縮率が低くなる例
一方で、そのままの状態で圧縮するよりもソートによって圧縮率が低くなることもある。 当初、全カラムでなくてもソートすれば多少はそのままの時よりも圧縮率が高くなるだろうと気楽に考えていたが １カラムについてのみソートしたところ、逆に圧縮率が低い結果が得られた。
--ownerカラムでソートした状態の表を作成
create table testtbl22 as select * from testtbl11 order by owner;
--圧縮
alter table testtbl22 move compress for oltp ;
select segment_name,bytes,blocks,extents from dba_segments
where segment_name in (
'TESTTBL11',
'TESTTBL22'
);
--圧縮後の結果
-- TESTTBL22の方が圧縮率が悪い。。。
-- TESTTBL11 7340032 896 22
-- TESTTBL22 8388608 1024 23
| 圧縮後の結果 | データサイズ(Byte) | ブロック数 | エクステント数 |
|---|---|---|---|
| ソートなし | 7,340,032 | 896 | 22 |
| OWNERカラムでソート | 8,388,608 | 1024 | 23 |
圧縮のツボ探し
全カラムについてのソートをすれば重複したレコードが隣り合わせになるため圧縮率は上がる。 これは当たり前であるが、たとえば先に挙げた通り逆に低くなる例もある。 一部のカラムのみのソートでも圧縮率を上げるためのツボはあるのだろうか。
仮説
カーディナリティに注目する。カーディナリティが高い（レコード数に対してユニークな値が多い）カラムについてソートすれば それよりもカーディナリティが低いカラムの値は隣り合う確率が高いため、圧縮率は高くなるのではないかと予想してみる。
検証
各カラムのカーディナリティを確認し、圧縮率の違いを調べる。 ここでは抽出する表のレコード数は全レコードで一定のため、各カラムのユニークな値の数がカーディナリティとする。
-- カーディナリティの出し方
-- e.g.
select count(distinct OWNER) from dba_objects;
--27
結果は以下の通り。
| カラム名 | ユニークなカラム値の数 |
|---|---|
| OWNER | 27 |
| OBJECT_NAME | 53,598 |
| SUBOBJECT_NAME | 277 |
| OBJECT_ID | 91,015 |
| DATA_OBJECT_ID | 7,661 |
| OBJECT_TYPE | 44 |
| CREATED | 929 |
| LAST_DDL_TIME | 1,025 |
| TIMESTAMP | 1,061 |
| STATUS | 1 |
| TEMPORARY | 2 |
| GENERATED | 2 |
| SECONDARY | 1 |
| NAMESPACE | 22 |
| EDITION_NAME | 0 |
| SHARING | 3 |
| EDITIONABLE | 2 |
| ORACLE_MAINTAINED | 2 |
上記結果を受けて、カーディナリティの高い以下3カラムについてそれぞれソートした表の圧縮結果を見てみる。
- OBJECT_ID
- OBJECT_NAME
- DATA_OBJECT_ID
圧縮の手順は先述の通りなので省略する。
ちなみに圧縮率の悪い例で紹介したOWNERカラムのカーディナリティは27と低かった。 果たしてこの仮説は正しいか。
結果
| 圧縮後の結果 | データサイズ(Byte) | ブロック数 | エクステント数 |
|---|---|---|---|
| ソートなし | 7,340,032 | 896 | 22 |
| 全カラムでソート | 6,291,456 | 768 | 21 |
| OBJECT_IDでソート | 6,291,456 | 768 | 21 |
| OBJECT_NAMEでソート | 5,242,880 | 640 | 20 |
| DATA_OBJECT_IDでソート | 6,291,456 | 768 | 21 |
以上の結果から、仮説に反しカーディナリティが二番目に高いOBJECT_NAMEでのソートが一番圧縮率が高かった。
考察
結果を踏まえると、カーディナリティよりは、カラムのデータサイズが大きく影響しているように見える。
--各カラムの値の合計サイズ（MB）
select round(sum(lengthb(object_name))/1024/1024,2) mb from dba_objects;
--2.06
select round(sum(lengthb(object_id))/1024/1024,2) mb from dba_objects;
--0.42
select round(sum(lengthb(data_object_id))/1024/1024,2) mb from dba_objects;
--0.03
って、少し考えれば当然か。。。 というわけでデータサイズを加えた表は以下の通り。
| カラム名 | ユニークなカラム値の数 | カラムのデータサイズ(MB) |
|---|---|---|
| OWNER | 27 | 0.41 |
| OBJECT_NAME | 53,598 | 2.06 |
| SUBOBJECT_NAME | 277 | 0.01 |
| OBJECT_ID | 91,015 | 0.42 |
| DATA_OBJECT_ID | 7,661 | 0.03 |
| OBJECT_TYPE | 44 | 0.67 |
| CREATED | 929 | 0.69 |
| LAST_DDL_TIME | 1,025 | 0.69 |
| TIMESTAMP | 1,061 | 1.65 |
| STATUS | 1 | 0.44 |
| TEMPORARY | 2 | 0.09 |
| GENERATED | 2 | 0.09 |
| SECONDARY | 1 | 0.09 |
| NAMESPACE | 22 | 0.09 |
| EDITION_NAME | 0 | N/A |
| SHARING | 3 | 1.07 |
| EDITIONABLE | 2 | 0.05 |
| ORACLE_MAINTAINED | 2 | 0.09 |
ここまでくると、やはりデータサイズが大きい順にソートした場合の圧縮率を見たくなる。
create table testtbl_sorted_by_datasize as select * from testtbl11
order by
OBJECT_NAME,
TIMESTAMP,
SHARING,
CREATED,
LAST_DDL_TIME,
OBJECT_TYPE,
STATUS,
OBJECT_ID,
OWNER,
TEMPORARY,
GENERATED,
SECONDARY,
NAMESPACE,
ORACLE_MAINTAINED,
EDITIONABLE,
DATA_OBJECT_ID,
SUBOBJECT_NAME,
EDITION_NAME
/
-- 表が作成されました。
alter table testtbl_sorted_by_datasize move compress for oltp ;
-- 表が変更されました。
SQL> select segment_name,bytes,blocks,extents from dba_segments where segment_name='TESTTBL_SORTED_BY_DATASIZE';
-- SEGMENT_NAME BYTES BLOCKS EXTENTS
-- ------------------------- ---------- ---------- ----------
-- TESTTBL_SORTED_BY_DATASIZE 5242880 640 20
結果を追記。 より高い圧縮率を期待したものの、OBJECT_NAMEでソートした際と結果は同じであった。
| 圧縮後の結果 | データサイズ(Byte) | ブロック数 | エクステント数 |
|---|---|---|---|
| ソートなし | 7,340,032 | 896 | 22 |
| 全カラムでソート | 6,291,456 | 768 | 21 |
| OBJECT_IDでソート | 6,291,456 | 768 | 21 |
| OBJECT_NAMEでソート | 5,242,880 | 640 | 20 |
| DATA_OBJECT_IDでソート | 6,291,456 | 768 | 21 |
| カラムのデータサイズでソート | 5,242,880 | 640 | 20 |
今回の例ではOBJECT_NAMEのデータサイズだけ他カラムに比べ飛び抜けて大きかったため結果が同じになったのかもしれないが いずれにせよデータサイズを考慮したソートが圧縮率を高めるのに有効であるとは言えそう。
まとめ
表の圧縮についての動作確認と、ソートによる圧縮率の違いを示した。 なお、圧縮率を高めるためにはカラムの値を考慮してソートするカラムを選択するのが良さそうである。
蛇足：圧縮表から作成した表は非圧縮
圧縮表から新しく別名で表を作成した場合に表のサイズは最初から圧縮されるのかどうか気になり試してみた。 結果は、新しく作成される表は非圧縮状態で作成された。
--圧縮表をもとにcreate tableする
create table testtbl33 as select * from testtbl11;
select segment_name,bytes,blocks,extents from dba_segments
where segment_name in (
'TESTTBL11',
'TESTTBL33'
);
-- TESTTBL11 7340032 896 22
-- TESTTBL33 25165824 3072 39
--圧縮表をもとにcreate tableしても圧縮されない
圧縮状態で作成するには以下のように圧縮オプションcompress for XXXを指定する必要がある。
drop table testtbl33;
create table testtbl33 compress for oltp as select * from testtbl11;
select segment_name,bytes,blocks,extents from dba_segments
where segment_name in (
'TESTTBL11',
'TESTTBL33'
);
-- SEGMENT_NAME BYTES BLOCKS EXTENTS
-- -------------------- ---------- ---------- ----------
-- TESTTBL11 7340032 896 22
-- TESTTBL33 7340032 896 22
--圧縮された状態で作成されていることが分かる