openGauss每日一练第10天 | 学习心得体会 | Create by 朱志浇

课程作业

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

CREATE TABLE products
(
CA_ADDRESS_SK INTEGER NOT NULL,
CA_ADDRESS_ID CHAR(16) NOT NULL,
CA_STREET_NUMBER CHAR(10) ,
CA_STREET_NAME VARCHAR(60) ,
CA_STREET_TYPE CHAR(15) ,
CA_SUITE_NUMBER CHAR(10) ,
CA_CITY VARCHAR(60) ,
CA_COUNTY VARCHAR(30) ,
CA_STATE CHAR(2) ,
CA_ZIP CHAR(10) ,
CA_COUNTRY VARCHAR(20) ,
CA_GMT_OFFSET DECIMAL(5,2) ,
CA_LOCATION_TYPE CHAR(20)
)
PARTITION BY RANGE(CA_ADDRESS_SK)
(
PARTITION p1 VALUES LESS THAN (3000),
PARTITION p2 VALUES LESS THAN (5000),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

CREATE INDEX ds_products_p1_index1 ON products(CA_ADDRESS_SK) LOCAL;

CREATE INDEX ds_products_p1_index2 ON
products(CA_ADDRESS_SK) LOCAL
(
PARTITION CA_ADDRESS_SK_index1,
PARTITION CA_ADDRESS_SK_index2,
PARTITION CA_ADDRESS_SK_index3
);

CREATE INDEX ds_products_p1_index3 ON products(CA_ADDRESS_ID) GLOBAL;
复制

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

CREATE TABLESPACE tscp1 RELATIVE LOCATION 'tscp1/tscp1_1';
\db
\d+ products 
ALTER INDEX ds_products_p1_index1 MOVE PARTITION p1_ca_address_sk_idx TABLESPACE tscp1;
\d+ products
ALTER INDEX ds_products_p1_index1 RENAME PARTITION p1_ca_address_sk_idx TO p4_ca_address_sk_idx;
\d+ products
复制

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

reindex index ds_products_p1_index2 PARTITION CA_ADDRESS_SK_index1;
reindex table products PARTITION p1;
复制

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

\d+ products;
select * from pg_indexes where tablename = 'products';
select * from pg_partition;
复制

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

drop index ds_products_p1_index1;
drop index ds_products_p1_index2;
drop index ds_products_p1_index3;
drop table products;
drop tablespace tscp1;
复制

学习心得

通过本次练习，掌握了openGauss分区表索引的创建和基本使用方法。