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

课后作业

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

-创建范围分区表products

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 tpcds;
CREATE TABLE tpcds.products
(
id INTEGER NOT NULL,
name CHAR(16) NOT NULL
)
PARTITION BY RANGE(CA_ADDRESS_SK)
(
PARTITION p1 VALUES LESS THAN (3000),
PARTITION p2 VALUES LESS THAN (5000) TABLESPACE example1,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE example2
);

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

CREATE INDEX index_1 ON tpcds.products(id) LOCAL;

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

CREATE INDEX index_2 ON
tpcds.products(id) LOCAL
(
PARTITION index1,
PARTITION index2 TABLESPACE example3,
PARTITION index3 TABLESPACE example4
);

-创建GLOBAL分区索引3

CREATE INDEX index_3 ON tpcds.products(id) GLOBAL;

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

ALTER INDEX tpcds.index_1 MOVE PARTITION
index2 TABLESPACE example1;

-重命名分区表索引

ALTER INDEX tpcds.index_1 RENAME PARTITION
index1 TO index4;

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

reindex index tpcds.index_2 PARTITION p1_ca_address_sk_idx;

reindex table tpcds.products PARTITION p1;

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

\d+ tpcds.products;
select * from pg_indexes where tablename = 'products';
select * from pg_partition;

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

DROP INDEX tpcds.index_2;
DROP INDEX tpcds.index_3;
DROP INDEX tpcds.index_4;
drop schema tpcds cascade;
drop tablespace example1;
drop tablespace example2;
drop tablespace example3;
drop tablespace example4;