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

-- 1.
create table products(id integer)
partition by range(id)(
	partition store_p0 values less than(50),
	partition store_p1 values less than(100),
	partition store_p2 values less than(150)
);
-- 不指定索引分区的名称 会采用默认值 e.g. store_p0_id_idx
create index index1 on products(id) local;
create index index2 on products(id) local
(
	partition id_index1,
	partition id_index2,
	partition id_index3
);
-- 不指定关键字，默认创建的也是GLOBAL分区索引
-- NOTE: global 和 local 的分区索引不能创建在同一列上
alter table products add column id2 integer;
create index index3 on products(id2) global;
\d+ products
select * from pg_indexes where tablename = 'products';
select * from pg_partition;
-- 可以看到 pg_partition 里不仅有各分区 还有各分区索引

-- 2.
create tablespace example0 relative location 'tablespace/example0';
alter index index1 move partition store_p0_id_idx tablespace example0;
alter index index1 rename partition store_p1_id_idx to store_p1_id_idxxx;
\d+ products;

-- 3.
reindex index index2 partition id_index1;
reindex table products partition store_p0;

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

-- 5.
drop index index1;
drop index index2;
drop index index3;
drop tablespace example0;
drop table products;
复制