openGauss每日一练第10天|分区表索引

学习目标：

学习openGauss分区表索引

学习内容部分如下：

课程作业：

1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3

使用学习中创建而未删除的表空间和模式。

omm=# create table tpcds.products(id int,num int,name char(20),disp char(20))omm-#
partition by range(id)(partition p1 values less than (100),
omm(# partition p2 values less than (200) tablespace example1,
omm(# partition p3 values less than (MAXVALUE)tablespace example2);
CREATE TABLE

omm=# create index index_1 on tpcds.products(id) local;
CREATE INDEX

omm=# create index index_2 on tpcds.products(id) local(
omm(# partition pct1 tablespace example1 ,partition pct2 tablespace example2,
omm(# partition pct3 tablespace example3);
CREATE INDEX

这里建立时要求分区数量相等：ERROR: number of partitions of LOCAL index must equal that of the underlying table

注意：omm=# create index index_3 on tpcds.products(id) global;会有ERROR: Global and local partition index should not be on same column
omm=# create index index_3 on tpcds.products(num) global;
CREATE INDEX

2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引

omm=# \d+ tpcds.products;
Table "tpcds.products"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
id | integer | | plain | |
num | integer | | plain | |
omm=# name | character(20) | | extended | |
disp | character(20) | | extended | |
Indexes:
"index_1" btree (id) LOCAL(PARTITION p1_id_idx, PARTITION p2_id_idx, PARTITION p3_id_idx) TABLESPACE pg_default
"index_2" btree (id) LOCAL(PARTITION pct1 TABLESPACE example1, PARTITION pct2 TABLESPACE example2, PARTITION pct3 TABLESPACE example3) TABLESPACE pg_default
"index_3" btree (num) TABLESPACE pg_default
Range partition by(id)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no

omm=# alter index tpcds.index_1 move partition p1_id_idx tablespace example1;
ALTER INDEX

omm=# alter index tpcds.index_1 rename partition p2_id_idx to p2_id_idx0;
ALTER INDEX

3.在分区表索引2上，重建单个索引分区和分区上的所有索引

omm=# reindex index tpcds.index_2 partition pct1;
REINDEX
omm=# reindex table tpcds.products partition p1;
REINDEX

4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息

omm=# \d+ tpcds.products;
Table "tpcds.products"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
id | integer | | plain | |
num | integer | | plain | |
name | character(20) | | extended | |
disp | character(20) | | extended | |
Indexes:
"index_1" btree (id) LOCAL(PARTITION p1_id_idx TABLESPACE example1, PARTITION p2_id_idx0, PARTITION p3_id_idx) TABLESPACE pg_default
"index_2" btree (id) LOCAL(PARTITION pct1 TABLESPACE example1, PARTITION pct2 TABLESPACE example2, PARTITION pct3 TABLESPACE example3) TABLESPACE pg_default
"index_3" btree (num) TABLESPACE pg_default
Range partition by(id)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no

omm=# select * from pg_indexes where tablename = 'products' ;
schemaname | tablename | indexname | tablespace |
indexdef
------------+-----------+-----------+------------+----------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
tpcds | products | index_1 | | CREATE INDEX index_1 ON tpcds.products USING btree (id) LOCAL(PARTITION p1_
id_idx TABLESPACE example1, PARTITION p2_id_idx0, PARTITION p3_id_idx) TABLESPACE pg_default
tpcds | products | index_2 | | CREATE INDEX index_2 ON tpcds.products USING btree (id) LOCAL(PARTITION pct
1 TABLESPACE example1, PARTITION pct2 TABLESPACE example2, PARTITION pct3 TABLESPACE example3) TABLESPACE pg_default
tpcds | products | index_3 | | CREATE INDEX index_3 ON tpcds.products USING btree (num) TABLESPACE pg_defa
ult
(3 rows)

omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages |
reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudesc
relid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit |
reloptions | relfrozenxid64
---------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-
----------+---------------+---------------+---------------+------------+-------------+---------------+-------------+----------
------+--------------+--------------+----------+---------+--------------------+----------+------------+---------+-------------
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 0 | | 1 | | | | | {orientation
--------------------------------------+----------------
customer_address_p1 | r | 16414 | 0 | 0 | r | 0 | 0 | 0 |
p1 | p | 16414 | 0 | 0 | r | 16418 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
=row,compression=no,wait_clean_gpi=n} | 0
0 | 0 | 9351 | | | | | {3000} | | {orientation
=row,compression=no} | 9351
p2 | p | 16414 | 0 | 0 | r | 16419 | 16410 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9351 | | | | | {5000} | | {orientation
=row,compression=no} | 9351
p3 | p | 16414 | 0 | 0 | r | 16420 | 16411 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9351 | | | | | {NULL} | | {orientation
=row,compression=no} | 9351
products | r | 16436 | 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 | 16436 | 0 | 0 | r | 16440 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9368 | | | | | {100} | | {orientation
=row,compression=no} | 9368
p2 | p | 16436 | 0 | 0 | r | 16441 | 16410 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9368 | | | | | {200} | | {orientation
=row,compression=no} | 9368
p3 | p | 16436 | 0 | 0 | r | 16442 | 16411 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9368 | | | | | {NULL} | | {orientation
=row,compression=no} | 9368
p3_id_idx | x | 16443 | 0 | 0 | n | 16446 | 0 | 1 |
0 | 0 | 0 | 0 | 16442 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
pct2 | x | 16447 | 0 | 0 | n | 16449 | 16411 | 1 |
0 | 0 | 0 | 0 | 16441 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
pct3 | x | 16447 | 0 | 0 | n | 16450 | 16412 | 1 |
0 | 0 | 0 | 0 | 16442 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
p2_id_idx0 | x | 16443 | 0 | 0 | n | 16445 | 0 | 1 |
0 | 0 | 0 | 0 | 16441 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
p1_id_idx | x | 16443 | 0 | 0 | n | 16454 | 16410 | 1 |
0 | 0 | 0 | 0 | 16440 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
pct1 | x | 16447 | 0 | 0 | n | 16455 | 16410 | 1 |
0 | 0 | 0 | 0 | 16440 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
(14 rows)

5.删除索引、表和表空间

omm=# drop index tpcds.index_1;
DROP INDEX
omm=# drop index tpcds.index_2;
DROP INDEX
omm=# drop index tpcds.index_3;
DROP INDEX
omm=# drop table tpcds.products;
DROP TABLE

omm=# drop table tpcds.customer_address_p1;
DROP TABLE
omm=# drop tablespace example1;
DROP TABLESPACE
omm=# drop tablespace example2;
DROP TABLESPACE
omm=# drop tablespace example3;
DROP TABLESPACE
omm=# drop tablespace example4;
DROP TABLESPACE