admin_move_table的重组机制验证（cluster index）
来源：互联网 发布：五毛特效app软件 编辑：程序博客网 时间：2024/05/16 18:03
最开始是发现文档中示例中：
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/orgsp2db2 "create database testmove"db2 connect to testmovedb2 "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 -1SQL170705200828640 SYSIBM T2 DB2INST1 REG -1SQL170705200828840 SYSIBM T3 DB2INST1 REG -1T4_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* [0009] 5 0x41* [0010] 5 0x01* [0011] 6 0x40* [0012] 6 0x00* [0013] 6 0x41* [0014] 6 0x01* [0015] 7 0x40* [0016] 7 0x00* [0017] 7 0x41* [0018] 7 0x01* [0019] 7 0x00 [0020] 7 0x00 [0021] 7 0x00 [0022] 7 0x00 [0023] 7 0x00 [0024] 7 0x00 [0025] 7 0x01 [0026] 7 0x00 [0027] 4 0x00 [0028] 4 0x00 [0029] 4 0x01 [0030] 4 0x00 [0031] 4 0x00 [0032] 4 0x00 [0033] 4 0x01 [0034] 4 0x00 [0035] 5 0x00 [0036] 5 0x01 [0037] 5 0x00 [0038] 5 0x00 [0039] 6 0x00 [0040] 6 0x00 [0041] 6 0x00 [0042] 6 0x00 [0043] 6 0x00 [0044] 6 0x00 [0045] 6 0x01 [0046] 6 0x00 [0047] 6 0x00 [0048] 6 0x00 [0049] 6 0x00 [0050] 6 0x00 [0051] 7 0x00 [0052] 7 0x00 [0053] 7 0x00 [0054] 7 0x00 [0055] 7 0x00 [0056] 7 0x01 [0057] 7 0x00 [0058] 7 0x00 [0059] 7 0x00 表7占据了15-26,51-59 这么多个EXTENT；
导一下表7的数据，然后看一下顺序：
db2dart testmove /DD /tsi 3 /oi 7 /ps 0 /np 0 /v y /rptn t4-initdata.dart less t4-initdata.dart | grep 'Table Data Record' -A 10|grep 'Value ='根据表的导出顺序，可以看出插入记录的顺序就是存储的顺序
INIT
db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','','','','','','INIT')" Result set 1 -------------- KEY VALUE -------------------------------- -------------------------------------------------------------------------------------------------------------------------------- STATUS COPY AUTHID DB2INST1 VERSION 11.01.0101 INIT_START 2017-07-05-17.17.19.596661 INDEXSCHEMA SYSIBM INDEXNAME SQL170705170714560 TARGET T4AAAAV0t STAGING T4AAAAV0s INIT_END 2017-07-05-17.17.23.134999 UTILITY_INVOCATION_ID 0100000001000000080000000000000000002017070517172319853800000000 10 record(s) selected. Return Status = 0利用了集群索引：
[db2inst1@db22 opt]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','','','','','','INIT')" Result set 1 -------------- KEY VALUE -------------------------------- -------------------------------------------------------------------------------------------------------------------------------- AUTHID DB2INST1 COPY_INDEXNAME T4_PK_INDEX COPY_INDEXSCHEMA DB2INST1 INDEXNAME T4_PK_INDEX INDEXSCHEMA DB2INST1 INIT_END 2017-07-05-20.16.56.562890 INIT_START 2017-07-05-20.16.54.655873 STAGING T4AAAAV0s STATUS COPY TARGET T4AAAAV0t UTILITY_INVOCATION_ID 0100000001000000080000000000000000002017070520165660158000000000 VERSION 11.01.0101 12 record(s) selected. Return Status = 0 [0060] 8 0x40* [0061] 8 0x00* [0062] 9 0x40* [0063] 9 0x00* [0064] 9 0x41* [0065] 9 0x01* 只看最后几行可以看到INIT阶段初始化了目标表，staging table:
注意：初始化的目标表没有索引哦。staging table是有索引的。
[db2inst1@db22 tmp]$ db2 "select indname,indschema,tabschema,tabname,colnames from syscat.indexes where tabschema='DB2INST1'"INDNAME INDSCHEMA TABSCHEMA TABNAME COLNAMES ------------------------ ----------- ----------- ----------- ----------SQL170630153727970 SYSIBM DB2INST1 T1 +ID T4AAAAV0sAFVT7zg DB2INST1 DB2INST1 T4AAAAV0s +ID SQL170630153728320 SYSIBM DB2INST1 T2 +ID SQL170630153728600 SYSIBM DB2INST1 T3 +ID SQL170630153755800 SYSIBM DB2INST1 T4 +ID 可以看到登台表的索引列是id列。
COPY
使用了索引之后的：
[db2inst1@db22 opt]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','','','','','','COPY')" Result set 1 -------------- KEY VALUE -------------------------------- -------------------------------------------------------------------------------------------------------------------------------- AUTHID DB2INST1 COPY_END 2017-07-05-20.20.48.713486 COPY_INDEXNAME T4_PK_INDEX COPY_INDEXSCHEMA DB2INST1 COPY_OPTS OVER_INDEX,ARRAY_INSERT COPY_START 2017-07-05-20.20.46.914488 COPY_TOTAL_ROWS 12468 INDEXNAME T4_PK_INDEX INDEXSCHEMA DB2INST1 INDEX_CREATION_TOTAL_TIME 1 INIT_END 2017-07-05-20.16.56.562890 INIT_START 2017-07-05-20.16.54.655873 REPLAY_START 2017-07-05-20.20.48.714188 STAGING T4AAAAV0s STATUS REPLAY TARGET T4AAAAV0t UTILITY_INVOCATION_ID 0100000001000000080000000000000000002017070520165660158000000000 VERSION 11.01.0101 18 record(s) selected. Return Status = 0 [0060] 8 0x40* [0061] 8 0x00* [0062] 9 0x40* [0063] 9 0x00* [0064] 9 0x41* [0065] 9 0x01* [0066] 8 0x41* [0067] 8 0x01* [0068] 8 0x00 [0069] 8 0x00 [0070] 8 0x00 [0071] 8 0x00 [0072] 8 0x00 [0073] 8 0x00 [0074] 8 0x01 [0075] 8 0x00 [0076] 8 0x00 [0077] 8 0x00 [0078] 8 0x00 [0079] 8 0x00 可以看到，相比上一步，表8建了索引，随后表8占据了66-79这几个块。COPY阶段先建索引1。
现在审视一下表8的数据：
db2dart testmove /DD /tsi 3 /oi 8 /ps 0 /np 0 /v y /rptn t4td.dartless t4td.dart | grep 'Table Data Record' -A 10|grep 'Value =' > orderornot.orderless less less orderornot.order 查看导出的数据，可以发现id是顺序的了。
执行SWAP:
[db2inst1@db22 opt]$ db2 "CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','T4','','', '','','','','','','SWAP')" Result set 1 -------------- KEY VALUE -------------------------------- -------------------------------------------------------------------------------------------------------------------------------- AUTHID DB2INST1 CLEANUP_END 2017-07-05-20.26.22.800814 CLEANUP_START 2017-07-05-20.26.22.272712 COPY_END 2017-07-05-20.20.48.713486 COPY_INDEXNAME T4_PK_INDEX COPY_INDEXSCHEMA DB2INST1 COPY_OPTS OVER_INDEX,ARRAY_INSERT COPY_START 2017-07-05-20.20.46.914488 COPY_TOTAL_ROWS 12468 INDEXNAME T4_PK_INDEX INDEXSCHEMA DB2INST1 INDEX_CREATION_TOTAL_TIME 1 INIT_END 2017-07-05-20.16.56.562890 INIT_START 2017-07-05-20.16.54.655873 ORIGINAL_TBLSIZE 2688 REPLAY_END 2017-07-05-20.26.21.776445 REPLAY_START 2017-07-05-20.20.48.714188 REPLAY_TOTAL_ROWS 0 REPLAY_TOTAL_TIME 1 STATUS COMPLETE SWAP_END 2017-07-05-20.26.22.189495 SWAP_RETRIES 0 SWAP_START 2017-07-05-20.26.21.854931 UTILITY_INVOCATION_ID 0100000001000000080000000000000000002017070520165660158000000000 VERSION 11.01.0101 25 record(s) selected. Return Status = 0High water mark: 2560 pages, 80 extents (extents #0 - 79) [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* [0009] 5 0x41* [0010] 5 0x01* [0011] 6 0x40* [0012] 6 0x00* [0013] 6 0x41* [0014] 6 0x01* [0015] == EMPTY == [0016] == EMPTY == [0017] == EMPTY == [0018] == EMPTY == [0019] == EMPTY == [0020] == EMPTY == [0021] == EMPTY == [0022] == EMPTY == [0023] == EMPTY == [0024] == EMPTY == [0025] == EMPTY == [0026] == EMPTY == [0027] 4 0x00 [0028] 4 0x00 [0029] 4 0x01 [0030] 4 0x00 [0031] 4 0x00 [0032] 4 0x00 [0033] 4 0x01 [0034] 4 0x00 [0035] 5 0x00 [0036] 5 0x01 [0037] 5 0x00 [0038] 5 0x00 [0039] 6 0x00 [0040] 6 0x00 [0041] 6 0x00 [0042] 6 0x00 [0043] 6 0x00 [0044] 6 0x00 [0045] 6 0x01 [0046] 6 0x00 [0047] 6 0x00 [0048] 6 0x00 [0049] 6 0x00 [0050] 6 0x00 [0051] == EMPTY == [0052] == EMPTY == [0053] == EMPTY == [0054] == EMPTY == [0055] == EMPTY == [0056] == EMPTY == [0057] == EMPTY == [0058] == EMPTY == [0059] == EMPTY == [0060] 8 0x40* [0061] 8 0x00* [0062] == EMPTY == [0063] == EMPTY == [0064] == EMPTY == [0065] == EMPTY == [0066] 8 0x41* [0067] 8 0x01* [0068] 8 0x00 [0069] 8 0x00 [0070] 8 0x00 [0071] 8 0x00 [0072] 8 0x00 [0073] 8 0x00 [0074] 8 0x01 [0075] 8 0x00 [0076] 8 0x00 [0077] 8 0x00 [0078] 8 0x00 [0079] 8 0x00 阅读全文
0 0
- admin_move_table的重组机制验证（cluster index）
- admin_move_table的重组机制验证（失败了）
- 实验测试admin_move_table的机制（1）
- Cluster Index和Noncluster Index的区别
- CLUSTER INDEX
- libnids 中ipfrag重组的实现机制
- rebuild index 的机制
- cluster index in sqlserver
- Eclipse JDT的Index机制
- L3---fragment: 3层的分片/重组机制
- 转载：REDHAT CLUSTER 的心跳机制
- DB2 admin_move_table
- PostgreSQL cluster table using index
- 通过QDisk增强Red Hat Cluster Suite的仲裁机制（Quorum）--转载
- 通过QDisk增强Red Hat Cluster Suite的仲裁机制（Quorum）
- rebuild index online的锁机制浅析
- rebuild index online的锁机制浅析
- phalcon的验证机制
- 网络编程之UDP协议学习
- 本地删除文件svn找回
- MySQL索引优化——覆盖索引
- 【电路第七章之III篇】一阶电路的全响应分析
- 揭开跨域的面纱（上）
- admin_move_table的重组机制验证（cluster index）
- SVN update拉取后撤回
- Tomcat8.0配置虚拟主机
- Java Set集合
- sam2gff3
- 【JZOJ 1319】 邮递员
- 面试题2——单例模式
- AVL树详解
- android 检测网线连接
