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

学习目标

学习openGauss分区表索引

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

–创建表空间

CREATE TABLESPACE tbs_data1 RELATIVE LOCATION 'tablespace1/tbs_data1';
CREATE TABLESPACE tbs_data2 RELATIVE LOCATION 'tablespace2/tbs_data2';
CREATE TABLESPACE tbs_data3 RELATIVE LOCATION 'tablespace3/tbs_data3';
CREATE TABLESPACE tbs_data4 RELATIVE LOCATION 'tablespace3/tbs_data4';

–创建范围分区表products

create schema lp;
CREATE TABLE lp.products
(
 product_id             integer,   
 product_name           char(20),    
 category_id               char(10)   
)
PARTITION BY RANGE(product_id)
(
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (200) TABLESPACE tbs_data1,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE tbs_data2
);

–为表创建分区表索引1，不指定索引分区的名称

CREATE INDEX lp.products_index1 ON lp.products(product_id) LOCAL;

–创建分区表索引2，并指定索引分区的名称

CREATE INDEX lp.products_index2 ON
lp.products(product_name) LOCAL
(
PARTITION product_p_index1,
PARTITION product_p_index2 TABLESPACE tbs_data3,
PARTITION product_p_index3 TABLESPACE tbs_data4
);

–创建GLOBAL分区索引3

CREATE INDEX lp.products_index3 ON lp.products(category_id) GLOBAL;
CREATE INDEX lp.products_index3 ON lp.products(category_id) ;

–查看表

\d+ lp.products;

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

alter index lp.products_index1 set tablespace tbs_data3;

注：不能直接修改索引表空间，只能move分区索引

ALTER INDEX lp.products_index1 MOVE PARTITION p1_product_id_idx TABLESPACE tbs_data3;

ALTER INDEX lp.products_index1 RENAME PARTITION p1_product_id_idx TO p1_product_id_idx100;

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

reindex index lp.products_index2 partition product_p_index1;

reindex table lp.products partition p1;

注：rename to后面不用跟schema

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

\d+ lp.products;
select * from pg_indexes where schemaname='lp' and tablename = 'products';
select * from pg_partition;

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

drop index lp.products_index1;
drop index lp.products_index2;
drop index lp.products_index3;
drop table lp.products;
drop tablespace tbs_data1;
drop tablespace tbs_data2;
drop tablespace tbs_data3;
drop tablespace tbs_data4;
drop schema lp;