DB2: Reorg之后表中记录在磁盘上存放的顺序

对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排序存放。