《openGauss每日一练第10天|索引操作--分区表》

一、学习目标

在普通表索引的基础上学习openGauss分区表的索引

二、索引（INDEX）

1. 简介
   索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息

三、openGauss基础操作

• openGauss连接

su - omm
gsql -r
复制

• 查看openGauss表空间信息

// 显示表信息（包含索引）
\d
// 显示表更多信息
\d+
// 显示指定表更多信息
\d+ table_name
复制

四、openGauss普通表的索引操作

• 创建分区表索引

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.customer_address_p1
(
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) TABLESPACE example1,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE example2
);

// 创建分区表索引ds_customer_address_p1_index1，不指定索引分区的名称
CREATE INDEX ds_customer_address_p1_index1 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL;

// 创建分区表索引ds_customer_address_p1_index2，并指定索引分区的名称
CREATE INDEX ds_customer_address_p1_index2 ON
tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL
(
PARTITION CA_ADDRESS_SK_index1,
PARTITION CA_ADDRESS_SK_index2 TABLESPACE example3,
PARTITION CA_ADDRESS_SK_index3 TABLESPACE example4
);

// 创建GLOBAL分区索引
CREATE INDEX ds_customer_address_p1_index3 ON tpcds.customer_address_p1(CA_ADDRESS_ID) GLOBAL;

// 不指定关键字，默认创建GLOBAL分区索引
CREATE INDEX ds_customer_address_p1_index4 ON tpcds.customer_address_p1(CA_ADDRESS_ID);

复制

• 修改分区表索引

// 修改分区表索引CA_ADDRESS_SK_index2的表空间为example1
ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION
CA_ADDRESS_SK_index2 TABLESPACE example1;

// 修改分区表索引CA_ADDRESS_SK_index3的表空间为example2
ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION
CA_ADDRESS_SK_index3 TABLESPACE example2;

// 重命名索引
ALTER INDEX tpcds.ds_customer_address_p1_index2 RENAME PARTITION
CA_ADDRESS_SK_index1 TO CA_ADDRESS_SK_index4;
复制

• 重建索引

// 重建一个单独索引
reindex index tpcds.ds_customer_address_p1_index1 PARTITION p1_ca_address_sk_idx;


// 重建所有索引
reindex table tpcds.customer_address_p1 PARTITION p1;

复制

• 删除索引
  DROP INDEX index_name

DROP INDEX tpcds.ds_customer_address_p1_index1;
DROP INDEX tpcds.ds_customer_address_p1_index2;
DROP INDEX tpcds.ds_customer_address_p1_index3;
DROP INDEX tpcds.ds_customer_address_p1_index4;
复制

五、课后作业

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

// 创建表空间
create tablespace test1 relative location 'tablespace/tablespace_1';
create tablespace test2 relative location 'tablespace/tablespace_2';

// 创建模式
create schema tpcds;

// 创建范围分区表
create table tpcds.products
(
sum int,
name char(30)
)
partition by range(sum)
(
partition p1 values less than (100),
partition p2 values less than (200),
partition p3 vlaues less than (300)
);

// 创建分区表索引1，不指定索引分区的名称
CREATE INDEX index1 ON tpcds.products(sum) LOCAL;

// 创建分区表索引2，并指定索引分区的名称
CREATE INDEX index2 ON
tpcds.products(sum) LOCAL
(
PARTITION sum_index1 tablespace test1,
PARTITION sum_index2,
PARTITION sum_index3
);

// 创建GLOBAL分区索引3
CREATE INDEX index3 ON tpcds.products(sum) GLOBAL;
复制

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

// 修改表空间
ALTER INDEX tpcds.index1 MOVE PARTITION
sum_index1 TABLESPACE test2;

// 重命名
ALTER INDEX tpcds.index1 RENAME PARTITION
sum_index1 TO sum_index11;
复制

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

// 重建单个索引
reindex index tpcds.index2 PARTITION p1_ca_address_sk_idx;

// 重建所有索引
reindex table tpcds.products PARTITION p1;
复制

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

\d+ tpcds.products
select * from pg_indexes where tablename = 'products';
reindex table tpcds.customer_address_p1 PARTITION p1;

复制

• 删除索引、表和表空间

DROP INDEX tcpds.index_11;
DROP INDEX tcpds.index_2;
DROP INDEX tcpds.index_3;
DROP table if exists tcpds.products;
drop tablespace test1;
drop tablespace test2;
复制