openGauss每日一练第10天|学习心得

今天学习openGauss分区表索引，以下是作业和心得:
1. 连接openGauss

su - omm
gsql -r

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

create tablespace test1 relative location 'tablespace/test1';
create schema s1;
create table s1.products (
id integer not null,
name char(40),
price float4,
category char(30),
pnum integer,
description char(50)
)
partition by range(id)
(
partition p1 values less than (100),
partition p2 values less than (200),
partition p3 values less than (300)
);
create index products_index1 on s1.products(id) local;
create index products_index2 on s1.products(id) local
(
partition id_ind_index1,
partition id_ind_index2,
partition id_ind_index3
);
create index products_index3 on s1.products(name) global;
\

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

\d+ s1.products;
alter index s1.products_index1 move partition p1_id_idx tablespace test1;
alter index s1.products_index1 rename  to products_index1_new;
\

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

reindex index s1.products_index2 partition id_ind_index1;
reindex table s1.products partition  p1;
\

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

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

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

drop index s1.products_index1_new;
drop index s1.products_index2;
drop index s1.products_index3;
drop table s1.products;
drop tablespace test1;
\

通过学习和作业，巩固了openGauss分区表索引的知识，索引创建成功后，系统会自动判断何时引用索引。当系统认为使用索引比顺序扫描更快时，就会使用索引。分区表索引分为LOCAL索引与GLOBAL索引，一个LOCAL索引对应一个具体分区，而GLOBAL索引则对应整个分区表。