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

分区索引(或索引分区)主要是针对分区表而言的。随着数据量的不断增长，普通的堆表需要转换到分区表，其索引呢，则对应的转换到分区索引。分区索引的好处是显而易见的。就是简单地把一个索引分成多个片断，在获取所需数据时，只需要访问更小的索引片断(块)即可实现。同时把分区放在不同的表空间可以提高分区的可用性和可靠性。本文主要描述了分区索引的相关特性并给出演示示例。

课程作业：

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

–创建表空间

CREATE TABLESPACE tbs_01 RELATIVE LOCATION 'tablespace1/tbs_01';
CREATE TABLESPACE tbs_02 RELATIVE LOCATION 'tablespace2/tbs_02';
CREATE TABLESPACE tbs_03 RELATIVE LOCATION 'tablespace3/tbs_03';
CREATE TABLESPACE tbs_04 RELATIVE LOCATION 'tablespace3/tbs_04';

–创建范围分区表products

create schema sym;
CREATE TABLE sym.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_01,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE tbs_02
);

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

CREATE INDEX sym.products_index1 ON sym.products(category_id) LOCAL;

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

CREATE INDEX sym.products_index2 ON
sym.products(product_name) LOCAL
(
PARTITION product_p_index1,
PARTITION product_p_index2 TABLESPACE tbs_03,
PARTITION product_p_index3 TABLESPACE tbs_04
);

–创建GLOBAL分区索引3

CREATE INDEX sym.products_index3 ON sym.products(product_id) GLOBAL;
CREATE INDEX sym.products_index3 ON sym.products(product_id) ;  --不带global声明

–查看表

\d+ sym.products;

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

ALTER INDEX sym.products_index1 MOVE PARTITION p1_category_id_idx TABLESPACE tbs_03;
ALTER INDEX sym.products_index1 RENAME PARTITION p1_category_id_idx TO p1_category_id_idx1;

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

REINDEX INDEX sym.products_index2 partition product_p_index1;
REINDEX TABLE sym.products partition p1;

注意：rename to后面不能带schema

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

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

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

drop index sym.products_index1;
drop index sym.products_index2;
drop index sym.products_index3;
drop table sym.products;
drop tablespace tbs_01;
drop tablespace tbs_02;
drop tablespace tbs_03;
drop tablespace tbs_04;