学习openGauss分区表索引
课后作业打卡

1.创建范围分区表products

为表创建分区表索引1，不指定索引分区的名称;
创建分区表索引2，并指定索引分区的名称;
创建GLOBAL分区索引3;

CREATE TABLESPACE tbs01 RELATIVE LOCATION 'tablespace1/tablespace_11';
CREATE TABLESPACE tbs02 RELATIVE LOCATION 'tablespace2/tablespace_12';
CREATE TABLESPACE tbs03 RELATIVE LOCATION 'tablespace3/tablespace_13';
CREATE TABLESPACE tbs04 RELATIVE LOCATION 'tablespace4/tablespace_14';

create schema mydb;
CREATE TABLE mydb.products
(
products_name INTEGER NOT NULL,
products_ID CHAR(16) NOT NULL,
products_price int ,
products_sales VARCHAR(60)
)
PARTITION BY RANGE(products_price)
(
PARTITION p1 VALUES LESS THAN (3000),
PARTITION p2 VALUES LESS THAN (5000) TABLESPACE tbs01,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE tbs02
);

CREATE INDEX idx_p1_index1 ON mydb.products(products_price) LOCAL;
CREATE INDEX idx_p2_index2 ON mydb.products(products_price) LOCAL
(
PARTITION price_index1,
PARTITION price_index2 TABLESPACE example3,
PARTITION price_index3 TABLESPACE example4
);

CREATE INDEX idx_p3_index3 ON mydb.products(products_id) GLOBAL;

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

ALTER INDEX mydb.idx_p2_index2 MOVE PARTITION price_index1 TABLESPACE tbs01;
--重命名
ALTER INDEX mydb.idx_p2_index2 RENAME PARTITION price_index1 TO price_index5;

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

reindex table mydb.products PARTITION p1;
reindex table mydb.products;

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

\d+ mydb.products
select * from pg_indexes where tablename=‘products’;
select relname,partkey,boundaries from pg_partition;

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

DROP INDEX mydb.idx_p1_index1;
DROP INDEX mydb.idx_p2_index2;
DROP INDEX mydb.idx_p3_index3;

drop table mydb.products
DROP TABLESPACE IF EXISTS tbs01;
DROP TABLESPACE IF EXISTS tbs02;
DROP TABLESPACE IF EXISTS tbs03;
DROP TABLESPACE IF EXISTS tbs04;