partitioning + compress で200%アップ

TPC-Hベンチマーク続き
前回のCompressを行う前は：

次にLINEITEMをl_shipdateでPartitioningしたら

そして、各パーティションをCompressしたら、2倍以上のqphをたたき出した

Partitioning前はLINITEMが全体の70%の負荷オブジェクトだったが、今では負荷はある程度分散された。

そして、あれほど気にしていたディスク転送量もMAX時の420MB/sから100MB/s程度下がってしまった、、、
しかし、SSDの応答時間は2ミリ秒。通常のHDDの応答時間が12ミリ秒前後を考えると、SSD RAID0で十分すぎるレスポンス・タイムだ。

CPU%は？
まだまだ20%程度しか使われていない。

create table lineitem
pctfree 1
pctused 99
initrans 10
storage (freelist groups 4 freelists 84)
parallel
nologging
partition by range (l_shipdate)
(
partition item1 values less than (to_date('1992-01-01','YYYY-MM-DD')),
partition item2 values less than (to_date('1992-02-01','YYYY-MM-DD')),
partition item3 values less than (to_date('1992-03-01','YYYY-MM-DD')),
partition item4 values less than (to_date('1992-04-01','YYYY-MM-DD')),
partition item5 values less than (to_date('1992-05-01','YYYY-MM-DD')),
partition item6 values less than (to_date('1992-06-01','YYYY-MM-DD')),
partition item7 values less than (to_date('1992-07-01','YYYY-MM-DD')),
partition item8 values less than (to_date('1992-08-01','YYYY-MM-DD')),
partition item9 values less than (to_date('1992-09-01','YYYY-MM-DD')),
partition item10 values less than (to_date('1992-10-01','YYYY-MM-DD')),
partition item11 values less than (to_date('1992-11-01','YYYY-MM-DD')),
partition item12 values less than (to_date('1992-12-01','YYYY-MM-DD')),
partition item13 values less than (to_date('1993-01-01','YYYY-MM-DD')),
partition item14 values less than (to_date('1993-02-01','YYYY-MM-DD')),
partition item15 values less than (to_date('1993-03-01','YYYY-MM-DD')),
partition item16 values less than (to_date('1993-04-01','YYYY-MM-DD')),
partition item17 values less than (to_date('1993-05-01','YYYY-MM-DD')),
partition item18 values less than (to_date('1993-06-01','YYYY-MM-DD')),
partition item19 values less than (to_date('1993-07-01','YYYY-MM-DD')),
partition item20 values less than (to_date('1993-08-01','YYYY-MM-DD')),
partition item21 values less than (to_date('1993-09-01','YYYY-MM-DD')),
partition item22 values less than (to_date('1993-10-01','YYYY-MM-DD')),
partition item23 values less than (to_date('1993-11-01','YYYY-MM-DD')),
partition item24 values less than (to_date('1993-12-01','YYYY-MM-DD')),
partition item25 values less than (to_date('1994-01-01','YYYY-MM-DD')),
partition item26 values less than (to_date('1994-02-01','YYYY-MM-DD')),
partition item27 values less than (to_date('1994-03-01','YYYY-MM-DD')),
partition item28 values less than (to_date('1994-04-01','YYYY-MM-DD')),
partition item29 values less than (to_date('1994-05-01','YYYY-MM-DD')),
partition item30 values less than (to_date('1994-06-01','YYYY-MM-DD')),
partition item31 values less than (to_date('1994-07-01','YYYY-MM-DD')),
partition item32 values less than (to_date('1994-08-01','YYYY-MM-DD')),
partition item33 values less than (to_date('1994-09-01','YYYY-MM-DD')),
partition item34 values less than (to_date('1994-10-01','YYYY-MM-DD')),
partition item35 values less than (to_date('1994-11-01','YYYY-MM-DD')),
partition item36 values less than (to_date('1994-12-01','YYYY-MM-DD')),
partition item37 values less than (to_date('1995-01-01','YYYY-MM-DD')),
partition item38 values less than (to_date('1995-02-01','YYYY-MM-DD')),
partition item39 values less than (to_date('1995-03-01','YYYY-MM-DD')),
partition item40 values less than (to_date('1995-04-01','YYYY-MM-DD')),
partition item41 values less than (to_date('1995-05-01','YYYY-MM-DD')),
partition item42 values less than (to_date('1995-06-01','YYYY-MM-DD')),
partition item43 values less than (to_date('1995-07-01','YYYY-MM-DD')),
partition item44 values less than (to_date('1995-08-01','YYYY-MM-DD')),
partition item45 values less than (to_date('1995-09-01','YYYY-MM-DD')),
partition item46 values less than (to_date('1995-10-01','YYYY-MM-DD')),
partition item47 values less than (to_date('1995-11-01','YYYY-MM-DD')),
partition item48 values less than (to_date('1995-12-01','YYYY-MM-DD')),
partition item49 values less than (to_date('1996-01-01','YYYY-MM-DD')),
partition item50 values less than (to_date('1996-02-01','YYYY-MM-DD')),
partition item51 values less than (to_date('1996-03-01','YYYY-MM-DD')),
partition item52 values less than (to_date('1996-04-01','YYYY-MM-DD')),
partition item53 values less than (to_date('1996-05-01','YYYY-MM-DD')),
partition item54 values less than (to_date('1996-06-01','YYYY-MM-DD')),
partition item55 values less than (to_date('1996-07-01','YYYY-MM-DD')),
partition item56 values less than (to_date('1996-08-01','YYYY-MM-DD')),
partition item57 values less than (to_date('1996-09-01','YYYY-MM-DD')),
partition item58 values less than (to_date('1996-10-01','YYYY-MM-DD')),
partition item59 values less than (to_date('1996-11-01','YYYY-MM-DD')),
partition item60 values less than (to_date('1996-12-01','YYYY-MM-DD')),
partition item61 values less than (to_date('1997-01-01','YYYY-MM-DD')),
partition item62 values less than (to_date('1997-02-01','YYYY-MM-DD')),
partition item63 values less than (to_date('1997-03-01','YYYY-MM-DD')),
partition item64 values less than (to_date('1997-04-01','YYYY-MM-DD')),
partition item65 values less than (to_date('1997-05-01','YYYY-MM-DD')),
partition item66 values less than (to_date('1997-06-01','YYYY-MM-DD')),
partition item67 values less than (to_date('1997-07-01','YYYY-MM-DD')),
partition item68 values less than (to_date('1997-08-01','YYYY-MM-DD')),
partition item69 values less than (to_date('1997-09-01','YYYY-MM-DD')),
partition item70 values less than (to_date('1997-10-01','YYYY-MM-DD')),
partition item71 values less than (to_date('1997-11-01','YYYY-MM-DD')),
partition item72 values less than (to_date('1997-12-01','YYYY-MM-DD')),
partition item73 values less than (to_date('1998-01-01','YYYY-MM-DD')),
partition item74 values less than (to_date('1998-02-01','YYYY-MM-DD')),
partition item75 values less than (to_date('1998-03-01','YYYY-MM-DD')),
partition item76 values less than (to_date('1998-04-01','YYYY-MM-DD')),
partition item77 values less than (to_date('1998-05-01','YYYY-MM-DD')),
partition item78 values less than (to_date('1998-06-01','YYYY-MM-DD')),
partition item79 values less than (to_date('1998-07-01','YYYY-MM-DD')),
partition item80 values less than (to_date('1998-08-01','YYYY-MM-DD')),
partition item81 values less than (to_date('1998-09-01','YYYY-MM-DD')),
partition item82 values less than (to_date('1998-10-01','YYYY-MM-DD')),
partition item83 values less than (to_date('1998-11-01','YYYY-MM-DD')),
partition item84 values less than (MAXVALUE))
as select * from lineitem_org;

ALTER TABLE lineitem MOVE PARTITION item1 COMPRESS;
ALTER TABLE lineitem MOVE PARTITION item2 COMPRESS;
ALTER TABLE lineitem MOVE PARTITION item3 COMPRESS;
..
ALTER TABLE lineitem MOVE PARTITION item81 COMPRESS;
ALTER TABLE lineitem MOVE PARTITION item82 COMPRESS;
ALTER TABLE lineitem MOVE PARTITION item83 COMPRESS;
ALTER TABLE lineitem MOVE PARTITION item84 COMPRESS;
ALTER TABLE lineitem MOVE PARTITION item85 COMPRESS;