对DB2的表做重组之后,表中记录在磁盘上的存放顺序可能会发生改变,那么到底是怎么存放的呢?之前写过一篇文章,链接如下:
Db2中"SELECT * FROM TABLE"时返回记录的顺序
这篇文章继续探索表被重组(以下内容只关注离线重组)之后,记录是如何存放的。
先创建如下一张表,表中有两个普通索引:
db2 "create table t1(id int, name char(5))"
db2 "create index t1_idx1 on t1(id)"
db2 "create index t1_idx2 on t1(name)"
db2 "insert into t1 values(333, 'aaa')"
db2 "insert into t1 values(444, 'ddd')"
db2 "insert into t1 values(111, 'bbb')"
db2 "insert into t1 values(222, 'eee')"
db2 "insert into t1 values(555, 'ccc')"
测试1:
inst105@node01:~> db2 "select * from t1"
ID NAME
----------- -----
333 aaa
444 ddd
111 bbb
222 eee
555 ccc
5 record(s) selected.
inst105@node01:~> db2 "reorg table t1"
DB20000I The REORG command completed successfully.
inst105@node01:~> db2 "select * from t1"
ID NAME
----------- -----
333 aaa
444 ddd
111 bbb
222 eee
555 ccc
5 record(s) selected.
结论1: Reorg之后,表中的记录存放顺序可能不会改变。
测试2:
inst105@node01:~> db2 "reorg table t1 index t1_idx1"
DB20000I The REORG command completed successfully.
inst105@node01:~> db2 "select * from t1"
ID NAME
----------- -----
111 bbb
222 eee
333 aaa
444 ddd
555 ccc
5 record(s) selected.
inst105@node01:~> db2 "reorg table t1 index t1_idx2"
DB20000I The REORG command completed successfully.
inst105@node01:~> db2 "select * from t1"
ID NAME
----------- -----
333 aaa
111 bbb
555 ccc
444 ddd
222 eee
5 record(s) selected.
结论2:如果Reorg table命令后面加上了index选项,那么会按照指定的index排序存放。
再来看另外一张表
db2 "create table t2(id int, name char(5))"
db2 "create index t2_idx1 on t2(id) CLUSTER"
db2 "insert into t2 values(333, 'aaa')"
db2 "insert into t2 values(444, 'ddd')"
db2 "insert into t2 values(111, 'bbb')"
db2 "insert into t2 values(222, 'eee')"
db2 "insert into t2 values(555, 'ccc')"
测试3:
inst105@node01:~> db2 "select * from t2"
ID NAME
----------- -----
333 aaa
444 ddd
111 bbb
222 eee
555 ccc
5 record(s) selected.
inst105@node01:~> db2 "reorg table t2"
DB20000I The REORG command completed successfully.
inst105@node01:~> db2 "select * from t2"
ID NAME
----------- -----
111 bbb
222 eee
333 aaa
444 ddd
555 ccc
5 record(s) selected.
结论3:如果表上有cluster index,重组时不加index选项,则重组后表中记录按照cluster index排序存放。