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

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

create table products(
product_id INTEGER,
product_name char(20),
category char(10)
)
partition by range (product_id)
(
partition p0 values less than (100),
partition p1 values less than (200),
partition p2 values less than (300),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

CREATE INDEX products_index1 ON products(product_id) LOCAL;
CREATE INDEX products_index2 ON
products(product_id) LOCAL
(
PARTITION product_id_index1,
PARTITION product_id_index2,
PARTITION product_id_index3,
PARTITION product_id_index4
);
CREATE INDEX products_index3 ON products(product_name) GLOBAL;

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

CREATE TABLESPACE products RELATIVE LOCATION 'tablespace0/tablespace_0';
\d+ products;
select * from pg_indexes where tablename = 'products';
select * from pg_partition;
ALTER INDEX products_index2 MOVE PARTITION product_id_index1 TABLESPACE products;
ALTER INDEX products_index2 RENAME PARTITION product_id_index1 TO product_id_index100;

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

reindex index products_index2 PARTITION product_id_index2;
reindex table products PARTITION p1;

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

\d+ products
select * from pg_indexes where tablename = 'products';
select * from pg_partition;

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

drop index products_index1;
drop index products_index2;
drop index products_index3;
drop table products;
drop tablespace products;