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

##第十天课后作业
1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3

 create table products(id int,productname char(30)) partition by range(id) 
 ( partition p1 values less than(1000),partition p2 values less than (2000),
partition p3 values less than(maxvalue));

create index products_p1_index on products(id) local;

create index products_p1_index2 on products(id) local
(partition  products_id_index1,partition produicts_id_index2,partition products_id_index3);

create index products_p1_index3 on products(productname);

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

alter index products_p1_index2  move partition products_id_index1 tablespace example1;
alter index products_p1_index2 rename partition products_id_index1 to index2_p1;

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

reindex index products_p1_index2  partition index2_p1;
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_p1_index;
drop index products_p1_index2;
drop index products_p1_index3;
drop tablespace if exists example1;