openGauss每日一练第10天学习_分区表索引

本课主要学习openGauss分区表索引,这节有点绕,主要是分区表索引概念,以及分区表索引上基础再指定索引分区有点难以理解,需要多实践几次

课后作业

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 tpcds;
CREATE TABLE tpcds.products
(
p_id INTEGER NOT NULL,
p_code CHAR(16) NOT NULL,
p_name CHAR(50)
)
PARTITION BY RANGE(p_id)
(
PARTITION p1 VALUES LESS THAN (3000),
PARTITION p2 VALUES LESS THAN (5000) TABLESPACE example1,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE example2
);
create index products_idx1 on tpcds.products(p_id) local;
create index products_idx2 on tpcds.products(p_id) local
(
  partition p_id_idx1,
  partition p_id_idx2 TABLESPACE example2,
  partition p_id_idx3 tablespace example3
);
create index products_idx4 on tpcds.products(p_code) global;

特点要注意的是 创建分区表索引1，不指定索引分区的名称后, 系统会自动创建三个默认的索引分区名称

p1_p_id_idx, p2_p_id_idx, p3_p_id_idx

查看创建表和索引:

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

alter index products_idx1 move partition  p1_p_id_idx tablespace example3;
alter index tpcds.products_idx1 rename partition p1_p_id_idx to p1_p_id_idx4;

查看修改结果

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

reindex index tpcds.products_idx2 partition p_id_idx1;
reindex table tpcds.products PARTITION p2;

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

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

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

DROP INDEX tpcds.products_idx1;
DROP INDEX tpcds.products_idx2;
DROP INDEX tpcds.products_idx4;
drop schema tpcds cascade;
drop tablespace example1;
drop tablespace example2;
drop tablespace example3;
drop tablespace example4;