目的
各型に対する複数値圧縮(MVC)の効果を検証
- char型
- varchar型
- Date型
- integer型
準備・前提
テーブル定義
show table t5;
CREATE SET TABLE SYSDBA.t5 ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
c1 INTEGER,
c2 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
c3 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
c4 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
c5 VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
c6 DATE FORMAT 'YYYY-MM-DD',
c7 INTEGER)
UNIQUE PRIMARY INDEX ( c1 );
データ
基本索引（c1）以外は同じ値。件数は約420万件。
select * from t5 where c1 < 10 order by c1;
c1 c2 c3 c4 c5 c6
----------- -- ---- ---------- ------------------------------ ---------- --
1 a abcd ex100 abcdefghijk 2020-02-04
3 a abcd ex100 abcdefghijk 2020-02-04
5 a abcd ex100 abcdefghijk 2020-02-04
7 a abcd ex100 abcdefghijk 2020-02-04
9 a abcd ex100 abcdefghijk 2020-02-04
select count(*) from t5;
Count(*)
-----------
4194309
容量
469,843,968バイト
select tablename, sum(currentPerm) from dbc.tableSize where tableName = 't5' group by tablename;
TableName Sum(CurrentPerm)
------------------------------ --------------------------
t5 469,843,968
複数値圧縮(MVC)
c2列 : char(1)を圧縮
alter table t5 add c2 compress ('a');
*** Table has been modified.
*** Total elapsed time was 13 seconds.
select tablename, sum(currentPerm) from dbc.tableSize where tableName = 't5' group by tablename;
TableName Sum(CurrentPerm)
------------------------------ --------------------------
t5 469,843,968
→char(1)に対する圧縮効果無し
c3列 : char(4)を圧縮
alter table t5 add c3 compress ('abcd');
*** Table has been modified.
*** Total elapsed time was 15 seconds.
select tablename, sum(currentPerm) from dbc.tableSize where tableName = 't5' group by tablename;
TableName Sum(CurrentPerm)
------------------------------ --------------------------
t5 436,330,496
→char(4) 圧縮効果 34MB（1件当たり8.0バイト）
c4列：varchar(10)を圧縮
alter table t5 add c4 compress ('ex100');
*** Table has been modified.
*** Total elapsed time was 15 seconds.
select tablename, sum(currentPerm) from dbc.tableSize where tableName = 't5' group by tablename;
TableName Sum(CurrentPerm)
------------------------------ --------------------------
t5 369,254,400
→varchar(10) 圧縮効果 67MB（1件当たり16.0バイト）
c5列：varchar(30）を圧縮
alter table t5 add c5 compress ('abcdefghijk');
*** Table has been modified.
*** Total elapsed time was 17 seconds.
select tablename, sum(currentPerm) from dbc.tableSize where tableName = 't5' group by tablename;
TableName Sum(CurrentPerm)
------------------------------ --------------------------
t5 268,546,048
→varchar(30) 圧縮効果 101MB（1件当たり24.0バイト）
c6列 ： date型圧縮
alter table t5 add c6 compress (date '2020-02-04');
*** Table has been modified.
*** Total elapsed time was 18 seconds.
select tablename, sum(currentPerm) from dbc.tableSize where tableName = 't5' group by tablename;
TableName Sum(CurrentPerm)
------------------------------ --------------------------
t5 234,967,040
→date型圧縮効果 34MB（1件当たり8.0バイト）
c7列 ： integer型圧縮
alter table t5 add c7 compress (10);
*** Table has been modified.
*** Total elapsed time was 19 seconds.
select tablename, sum(currentPerm) from dbc.tableSize where tableName = 't5' group by tablename;
TableName Sum(CurrentPerm)
------------------------------ --------------------------
t5 201,400,320
→integer型圧縮効果 34MB（1件当たり8.0バイト）
圧縮後の状態を確認
select TableName, ColumnName, ColumnLength, ColumnFormat, Compressible, CompressValueList from dbc.Columns where tablename = 't5' order by ColumnName ;
*** Query completed. 7 rows found. 6 columns returned.
*** Total elapsed time was 1 second.
TableName ColumnName ColumnLength ColumnFormat Compressible CompressValueList
------------------------------ ------------------------------ ------------- ------------------------------ ------------ --------------------------------------------------------------------------------
t5 c1 4 -(10)9 N ?
t5 c2 1 X(1) C ('a')
t5 c3 4 X(4) C ('abcd')
t5 c4 10 X(10) C ('ex100')
t5 c5 30 X(30) C ('abcdefghijk')
t5 c6 4 YYYY-MM-DD C (DATE '2020-02-04')
t5 c7 4 -(10)9 C (10)
おまけ
ブロック圧縮（BLC）有の表に対して同様の複数値圧縮(MVC)実施した場合も
若干効果有
select tablename, sum(currentPerm) from dbc.tableSize where databasename = 'sysdba' group by tablename;
（圧縮前）
TableName Sum(CurrentPerm)
------------------------------ --------------------------
t2 99,164,160
（圧縮後）
TableName Sum(CurrentPerm)
------------------------------ --------------------------
t2 92,319,744