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

学习openGauss分区表索引：openGauss分区表支持两种索引：全局（global）索引和本地（local）索引。分区表创建索引不支持concurrently语法，默认索引是全局索引，创建本地索引需要指定local。创建主键约束和唯一约束必须要包含分区字段，创建本地唯一索引也必须要包含分区字段，但是创建全局唯一索引没有这个限制

课程作业打卡：

1、创建范围分区表products

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

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

CREATE TABLESPACE tbs1 RELATIVE LOCATION 'tablespace1/tablespace_1';

CREATE TABLESPACE tbs2 RELATIVE LOCATION 'tablespace2/tablespace_2';

CREATE TABLESPACE tbs3 RELATIVE LOCATION 'tablespace3/tablespace_3';

CREATE TABLESPACE tbs4 RELATIVE LOCATION 'tablespace4/tablespace_4';

CREATE TABLESPACE tbs5 RELATIVE LOCATION 'tablespace4/tablespace_5';

CREATE TABLESPACE tbs6 RELATIVE LOCATION 'tablespace4/tablespace_6';

CREATE TABLESPACE tbs7 RELATIVE LOCATION 'tablespace4/tablespace_7';

CREATE TABLESPACE tbs8 RELATIVE LOCATION 'tablespace4/tablespace_8';

create schema pysql;

CREATE TABLE pysql.products

( product_id             integer,      

  product_name           char(20),    

  category               char(30)

)

PARTITION BY RANGE(product_id)

(

PARTITION p1 VALUES LESS THAN (2000) TABLESPACE tbs1,

PARTITION p2 VALUES LESS THAN (4000) TABLESPACE tbs2,

PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE tbs3

);

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

create index products_idx1 on pysql.products(product_id) local;

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

create index products_idx2 on pysql.products(product_id) local

(

partition id_index1 tablespace tbs4,

partition id_index2 tablespace tbs5,

partition id_index3 tablespace tbs6

);

#创建GLOBAL分区索引3

 CREATE INDEX products_idx3 ON pysql.products(product_id) GLOBAL;

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

 ALTER INDEX pysql.products_idx1 MOVE PARTITION id_index1 TABLESPACE tbs7;

ALTER INDEX pysql.products_idx1 RENAME PARTITION id_index1 TO id_index7;

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

 –重建单个索引分区

reindex index pysql.products_idx2 PARTITION id_index2;

–重建分区上的所有索引

reindex table pysql.products PARTITION p2;

################

实训环境学习:

创建分区表索引

 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);

–查看索引信息

\d+ tpcds.customer_address_p1;

select * from pg_indexes where tablename = 'customer_address_p1';

select * from pg_partition;

1.2 修改分区表索引定义

–修改分区表索引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;

1.3 重建索引分区

–重建单个索引分区

reindex index tpcds.ds_customer_address_p1_index1 PARTITION p1_ca_address_sk_idx;

–重建分区上的所有索引

reindex table tpcds.customer_address_p1 PARTITION p1;

1.4 删除索引

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;