今天学习分区索引；顾名思议就是分区表上创建的索引。
顺便引用下官方分区索引的相关信息如下：
https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/CREATE-INDEX.html
分区表索引分为LOCAL索引与GLOBAL索引，LOCAL索引与某个具体分区绑定，而GLOBAL索引则对应整个分区表；（不指定关键字，默认创建GLOBAL分区索引）
创建分区索引语法：
CREATE [ UNIQUE ] INDEX [ [schema_name.]index_name ] ON table_name [ USING method ]
( {{ column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, ...] )
[ LOCAL [ ( { PARTITION index_partition_name [ TABLESPACE index_partition_tablespace ] } [, ...] ) ] | GLOBAL ]
[ WITH ( { storage_parameter = value } [, ...] ) ]
[ TABLESPACE tablespace_name ];
约束限制：
分区表上不支持创建部分索引。
分区表创建GLOBAL索引时，存在以下约束条件：
- 不支持表达式索引、部分索引
- 不支持列存表
- 仅支持B-tree索引
在相同属性列上，分区LOCAL索引与GLOBAL索引不能共存。
GLOBAL索引，最大支持31列。
如果alter语句不带有UPDATE GLOBAL INDEX，那么原有的GLOBAL索引将失效，查询时将使用其他索引进行查询；如果alter语句带有UPDATE GLOBAL INDEX，原有的GLOBAL索引仍然有效，并且索引功能正确。
课程练习
1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3
penGauss=# create table will.products(id int,name char(30)) partition by range(id) (partition p1 values less than(100),partition p2 values less than (200));
CREATE TABLE
openGauss=# create index p_index1 on will.products (id) local;
CREATE INDEX
openGauss=# create index p_index2 on will.products (id) local (partition p_pindex1,partition p_pindex2);
CREATE INDEX
openGauss=# create index p_index3 on will.products (id) global;
ERROR: Global and local partition index should not be on same column
openGauss=# create index p_index3 on will.products (name) global;
CREATE INDEX
2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引
openGauss=# \d+ will.products;
Table "will.products"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
id | integer | | plain | |
name | character(30) | | extended | |
Indexes:
"p_index1" btree (id) LOCAL(PARTITION p1_id_idx, PARTITION p2_id_idx) TABLESPACE pg_default
"p_index2" btree (id) LOCAL(PARTITION p_pindex1, PARTITION p_pindex2) TABLESPACE pg_default
"p_index3" btree (name) TABLESPACE pg_default
Range partition by(id)
Number of partition: 2 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
openGauss=# create tablespace tbs_will relative location 'tbs_will';
CREATE TABLESPACE
openGauss=# alter index will.p_index1 move partition p1_id_idx tablespace tbs_will;
ALTER INDEX
openGauss=# alter index will.p_index1 rename partition p1_id_idx to p1_id_index;
ALTER INDEX
openGauss=# \d+ will.products;
Table "will.products"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
id | integer | | plain | |
name | character(30) | | extended | |
Indexes:
"p_index1" btree (id) LOCAL(PARTITION p1_id_index TABLESPACE tbs_will, PARTITION p2_id_idx) TABLESPACE pg_default
"p_index2" btree (id) LOCAL(PARTITION p_pindex1, PARTITION p_pindex2) TABLESPACE pg_default
"p_index3" btree (name) TABLESPACE pg_default
Range partition by(id)
Number of partition: 2 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
3.在分区表索引2上，重建单个索引分区和分区上的所有索引
openGauss=# reindex index will.p_index2 partition p_pindex1;
REINDEX
openGauss=# reindex table will.products partition p1;
REINDEX
4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
openGauss=# \d+ will.products;
Table "will.products"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
id | integer | | plain | |
name | character(30) | | extended | |
Indexes:
"p_index1" btree (id) LOCAL(PARTITION p1_id_index TABLESPACE tbs_will, PARTITION p2_id_idx) TABLESPACE pg_default
"p_index2" btree (id) LOCAL(PARTITION p_pindex1, PARTITION p_pindex2) TABLESPACE pg_default
"p_index3" btree (name) TABLESPACE pg_default
Range partition by(id)
Number of partition: 2 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
openGauss=# select * from pg_indexes where tablename='products';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------
will | products | p_index1 | | CREATE INDEX p_index1 ON will.products USING btree (id) LOCAL(PARTITION p1_id_index TABLESPACE tbs_will, PARTITION p2_id_idx) TABLESPACE pg_default
will | products | p_index2 | | CREATE INDEX p_index2 ON will.products USING btree (id) LOCAL(PARTITION p_pindex1, PARTITION p_pindex2) TABLESPACE pg_default
will | products | p_index3 | | CREATE INDEX p_index3 ON will.products USING btree (name) TABLESPACE pg_default
(3 rows)
openGauss=# openGauss=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoa
strelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | i
ntervaltablespace | interval | boundaries | transit | reloptions | relfrozenxid64
-------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+-------
--------+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+--
------------------+----------+------------+---------+---------------------------------------------------+----------------
products | r | 24676 | 0 | 0 | r | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 0 | | 1 |
| | | | {orientation=row,compression=no,wait_clean_gpi=n} | 0
p1 | p | 24676 | 0 | 0 | r | 24680 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 112022 | | |
| | {100} | | {orientation=row,compression=no} | 112022
p2 | p | 24676 | 0 | 0 | r | 24681 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 112022 | | |
| | {200} | | {orientation=row,compression=no} | 112022
p2_id_idx | x | 24682 | 0 | 0 | n | 24684 | 0 | 1 | 0 | 0 |
0 | 0 | 24681 | t | 0 | 0 | 0 | 0 | 0 | | |
| | | | | 0
p_pindex2 | x | 24685 | 0 | 0 | n | 24687 | 0 | 1 | 0 | 0 |
0 | 0 | 24681 | t | 0 | 0 | 0 | 0 | 0 | | |
| | | | | 0
p1_id_index | x | 24682 | 0 | 0 | n | 24692 | 24689 | 1 | 0 | 0 |
0 | 0 | 24680 | t | 0 | 0 | 0 | 0 | 0 | | |
| | | | | 0
p_pindex1 | x | 24685 | 0 | 0 | n | 24693 | 0 | 1 | 0 | 0 |
0 | 0 | 24680 | t | 0 | 0 | 0 | 0 | 0 | | |
| | | | | 0
(7 rows)
5.删除索引、表和表空间
openGauss=# drop index will.p_index1;
DROP INDEX
openGauss=# drop index will.p_index2;
DROP INDEX
openGauss=# drop table will.products;
DROP TABLE
openGauss=# drop tablespace tbs_will;
DROP TABLESPACE
openGauss=# 「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
