openGauss每日一练第10天 openGauss分区表索引
1.创建分区表索引
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;
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;
3.重建索引分区
–重建单个索引分区
reindex index tpcds.ds_customer_address_p1_index1 PARTITION p1_ca_address_sk_idx;
–重建分区上的所有索引
reindex table tpcds.customer_address_p1 PARTITION p1;
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;
课程作业
1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3
omm=# create table products
omm-# (
omm(# product_sk integer not null,
omm(# product_id char(30) not null,
omm(# product_name char(30)
omm(# )
omm-# partition by range(product_sk)
omm-# (
omm(# partition p1 values less than (1000),
omm(# partition p2 values less than (2000),
omm(# partition p3 values less than (MAXVALUE)
omm(# );
CREATE TABLE
omm=# \d+ products;
Table “public.products”
Column | Type | Modifiers | Storage | Stats target | Description
--------------±--------------±----------±---------±-------------±------------
product_sk | integer | not null | plain | |
product_id | character(30) | not null | extended | |
product_name | character(30) | | extended | |
Range partition by(product_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
omm=# create index products_sk_index1 on products(product_sk) local;
CREATE INDEX
omm=# create index products_sk_index2 on products(product_sk) local
omm-# (
omm(# partition products_sk_index2_p1,
omm(# partition products_sk_index2_p2,
omm(# partition products_sk_index2_p3
omm(# );
CREATE INDEX
omm=# create index products_sk_index3 on products(product_id) global;
CREATE INDEX
omm=# \d+ products;
Table “public.products”
Column | Type | Modifiers | Storage | Stats target | Description
--------------±--------------±----------±---------±-------------±------------
product_sk | integer | not null | plain | |
product_id | character(30) | not null | extended | |
product_name | character(30) | | extended | |
Indexes:
“products_sk_index1” btree (product_sk) LOCAL(PARTITION p1_product_sk_idx, PARTITION p2_product_sk_idx, PARTITION p3_product_sk_idx) TABLESPACE pg_default
“products_sk_index2” btree (product_sk) LOCAL(PARTITION products_sk_index2_p1, PARTITION products_sk_index2_p2, PARTITION products_sk_index2_p3) TABLESPACE pg_default
“products_sk_index3” btree (product_id) TABLESPACE pg_default
Range partition by(product_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
omm=#
2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引
omm=# create tablespace ts1 relative location ‘tablespace/ts1’;
CREATE TABLESPACE
omm=# alter index products_sk_index1 move partition p1_product_sk_idx tablespace ts1;
ALTER INDEX
omm=# alter index products_sk_index1 rename partition p1_product_sk_idx to p1_product_sk_idx_new;
ALTER INDEX
omm=# \d+ products;
Table “public.products”
Column | Type | Modifiers | Storage | Stats target | Description
--------------±--------------±----------±---------±-------------±------------
product_sk | integer | not null | plain | |
product_id | character(30) | not null | extended | |
product_name | character(30) | | extended | |
Indexes:
“products_sk_index1” btree (product_sk) LOCAL(PARTITION p1_product_sk_idx_new TABLESPACE ts1, PARTITION p2_product_sk_idx, PARTITION p3_product_sk_idx) TABLESPACE pg_default
“products_sk_index2” btree (product_sk) LOCAL(PARTITION products_sk_index2_p1, PARTITION products_sk_index2_p2, PARTITION products_sk_index2_p3) TABLESPACE pg_default
“products_sk_index3” btree (product_id) TABLESPACE pg_default
Range partition by(product_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
3.在分区表索引2上，重建单个索引分区和分区上的所有索引
omm=# reindex index products_sk_index2 partition products_sk_index2_p1;
REINDEX
omm=# reindex table products partition p1;
REINDEX
4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
omm=# \d+ products;
Table “public.products”
Column | Type | Modifiers | Storage | Stats target | Description
--------------±--------------±----------±---------±-------------±------------
product_sk | integer | not null | plain | |
product_id | character(30) | not null | extended | |
product_name | character(30) | | extended | |
Indexes:
“products_sk_index1” btree (product_sk) LOCAL(PARTITION p1_product_sk_idx_new TABLESPACE ts1, PARTITION p2_product_sk_idx, PARTITION p3_product_sk_idx) TABLESPACE pg_default
“products_sk_index2” btree (product_sk) LOCAL(PARTITION products_sk_index2_p1, PARTITION products_sk_index2_p2, PARTITION products_sk_index2_p3) TABLESPACE pg_default
“products_sk_index3” btree (product_id) TABLESPACE pg_default
Range partition by(product_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
omm=# select * from pg_indexes where tablename=‘products’;
schemaname | tablename | indexname | tablespace | indexdef
------------±----------±-------------------±-----------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
public | products | products_sk_index1 | | CREATE INDEX products_sk_index1 ON products USING btree (product_sk) LOCAL(PARTITION p1_product_sk_idx_new TABLESPACE ts1, PARTITION p2_product_sk_idx, PARTITION p3_product_sk_idx) TABLESPACE pg_default
public | products | products_sk_index2 | | CREATE INDEX products_sk_index2 ON products USING btree (product_sk) LOCAL(PARTITION products_sk_index2_p1, PARTITION products_sk_index2_p2, PARTITION products_sk_index2_p3) TABLESPACE pg_default
public | products | products_sk_index3 | | CREATE INDEX products_sk_index3 ON products USING btree (product_id) TABLESPACE pg_default
(3 rows)
omm=# select relname, parttype, parentid, reltablespace from pg_partition;
relname | parttype | parentid | reltablespace
-----------------------±---------±---------±--------------
customer_address_p1 | r | 16394 | 0
p1 | p | 16394 | 0
p2 | p | 16394 | 16389
p3 | p | 16394 | 16390
products | r | 16416 | 0
p1 | p | 16416 | 0
p2 | p | 16416 | 0
p3 | p | 16416 | 0
p2_product_sk_idx | x | 16423 | 0
p3_product_sk_idx | x | 16423 | 0
products_sk_index2_p2 | x | 16427 | 0
products_sk_index2_p3 | x | 16427 | 0
p1_product_sk_idx_new | x | 16423 | 16432
products_sk_index2_p1 | x | 16427 | 0
(14 rows)
5.删除索引、表和表空间
omm=# drop index products_sk_index1,products_sk_index2,products_sk_index3;
DROP INDEX
omm=# drop table products;
DROP TABLE
omm=# drop tablespace ts1;
DROP TABLESPACE
