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
(
pro_id INTEGER NOT NULL,
pro_name CHAR(30),
pro_code CHAR(10),
pro_loc CHAR(20)
)
PARTITION BY RANGE(pro_id)
(
PARTITION p1 VALUES LESS THAN (25000),
PARTITION p2 VALUES LESS THAN (50000) TABLESPACE example1,
PARTITION p5 VALUES LESS THAN (MAXVALUE) TABLESPACE example2
);

--为表创建分区表索引1，不指定索引分区的名称
CREATE INDEX ds_products_index1 ON tpcds.products(pro_id) LOCAL;

--创建分区表索引2，并指定索引分区的名称
CREATE INDEX ds_products_index2 ON
tpcds.products(pro_id) LOCAL
(
PARTITION pro_id_index1,
PARTITION pro_id_index2 TABLESPACE example3,
PARTITION pro_id_index3 TABLESPACE example4
);

--创建GLOBAL分区索引3
CREATE INDEX ds_products_index3 ON tpcds.products(pro_code) GLOBAL;

–查看索引信息
\d+ tpcds.products;
SELECT * FROM pg_indexes WHERE tablename = 'products';
SELECT * FROM pg_partition;

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

SELECT relname, reltablespace FROM pg_partition;
ALTER INDEX tpcds.ds_products_index2 MOVE PARTITION pro_id_index2 TABLESPACE example4;
ALTER INDEX tpcds.ds_products_index1 MOVE PARTITION p5_pro_id_idx TABLESPACE example4;
SELECT relname, reltablespace FROM pg_partition;

--重命名分区表索引
ALTER INDEX tpcds.ds_products_index1 RENAME PARTITION p5_pro_id_idx TO pro_id_index5;

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

–重建单个索引分区
reindex index tpcds.ds_products_index2 PARTITION pro_id_index3;
–重建分区上的所有索引
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.ds_products_index1;
DROP INDEX tpcds.ds_products_index2;
DROP INDEX tpcds.ds_products_index3;

DROP TABLE tpcds.products;
DROP SCHEMA tpcds;

DROP TABLESPACE example1;
DROP TABLESPACE example2;
DROP TABLESPACE example3;
DROP TABLESPACE example4;