自己安装的openGauss环境
启动openGauss
gs_ctl -D /gauss/data/db1/ start
登录openGauss
gsql -d postgres -p 26000 -r
1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3
CREATE TABLESPACE example1 RELATIVE LOCATION ‘tablespace1/tablespace_1’;
CREATE TABLESPACE example2 RELATIVE LOCATION ‘tablespace2/tablespace_2’;
CREATE TABLESPACE example3 RELATIVE LOCATION ‘tablespace3/tablespace_3’;
CREATE TABLESPACE example4 RELATIVE LOCATION ‘tablespace4/tablespace_4’;
create schema hc;
create table hc.products
(product_id INTEGER,
product_name Char(30)
)
partition by range (product_id)
(partition p1 values less than (50),PARTITION p2 VALUES LESS THAN (100) TABLESPACE example1,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE example2);
create index products_idx1 on hc.products(product_id) local;
CREATE INDEX products_idx2 ON
hc.products(product_id) LOCAL
(
PARTITION product_id_index1,
PARTITION product_id_index2 TABLESPACE example1,
PARTITION product_id_index3 TABLESPACE example2
);
create index products_idx3 on hc.products(product_name) GLOBAL;
2.在分区表索引2上，修改分区表索引的表空间，重命名分区表索引
ALTER INDEX hc.products_idx2 MOVE PARTITION
product_id_index1 TABLESPACE example1;
ALTER INDEX hc.products_idx2 RENAME PARTITION
product_id_index1 TO product_id_index4;
3.在分区表索引2上，重建单个索引分区和分区上的所有索引
reindex index hc.products_idx2 PARTITION product_id_index4;
reindex table hc.products PARTITION p1;
4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
\d+ hc.products;
select * from pg_indexes where tablename = ‘products’;
select * from pg_partition;
5.删除索引、表和表空间
drop index hc.products_idx1;
drop index hc.products_idx2;
drop index hc.products_idx3;
drop table hc.products;
drop schema hc;
drop tablespace example1;
drop tablespace example2;
drop tablespace example3;
drop tablespace example4;
