DB2分区表如何区分索引是分区索引还是非分区索引
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了DB2分区表如何区分索引是分区索引还是非分区索引，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3758字，纯文字阅读大概需要6分钟。
内容图文
问题描述：
经常有人问，我分区表里的索引到底是分区索引还是非分区索引？
因为是否是分区索引涉及到detach分区的时候是否会耗费大量的时间做异步索引清理：如果是非分区索引，则异步索引清理需要大量时间。
总体结论：
--对于唯一索引或者主健，如果包含了分区健，则默认是分区索引；如果不包含分区健，则默认是非分区索引。
--对于非唯一索引，默认都是分区索引。
测试过程
DB2版本为10.5
$ db2 "create table p1 (col1 int not null, col2 int not null, col3 int not null) partition by range(col2)(partition part1 starting 1 ending 5, partition part2 starting 6 ending 10, partition part3 starting 11 ending 15)"
1. 唯一索引
$ db2 "create unique index u_idx1 on p1 (col1)"
$ db2 "create unique index u_idx1_2 on p1 (col1,col2)"
$ db2 "create unique index u_idx1_3 on p1 (col1,col3)"
$ db2look -d sample -a -e -t p1
CREATE UNIQUE INDEX "DB2TST "."U_IDX1" ON "DB2TST "."P1"
("COL1" ASC)
NOT PARTITIONED IN "TBS2"
COMPRESS NO
INCLUDE NULL KEYS ALLOW REVERSE SCANS;
-- DDL Statements for Indexes on Table "DB2TST "."P1"
SET SYSIBM.NLS_STRING_UNITS = 'SYSTEM';
CREATE UNIQUE INDEX "DB2TST "."U_IDX1_2" ON "DB2TST "."P1"
("COL1" ASC,
"COL2" ASC)
PARTITIONED
COMPRESS NO
INCLUDE NULL KEYS ALLOW REVERSE SCANS;
-- DDL Statements for Indexes on Table "DB2TST "."P1"
SET SYSIBM.NLS_STRING_UNITS = 'SYSTEM';
CREATE UNIQUE INDEX "DB2TST "."U_IDX1_3" ON "DB2TST "."P1"
("COL1" ASC,
"COL3" ASC)
NOT PARTITIONED IN "TBS2"
COMPRESS NO
INCLUDE NULL KEYS ALLOW REVERSE SCANS;
结论：对于唯一索引，如果索引中包含了分区健，默认是分区索引。如果索引中不包含分区健，默认是非分区索引。
问题：如果是不包含分区健的唯一索引，想做成分区的怎么破？答：没有办法，创建的时候会报错 SQL20303N
$ db2 "create unique index u_idx3 on p1 (col3) partitioned"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL20303N The partitioned unique index was not created because either the
index definition did not include all of the partitioning columns, or the index
was being created over XML data. SQLSTATE=42990
2. 主健
删表重建
$ db2 "alter table p1 add primary key(col1)"
$ db2 "select varchar(INDNAME,40) as idxname from syscat.indexpartitions where TABNAME='P1'"
IDXNAME
----------------------------------------
0 record(s) selected.
$ db2 "alter table p1 drop primary key"
$ db2 "alter table p1 add primary key(col1,col2)"
$ db2 "select varchar(INDNAME,40) as idxname from syscat.indexpartitions where TABNAME='P1'"
IDXNAME
----------------------------------------
SQL200122112144130
SQL200122112144130
SQL200122112144130
3 record(s) selected.
结论：主健实际上是唯一索引，因此效果跟唯一索引类似，即：
如果主健中包含了分区健，则主健对应索引是分区索引。 如果主健中不包含分区健，则索引是非分区索引。
3. 非唯一索引
删表重建
$ db2 "create index idx1 on p1 (col1)"
$ db2 "create index idx1_2 on p1 (col1,col2)"
$ db2 "create index idx1_3 on p1 (col1,col3)"
$ db2look -d sample -a -e -t p1
CREATE INDEX "DB2TST "."IDX1" ON "DB2TST "."P1"
("COL1" ASC)
PARTITIONED
COMPRESS NO
INCLUDE NULL KEYS ALLOW REVERSE SCANS;
-- DDL Statements for Indexes on Table "DB2TST "."P1"
SET SYSIBM.NLS_STRING_UNITS = 'SYSTEM';
CREATE INDEX "DB2TST "."IDX1_2" ON "DB2TST "."P1"
("COL1" ASC,
"COL2" ASC)
PARTITIONED
COMPRESS NO
INCLUDE NULL KEYS ALLOW REVERSE SCANS;
-- DDL Statements for Indexes on Table "DB2TST "."P1"
SET SYSIBM.NLS_STRING_UNITS = 'SYSTEM';
CREATE INDEX "DB2TST "."IDX1_3" ON "DB2TST "."P1"
("COL1" ASC,
"COL3" ASC)
PARTITIONED
COMPRESS NO
INCLUDE NULL KEYS ALLOW REVERSE SCANS;
结论：非唯一索引默认都是分区索引。
匿_名_用_户 发布了302 篇原创文章 · 获赞 79 · 访问量 70万+ 他的留言板 关注内容总结
以上是互联网集市为您收集整理的DB2分区表如何区分索引是分区索引还是非分区索引全部内容，希望文章能够帮你解决DB2分区表如何区分索引是分区索引还是非分区索引所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。