Oracle & Starcraft

oracle从9i r2开始推出了compress table的功能，compress table能提供良好的压缩性能，十分适用于存储历史数据。的打算

compress table需要通过创建table时指定compress子句

SQL 9I>create table testcom3(a number) compress;
Table created.

需要通过批量导入数据才能实现compress

1.alter table move
2.create table as select
3.insert /*+ APPEND */
4.direct path sqlldr

下面来看一些例子

SQL 9I>create table test(a varchar2(10),b number);
Table created.
begin
for i in 1..1000 loop
insert into test values(to_char(mod(i,9)),i);
commit;
end loop;
end;
/

PL/SQL procedure successfully completed.
SQL 9I>create table testcom1 compress as select * from test order by a;
Table created.
SQL 9I>set serveroutput on
SQL 9I>exec show_space(’TEST’);
Unformatted Blocks ………………… 32
FS1 Blocks (0-25) ………………… 0
FS2 Blocks (25-50) ………………… 0
FS3 Blocks (50-75) ………………… 1
FS4 Blocks (75-100)………………… 26
Full Blocks ………………… 1
Total Blocks………………………. 128
Total Bytes……………………….. 1,048,576
Total MBytes………………………. 1
Unused Blocks……………………… 64
Unused Bytes………………………. 524,288
Last Used Ext FileId……………….. 11
Last Used Ext BlockId………………. 904
Last Used Block……………………. 64
PL/SQL procedure successfully completed.

SQL 9I>exec show_space(’TESTCOM1′);
Unformatted Blocks ………………… 0
FS1 Blocks (0-25) ………………… 0
FS2 Blocks (25-50) ………………… 0
FS3 Blocks (50-75) ………………… 0
FS4 Blocks (75-100)………………… 0
Full Blocks ………………… 2
Total Blocks………………………. 128
Total Bytes……………………….. 1,048,576
Total MBytes………………………. 1
Unused Blocks……………………… 122
Unused Bytes………………………. 999,424
Last Used Ext FileId……………….. 11
Last Used Ext BlockId………………. 1,032
Last Used Block……………………. 6
PL/SQL procedure successfully completed.

可以看到compress table提供了良好的压缩比

另外创建compress table的时候还需要注意的是order by子句的功能

create table test2(a varchar2(10),b varchar2(10),c varchar2(10));

begin
for i in 1000000000..1000100000 loop
insert into test2 values(i,’1′,to_char(mod(i,100)));
commit;
end loop;
end;
/

create table testcom4 compress as select * from test2 order by c;

create table testcom5 compress as select * from test2;

SQL 9I>exec show_space(’TEST2′);
Unformatted Blocks ………………… 0
FS1 Blocks (0-25) ………………… 0
FS2 Blocks (25-50) ………………… 0
FS3 Blocks (50-75) ………………… 0
FS4 Blocks (75-100)………………… 24
Full Blocks ………………… 288
Total Blocks………………………. 384
Total Bytes……………………….. 3,145,728
Total MBytes………………………. 3
Unused Blocks……………………… 64
Unused Bytes………………………. 524,288
Last Used Ext FileId……………….. 13
Last Used Ext BlockId………………. 1,032
Last Used Block……………………. 64
PL/SQL procedure successfully completed.

SQL 9I>exec show_space(’TESTCOM4′);
Unformatted Blocks ………………… 0
FS1 Blocks (0-25) ………………… 0
FS2 Blocks (25-50) ………………… 0
FS3 Blocks (50-75) ………………… 0
FS4 Blocks (75-100)………………… 0
Full Blocks ………………… 226
Total Blocks………………………. 256
Total Bytes……………………….. 2,097,152
Total MBytes………………………. 2
Unused Blocks……………………… 24
Unused Bytes………………………. 196,608
Last Used Ext FileId……………….. 12
Last Used Ext BlockId………………. 1,160
Last Used Block……………………. 104
PL/SQL procedure successfully completed.

SQL 9I>exec show_space(’TESTCOM5′);
Unformatted Blocks ………………… 0
FS1 Blocks (0-25) ………………… 0
FS2 Blocks (25-50) ………………… 0
FS3 Blocks (50-75) ………………… 0
FS4 Blocks (75-100)………………… 0
Full Blocks ………………… 259
Total Blocks………………………. 384
Total Bytes……………………….. 3,145,728
Total MBytes………………………. 3
Unused Blocks……………………… 117
Unused Bytes………………………. 958,464
Last Used Ext FileId……………….. 13
Last Used Ext BlockId………………. 1,160
Last Used Block……………………. 11
PL/SQL procedure successfully completed.

可见order by子句对compress影响也是比较大，我们应该指定重复值多并且长度大的列做order by以获得最大的压缩比。

有人会对compress的读写性能表示担忧，但是实际上无论是全表扫描还是通过索引回表扫描压缩表的性能都不会比非压缩表差。至于dml，压缩表应该是不推荐进行dml的，但是当你通过非bulk操作inert 数据时那么这些数据将会不会进行压缩存储，也就是按照普通格式操作，所以效率并不会低，但是还是要避免对压缩表进行dml操作，尤其是update，update将会导致行迁移，从而使压缩表的容量比非压缩表还要大。

全表扫描返回记录数对比：
select count(*) from test2

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.21 0.21 313 315 0 1
——- —— ——– ———- ———- ———- ———- ———-
total 4 0.21 0.21 313 315 0 1

select count(*) from testcom4

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.20 0.20 227 229 0 1
——- —— ——– ———- ———- ———- ———- ———-
total 4 0.20 0.20 227 229 0 1

全表扫描返回全部数据对比：

select * from test2

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 22 0.30 0.29 313 335 0 100001
——- —— ——– ———- ———- ———- ———- ———-
total 24 0.30 0.29 313 335 0 100001

select * from testcom4

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 22 0.29 0.26 227 249 0 100001
——- —— ——– ———- ———- ———- ———- ———-
total 24 0.29 0.26 227 249 0 100001

索引回表对比：

ALTER SESSION SET EVENTS ‘immediate trace name flush_cache’;
ALTER SESSION SET EVENTS ‘immediate trace name flush_cache’;
ALTER SESSION SET EVENTS ‘immediate trace name flush_cache’;
alter session set events’10046 trace name context forever,level 1′;
declare
l_rec test2%rowtype;
begin
for i in 1000000000..1000100000
loop
select * into l_rec from test2 where a = to_char(i);
select * into l_rec from testcom4 where a = to_char(i);
end loop;
end;
/
alter session set events’10046 trace name context off’;

SELECT * from test2 where a = to_char(:b1)

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 100001 1.95 1.76 0 0 0 0
Fetch 100001 2.84 2.69 597 300309 0 100001
——- —— ——– ———- ———- ———- ———- ———-
total 200003 4.79 4.45 597 300309 0 100001

SELECT * from testcom4 where a = to_char(:b1)

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 100001 1.56 1.76 0 0 0 0
Fetch 100001 3.15 2.76 534 300309 0 100001
——- —— ——– ———- ———- ———- ———- ———-
total 200003 4.71 4.53 534 300309 0 100001

排序对比：

select * from test2 order by c

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 22 0.61 0.61 313 315 0 100001
——- —— ——– ———- ———- ———- ———- ———-
total 24 0.61 0.61 313 315 0 100001

Rows Row Source Operation
——- —————————————————
100001 SORT ORDER BY (cr=315 r=313 w=0 time=466969 us)
100001 TABLE ACCESS FULL TEST2 (cr=315 r=313 w=0 time=125852 us)

select * from testcom4 order by c

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 22 0.51 0.50 227 229 0 100001
——- —— ——– ———- ———- ———- ———- ———-
total 24 0.51 0.51 227 229 0 100001

Rows Row Source Operation
——- —————————————————
100001 SORT ORDER BY (cr=229 r=227 w=0 time=369099 us)
100001 TABLE ACCESS FULL TESTCOM4 (cr=229 r=227 w=0 time=120071 us)

select count(*) from test2 group by c

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8 0.30 0.28 313 315 0 100
——- —— ——– ———- ———- ———- ———- ———-
total 10 0.30 0.29 313 315 0 100

select count(*) from testcom4 group by c

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8 0.25 0.24 227 229 0 100
——- —— ——– ———- ———- ———- ———- ———-
total 10 0.25 0.24 227 229 0 100

可以看到compress table的效率并不低，只要用到合适的地方能带来很好的效果。

上面提到update将会导致compress table容量大增

SQL 9I>update testcom4 set c=c;
100001 rows updated.
SQL 9I>commit;
Commit complete.
SQL 9I>exec show_space(’TESTCOM4′);
Unformatted Blocks ………………… 0
FS1 Blocks (0-25) ………………… 0
FS2 Blocks (25-50) ………………… 0
FS3 Blocks (50-75) ………………… 0
FS4 Blocks (75-100)………………… 23
Full Blocks ………………… 857
Total Blocks………………………. 896
Total Bytes……………………….. 7,340,032
Total MBytes………………………. 7
Unused Blocks……………………… 0
Unused Bytes………………………. 0
Last Used Ext FileId……………….. 29
Last Used Ext BlockId………………. 22,792
Last Used Block……………………. 128
PL/SQL procedure successfully completed.

SQL 9I>alter table testcom4 move compress;
Table altered.

SQL 9I>exec show_space(’TESTCOM4′);
Unformatted Blocks ………………… 0
FS1 Blocks (0-25) ………………… 0
FS2 Blocks (25-50) ………………… 0
FS3 Blocks (50-75) ………………… 0
FS4 Blocks (75-100)………………… 0
Full Blocks ………………… 226
Total Blocks………………………. 256
Total Bytes……………………….. 2,097,152
Total MBytes………………………. 2
Unused Blocks……………………… 24
Unused Bytes………………………. 196,608
Last Used Ext FileId……………….. 12
Last Used Ext BlockId………………. 1,416
Last Used Block……………………. 104
PL/SQL procedure successfully completed.

SQL 9I>update testcom4 set a=substr(a,1,4);
100001 rows updated.
SQL 9I>commit;
Commit complete.
SQL 9I>exec show_space(’TESTCOM4′);
Unformatted Blocks ………………… 0
FS1 Blocks (0-25) ………………… 0
FS2 Blocks (25-50) ………………… 0
FS3 Blocks (50-75) ………………… 0
FS4 Blocks (75-100)………………… 2
Full Blocks ………………… 814
Total Blocks………………………. 896
Total Bytes……………………….. 7,340,032
Total MBytes………………………. 7
Unused Blocks……………………… 64
Unused Bytes………………………. 524,288
Last Used Ext FileId……………….. 29
Last Used Ext BlockId………………. 22,792
Last Used Block……………………. 64
PL/SQL procedure successfully completed.

update导致testcom4的容量从2,097,152－>7,340,032,甚至比原始表还要大很多。

下面来看一下compress table的内部存储格式

r0_9ir2=0×0
mec_kdbh9ir2=0×3
r1_9ir2=0×0
76543210
flag_9ir2=——OC
fcls_9ir2[4]={ 0 32768 32768 10 }
perm_9ir2[3]={ 2 0 1 } 下面tab1中col n对应表实际列顺序，tab 1中的col2对应表的col 0(A),col 0对应表的col1(B)，col 1对应表的col2(c)
0×22:pti[0] nrow=1 offs=0 tab 0中有3行，tab 0是一个标记表，记录压缩的列，以下的tab 0记录了压缩过的b,c列重复值，一共有1组。
0×26:pti[1] nrow=444 offs=1 记录tab 1也就是存储非压缩列的数据，在这里是A列，一共拥有444条。
tab 0, row 0, @0×1f79
tl: 7 fb: –H-FL– lb: 0×0 cc: 2
col 0: [ 1] 31 列B值为’1′
col 1: [ 1] 30 列C值为’0′
bindmp: 01 bc 02 c9 31 c9 30 01bc＝444，这个压缩项提供给444行使用
tab 1, row 0, @0×1f69
tl: 16 fb: –H-FL– lb: 0×0 cc: 3
col 0: [ 1] 31
col 1: [ 1] 30
col 2: [10] 31 30 30 30 30 31 35 30 30 30
bindmp: 2c 00 02 03 00 c9 31 30 30 30 30 31 35 30 30 30
bindmp里面是实际储存的值，包含指向tab 0的指针和A列的值。

最后再来看一下update后数据块内部发生了什么变化
SQL 9I>update testcom4 set c=c;
100001 rows updated.

SQL 9I>commit;
Commit complete.

SQL 9I>exec show_space(’TESTCOM4′);
Unformatted Blocks ………………… 0
FS1 Blocks (0-25) ………………… 0
FS2 Blocks (25-50) ………………… 0
FS3 Blocks (50-75) ………………… 0
FS4 Blocks (75-100)………………… 23
Full Blocks ………………… 857
Total Blocks………………………. 896
Total Bytes……………………….. 7,340,032
Total MBytes………………………. 7
Unused Blocks……………………… 0
Unused Bytes………………………. 0
Last Used Ext FileId……………….. 29
Last Used Ext BlockId………………. 22,792
Last Used Block……………………. 128
PL/SQL procedure successfully completed.

SQL 9I>select header_file,header_block from dba_segments where segment_name=’TESTCOM4′;
HEADER_FILE HEADER_BLOCK
———– ————
11 2188

SQL 9I>alter system dump datafile 11 block 2189;
System altered.

tab 0, row 0, @0×1f79
tl: 7 fb: –H-FL– lb: 0×0 cc: 2
col 0: [ 1] 31
col 1: [ 1] 30
bindmp: 01 bc 02 c9 31 c9 30
tab 1, row 0, @0×1f69
tl: 9 fb: –H—– lb: 0×2 cc: 0
nrid: 0×030004fa.0
bindmp: 20 02 00 03 00 04 fa 00 00
发生了行迁移，导致了testcom4容量大增,所以切忌对compress table进行update操作，一定把compress table用到合适的地方。