admin_move_table的重组机制验证（cluster index）

最新推荐文章于 2020-10-20 15:02:16 发布

水墨胭脂

最新推荐文章于 2020-10-20 15:02:16 发布

阅读量577

点赞数

分类专栏： DB2

本文链接：https://blog.csdn.net/nayanminxing/article/details/74502362

版权

本文通过实例探讨了在数据库操作中，admin_move_table如何利用cluster index进行表重组。初始实验发现索引未被使用，经研究发现primary key只能为REG类型。通过创建CLUSTER索引，成功使admin_move_table在移动表后按id顺序排列。详细步骤包括创建测试表，插入数据，模拟插入、更新和删除操作，分析数据存储顺序，并观察INIT和COPY阶段的索引变化，最终在SWAP阶段实现id顺序排序。

摘要由CSDN通过智能技术生成

最开始是发现文档中示例中：

COPY_OPTS                    BY_KEY,OVER_INDEX

而我的实验结果却是：

COPY_OPTS                        OVER_INDEX,ARRAY_INSERT,NON_CLUSTER

就是没有用到索引了哦，为什么？最后查看了一下syscat.indexes中的indextype:
Type of index.

BLOK = Block index
CLUS = Clustering index (controls the physical placement of newly inserted rows)
REG = Regular index

应该id列是CLUS的索引才对，但是primary key只能是REG的，所以重新定义了CLUSTER索引，然后admin_move_table就能重组了，会把打乱的id顺序排序好。

修正版过程：

这次的表，字段多一些，建索引，然后表中行的顺序打乱一下，然后看看移动之后会否变的有顺序。

首先我们创建测试数据库和测试表：

[root@bogon tmp]# touch /opt/var/orgsp2
[root@bogon tmp]# chown db2inst1 /opt/var/orgsp2

db2 "create database testmove"

db2 connect to testmove

db2 "create tablespace orgsp2 managed by database using (FILE '/opt/var/orgsp2' 64M)"

db2 "create table t1 (id int not null primary key,name varchar(20), desc varchar(20)) IN orgsp2"

db2 "create table t2 (id int not null primary key,name varchar(20)) IN orgsp2"

db2 "create table t3 (id int not null primary key,name varchar(200)) IN orgsp2"

db2 "create table t4 (id int not null,name varchar(20),sex smallint,address varchar(200),education char(4)) IN orgsp2"

db2 "CREATE UNIQUE INDEX t4_pk_index ON db2inst1.t4(id) CLUSTER"

[db2inst1@db22 opt]$  db2 "select indname,indschema,tabname,tabschema,indextype,clusterratio from syscat.indexes where TABNAME IN ('T1','T2','T3','T4','T5')"

INDNAME             INDSCHEMA   TABNAME   TABSCHEMA  INDEXTYPE CLUSTERRATIO
------------------- ----------- --------- ---------- --------- ------------
SQL170705200828370  SYSIBM      T1        DB2INST1   REG                 -1
SQL170705200828640  SYSIBM      T2        DB2INST1   REG                 -1
SQL170705200828840  SYSIBM      T3        DB2INST1   REG                 -1
T4_PK_INDEX         DB2INST1    T4        DB2INST1   CLUS                -1

  4 record(s) selected.

插入初始化测试数据

db2 "insert into t4 with c1(col1) as (values(3000) union all select c1.col1 +1 from c1 where c1.col1<10800) select c1.col1,'t4LTT4','0','T4t4T4t4T4t4T4t4T4t4T4t4T4t4t4t4t4t4t4t4t4asdfskdfafasfsfsdfasdfjsadfla s sfdasdfsdfadsfas','benk' from c1"

db2 "insert into t1 with c1(col1) as (values(1) union all select c1.col1 +1 from c1 where c1.col1<18000) select c1.col1,'ltt1','this is table t1 ' from c1"

db2 "insert into t2 with c1(col1) as (values(10000) union all select c1.col1 +1 from c1 where c1.col1<21000) select c1.col1,'this is table t2 ' from c1"

db2 "insert into t3 with c1(col1) as (values(1) union all select c1.col1 +1 from c1 where c1.col1<13000) select c1.col1,'T3t3T3t3T3t3T3t3T3t3T3t3T3t3T3t3t3t3t3t3t3t3t3 sdflksadfsa dfjdslkfjsdfjaslkdfsdfsadlfkjsdlkfdsf' from c1"

... ... ...
 Used pages                           = 1632
 Free pages                           = 14720
 High water mark (pages)              = 1632

然后模拟t4表的插入更新和删除：



... ... ...
 Used pages                           = 1920
 Free pages                           = 14432
 High water mark (pages)              = 1920

这样：数据的存储顺序大体上是：
→3000~10800
→100~2000
→21000~23000
→16000~19600
→14400~15600

High water mark:  1920 pages, 60 extents (extents #0 - 59)


 [0000] 65534 0x0e   [0001] 65534 0x0e   [0002] 65535 0x00   [0003]     4 0x40* 
 [0004]     4 0x00*  [0005]     4 0x41*  [0006]     4 0x01*  [0007]     5 0x40* 
 [0008]     5 0x00*