学习目标
学习openGauss分区表索引
课程学习
连接openGauss
root@modb:~# su - omm
omm@modb:~$ gsql -r
1.创建分区表索引
omm=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1';
CREATE TABLESPACE
omm=# CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2';
CREATE TABLESPACE
omm=# CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3';
CREATE TABLESPACE
omm=# CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';
CREATE TABLESPACE
omm=#
omm=# create schema tpcds;
CREATE SCHEMA
omm=#
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，不指定索引分区的名称
omm=# CREATE INDEX ds_customer_address_p1_index1 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL;
CREATE INDEX
omm=#
--创建分区表索引 ds_customer_address_p1_index2，并指定索引分区的名称。
omm=# 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
);
CREATE INDEX
omm=#
--索引分区顺序及个数 对应 表分区
omm=# CREATE INDEX ds_customer_address_p1_index5 ON
omm-# tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL
omm-# (
omm(# PARTITION CA_ADDRESS_SK_index1,
omm(# PARTITION CA_ADDRESS_SK_index2 TABLESPACE example3
omm(# );
ERROR: Not enough index partition defined
omm=#
--创建GLOBAL分区索引
omm=# CREATE INDEX ds_customer_address_p1_index3 ON tpcds.customer_address_p1(CA_ADDRESS_ID) GLOBAL;
CREATE INDEX
omm=#
--不指定关键字，默认创建 GLOBAL 分区索引
omm=# CREATE INDEX ds_customer_address_p1_index4 ON tpcds.customer_address_p1(CA_ADDRESS_ID);
CREATE INDEX
omm=#
--查看索引信息
omm=# \d+ tpcds.customer_address_p1;
Table "tpcds.customer_address_p1"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+-----------------------+-----------+----------+--------------+-------------
ca_address_sk | integer | not null | plain | |
ca_address_id | character(16) | not null | extended | |
ca_street_number | character(10) | | extended | |
ca_street_name | character varying(60) | | extended | |
ca_street_type | character(15) | | extended | |
ca_suite_number | character(10) | | extended | |
ca_city | character varying(60) | | extended | |
ca_county | character varying(30) | | extended | |
ca_state | character(2) | | extended | |
ca_zip | character(10) | | extended | |
ca_country | character varying(20) | | extended | |
ca_gmt_offset | numeric(5,2) | | main | |
ca_location_type | character(20) | | extended | |
Indexes:
"ds_customer_address_p1_index1" btree (ca_address_sk) LOCAL(PARTITION p1_ca_address_sk_idx, PARTITION p2_ca_address_sk_idx, PARTITION p3_ca_address_sk_idx) TABLESPACE pg_default
"ds_customer_address_p1_index2" btree (ca_address_sk) LOCAL(PARTITION ca_address_sk_index1, PARTITION ca_address_sk_index2 TABLESPACE example3, PARTITION ca_address_sk_index3 TABLESPACE example4) TABLESPACE pg_default
"ds_customer_address_p1_index3" btree (ca_address_id) TABLESPACE pg_default
"ds_customer_address_p1_index4" btree (ca_address_id) TABLESPACE pg_default
Range partition by(ca_address_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 = 'customer_address_p1';
schemaname | tablename | indexname | tablespace | indexdef
------------+---------------------+-------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tpcds | customer_address_p1 | ds_customer_address_p1_index1 | | CREATE INDEX ds_customer_address_p1_index1 ON tpcds.customer_address_p1 USING btree (ca_address_sk) LOCAL(PARTITION p1_ca_address_sk_idx, PARTITION p2_ca_address_sk_idx, PARTITION p3_ca_address_sk_idx) TABLESPACE pg_default
tpcds | customer_address_p1 | ds_customer_address_p1_index3 | | CREATE INDEX ds_customer_address_p1_index3 ON tpcds.customer_address_p1 USING btree (ca_address_id) TABLESPACE pg_default
tpcds | customer_address_p1 | ds_customer_address_p1_index4 | | CREATE INDEX ds_customer_address_p1_index4 ON tpcds.customer_address_p1 USING btree (ca_address_id) TABLESPACE pg_default
tpcds | customer_address_p1 | ds_customer_address_p1_index2 | | CREATE INDEX ds_customer_address_p1_index2 ON tpcds.customer_address_p1 USING btree (ca_address_sk) LOCAL(PARTITION ca_address_sk_index1, PARTITION ca_address_sk_index2 TABLESPACE example3, PARTITION ca_address_sk_index3 TABLESPACE example4) TABLESPACE pg_default
(4 rows)
omm=#
mydb=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit | reloptions | relfrozenxid64
----------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+---------------+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+--------------------+----------+------------+---------+---------------------------------------------------+----------------
customer_address_p1 | r | 16390 | 0 | 0 | r | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 0 | | 1 | | | | | {orientation=row,compression=no,wait_clean_gpi=n} | 0
p1 | p | 16390 | 0 | 0 | r | 16394 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 14332 | | | | | {3000} | | {orientation=row,compression=no} | 14332
p2 | p | 16390 | 0 | 0 | r | 16395 | 16385 | 0 | 0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 14332 | | | | | {5000} | | {orientation=row,compression=no} | 14332
p3 | p | 16390 | 0 | 0 | r | 16396 | 16386 | 0 | 0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 14332 | | | | | {NULL} | | {orientation=row,compression=no} | 14332
p1_ca_address_sk_idx | x | 16397 | 0 | 0 | n | 16398 | 0 | 1 | 0 | 0 | 0 | 0 | 16394 | t | 0 | 0 | 0 | 0 | 0 | | | | | | | | 0
p2_ca_address_sk_idx | x | 16397 | 0 | 0 | n | 16399 | 0 | 1 | 0 | 0 | 0 | 0 | 16395 | t | 0 | 0 | 0 | 0 | 0 | | | | | | | | 0
p3_ca_address_sk_idx | x | 16397 | 0 | 0 | n | 16400 | 0 | 1 | 0 | 0 | 0 | 0 | 16396 | t | 0 | 0 | 0 | 0 | 0 | | | | | | | | 0
ca_address_sk_index1 | x | 16401 | 0 | 0 | n | 16402 | 0 | 1 | 0 | 0 | 0 | 0 | 16394 | t | 0 | 0 | 0 | 0 | 0 | | | | | | | | 0
ca_address_sk_index2 | x | 16401 | 0 | 0 | n | 16403 | 16387 | 1 | 0 | 0 | 0 | 0 | 16395 | t | 0 | 0 | 0 | 0 | 0 | | | | | | | | 0
ca_address_sk_index3 | x | 16401 | 0 | 0 | n | 16404 | 16388 | 1 | 0 | 0 | 0 | 0 | 16396 | t | 0 | 0 | 0 | 0 | 0 | | | | | | | | 0
(10 rows)
mydb=#
2.修改分区表索引定义
--–修改分区表索引 CA_ADDRESS_SK_index2 的表空间为example1
mydb=# ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION CA_ADDRESS_SK_index2 TABLESPACE example1;
ALTER INDEX
mydb=#
--–修改分区表索引 CA_ADDRESS_SK_index3 的表空间为example2
mydb=# ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION CA_ADDRESS_SK_index3 TABLESPACE example2;
ALTER INDEX
mydb=#
––重命名分区表索引
mydb=# ALTER INDEX tpcds.ds_customer_address_p1_index2 RENAME PARTITION CA_ADDRESS_SK_index1 TO CA_ADDRESS_SK_index4;
ALTER INDEX
mydb=#
3.重建索引分区
--–重建单个索引分区
mydb=# reindex index tpcds.ds_customer_address_p1_index1 PARTITION p1_ca_address_sk_idx;
REINDEX
mydb=#
--–重建分区上的所有索引
mydb=# reindex table tpcds.customer_address_p1 PARTITION p1;
REINDEX
mydb=#
4.删除索引
mydb=# DROP INDEX tpcds.ds_customer_address_p1_index1;
DROP INDEX
mydb=# DROP INDEX tpcds.ds_customer_address_p1_index2;
DROP INDEX
mydb=# DROP INDEX tpcds.ds_customer_address_p1_index3;
DROP INDEX
mydb=# DROP INDEX tpcds.ds_customer_address_p1_index4;
DROP INDEX
mydb=#
课后作业
1.创建范围分区表 products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建 GLOBAL 分区索引3
create table tpcds.products ( c1 int, c2 CHAR(2) )
partition by range (c1)
(
partition store_p0 values less than (50),
partition store_p1 values less than (100),
partition store_p2 values less than (150),
partition store_p3 values less than (200),
partition store_p4 values less than (MAXVALUE)
);
--创建分区表索引1，不指定索引分区的名称
mydb=# CREATE INDEX products_idx1 ON tpcds.products(c1) LOCAL;
CREATE INDEX
mydb=#
--创建分区表索引2，并指定索引分区的名称 (索引分区顺序及个数 对应 表分区)
mydb=# CREATE INDEX products_idx2 ON tpcds.products(c1) LOCAL
(
PARTITION products_idx1_p0,
PARTITION products_idx1_p1 TABLESPACE example1,
PARTITION products_idx1_p2 TABLESPACE example2,
PARTITION products_idx1_p3 TABLESPACE example3,
PARTITION products_idx1_p4 TABLESPACE example4
);
CREATE INDEX
mydb=#
--创建 GLOBAL 分区索引3
mydb=# CREATE INDEX products_idx3 ON tpcds.products(c1) GLOBAL;
ERROR: Global and local partition index should not be on same column
mydb=#
mydb=# CREATE INDEX products_idx3 ON tpcds.products(c2) GLOBAL;
CREATE INDEX
mydb=#
2.2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引
--–修改分区表索引 products_idx1 的表空间为 example1 (需先查出 分区索引的分区名 \d+ )
mydb=# ALTER INDEX tpcds.products_idx1 MOVE PARTITION store_p1_c1_idx TABLESPACE example1;
ALTER INDEX
mydb=#
mydb=# ALTER INDEX tpcds.products_idx1 RENAME TO products_idx11; --重命名 索引
ALTER INDEX
mydb=# ALTER INDEX tpcds.products_idx11 RENAME PARTITION store_p2_c1_idx TO store_p22_c1_idx; --重命名索引 分区名
ALTER INDEX
3.在分区表索引2上，重建单个索引分区和分区上的所有索引
mydb=# reindex index tpcds.products_idx2 PARTITION products_idx1_p1;
REINDEX
mydb=#
--–重建分区上的所有索引
mydb=# reindex table tpcds.products PARTITION store_p0;
REINDEX
4.使用\d+、系统视图 pg_indexes 和 pg_partition 查看索引信息
mydb=# \d+ tpcds.products
Table "tpcds.products"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------+-----------+----------+--------------+-------------
c1 | integer | | plain | |
c2 | character(2) | | extended | |
Indexes:
"products_idx11" btree (c1) LOCAL(PARTITION store_p0_c1_idx, PARTITION store_p1_c1_idx TABLESPACE example1, PARTITION store_p22_c1_idx, PARTITION store_p3_c1_idx, PARTITION store_p4_c1_idx) TABLESPACE pg_default
"products_idx2" btree (c1) LOCAL(PARTITION products_idx1_p0, PARTITION products_idx1_p1 TABLESPACE example1, PARTITION products_idx1_p2 TABLESPACE example2, PARTITION products_idx1_p3 TABLESPACE example3, PARTITION products_idx1_p4 TABLESPACE example4) TABLESPACE pg_default
"products_idx3" btree (c2) TABLESPACE pg_default
Range partition by(c1)
Number of partition: 5 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
mydb=#
mydb=# select * from pg_indexes where tablename = 'products';
schemaname | tablename | indexname | tablespace |
indexdef
------------+-----------+----------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tpcds | products | products_idx11 | | CREATE INDEX products_idx11 ON tpcds.products USING btree (c1) LOCAL(PARTITION store_p0_c1_idx, PARTITION store_p1_c1_idx TABLESPA
CE example1, PARTITION store_p22_c1_idx, PARTITION store_p3_c1_idx, PARTITION store_p4_c1_idx) TABLESPACE pg_default
tpcds | products | products_idx2 | | CREATE INDEX products_idx2 ON tpcds.products USING btree (c1) LOCAL(PARTITION products_idx1_p0, PARTITION products_idx1_p1 TABLESPACE example1, PARTITION products_idx1_p2 TABLESPACE example2, PARTITION products_idx1_p3 TABLESPACE example3, PARTITION products_idx1_p4 TABLESPACE example4) TABLESPACE pg_default
tpcds | products | products_idx3 | | CREATE INDEX products_idx3 ON tpcds.products USING btree (c2) TABLESPACE pg_default
(3 rows)
mydb=#
mydb=# select parentid::regclass,* from pg_partition ;
parentid | relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | relt
oastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | b
oundaries | transit | reloptions | relfrozenxid64
---------------------------+---------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+-----
----------+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+--------------------+----------+--
----------+---------+---------------------------------------------------+----------------
tpcds.customer_address_p1 | customer_address_p1 | r | 16390 | 0 | 0 | r | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 0 | | 1 | | |
| | {orientation=row,compression=no,wait_clean_gpi=n} | 0
tpcds.customer_address_p1 | p1 | p | 16390 | 0 | 0 | r | 16394 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 14332 | | | | | {
3000} | | {orientation=row,compression=no} | 14332
tpcds.customer_address_p1 | p2 | p | 16390 | 0 | 0 | r | 16395 | 16385 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 14332 | | | | | {
5000} | | {orientation=row,compression=no} | 14332
tpcds.customer_address_p1 | p3 | p | 16390 | 0 | 0 | r | 16396 | 16386 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 14332 | | | | | {
NULL} | | {orientation=row,compression=no} | 14332
tpcds.products_idx11 | store_p1_c1_idx | x | 16422 | 0 | 0 | n | 16435 | 16385 | 1 | 0 | 0 |
0 | 0 | 16418 | t | 0 | 0 | 0 | 0 | 0 | | | | |
| | | 0
tpcds.products_idx11 | store_p22_c1_idx | x | 16422 | 0 | 0 | n | 16425 | 0 | 1 | 0 | 0 |
0 | 0 | 16419 | t | 0 | 0 | 0 | 0 | 0 | | | | |
| | | 0
tpcds.products_idx2 | products_idx1_p1 | x | 16428 | 0 | 0 | n | 16436 | 16385 | 1 | 0 | 0 |
0 | 0 | 16418 | t | 0 | 0 | 0 | 0 | 0 | | | | |
| | | 0
tpcds.products_idx11 | store_p0_c1_idx | x | 16422 | 0 | 0 | n | 16437 | 0 | 1 | 0 | 0 |
0 | 0 | 16417 | t | 0 | 0 | 0 | 0 | 0 | | | | |
| | | 0
tpcds.products_idx2 | products_idx1_p0 | x | 16428 | 0 | 0 | n | 16438 | 0 | 1 | 0 | 0 |
0 | 0 | 16417 | t | 0 | 0 | 0 | 0 | 0 | | | | |
| | | 0
tpcds.products | products | r | 16413 | 0 | 0 | r | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 0 | | 1 | | |
| | {orientation=row,compression=no,wait_clean_gpi=n} | 0
tpcds.products | store_p0 | p | 16413 | 0 | 0 | r | 16417 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 14344 | | | | | {
50} | | {orientation=row,compression=no} | 14344
tpcds.products | store_p1 | p | 16413 | 0 | 0 | r | 16418 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 14344 | | | | | {
100} | | {orientation=row,compression=no} | 14344
tpcds.products | store_p2 | p | 16413 | 0 | 0 | r | 16419 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 14344 | | | | | {
150} | | {orientation=row,compression=no} | 14344
tpcds.products | store_p3 | p | 16413 | 0 | 0 | r | 16420 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 14344 | | | | | {
200} | | {orientation=row,compression=no} | 14344
tpcds.products | store_p4 | p | 16413 | 0 | 0 | r | 16421 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 14344 | | | | | {
NULL} | | {orientation=row,compression=no} | 14344
tpcds.products_idx11 | store_p3_c1_idx | x | 16422 | 0 | 0 | n | 16426 | 0 | 1 | 0 | 0 |
0 | 0 | 16420 | t | 0 | 0 | 0 | 0 | 0 | | | | |
| | | 0
tpcds.products_idx11 | store_p4_c1_idx | x | 16422 | 0 | 0 | n | 16427 | 0 | 1 | 0 | 0 |
0 | 0 | 16421 | t | 0 | 0 | 0 | 0 | 0 | | | | |
| | | 0
tpcds.products_idx2 | products_idx1_p2 | x | 16428 | 0 | 0 | n | 16431 | 16386 | 1 | 0 | 0 |
0 | 0 | 16419 | t | 0 | 0 | 0 | 0 | 0 | | | | |
| | | 0
tpcds.products_idx2 | products_idx1_p3 | x | 16428 | 0 | 0 | n | 16432 | 16387 | 1 | 0 | 0 |
0 | 0 | 16420 | t | 0 | 0 | 0 | 0 | 0 | | | | |
| | | 0
tpcds.products_idx2 | products_idx1_p4 | x | 16428 | 0 | 0 | n | 16433 | 16388 | 1 | 0 | 0 |
0 | 0 | 16421 | t | 0 | 0 | 0 | 0 | 0 | | | | |
| | | 0
(20 rows)
mydb=#
5.删除索引、表和表空间
omm=# DROP INDEX tpcds.products_idx11;
DROP INDEX
omm=# DROP INDEX tpcds.products_idx2;
DROP INDEX
omm=# DROP INDEX if exists tpcds.products_idx3;
DROP INDEX
omm=# drop table tpcds.products;
DROP TABLE
drop tablespace example1;
drop tablespace example2;
drop tablespace example3;
drop tablespace example4;
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
