openGauss每日一练第10天

学习内容：学习openGauss分区表索引

课后作业：

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

create table products (id int,name char(10)) PARTITION BY RANGE(id)(partition p1 values less than(10),partition p2 values less than(20),partition p3 values less than(30));
create index index1 on products(id) local; 
create index index2 on products(id) local (partition id_index1,partition id_index2,partition id_index3);create index index3 on products(name) global; 

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

CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace1;
ALTER INDEX index1 MOVE PARTITION TABLESPACE example1;ALTER INDEX index1  RENAME TO index11;

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

reindex index index2 PARTITION in_index1;
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 index1;
drop index index2;
drop index index3;
drop table products;
drop tablespace example1;