複数値圧縮(MVC)の効果検証

# 目的
各型に対する複数値圧縮(MVC)の効果を検証

1. char型
2. varchar型
3. Date型
4. 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
```