1.创建分区表索引
--1、使用gsql登录openGauss
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=#
--2、创建表空间
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
--3、创建用户
omm=# create schema tpcds;
CREATE SCHEMA
--4、创建分区表
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
);
CREATE TABLE
--5、创建分区表索引ds_customer_address_p1_index1，不指定索引分区的名称
omm=# CREATE INDEX ds_customer_address_p1_index1 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL;
CREATE INDEX
--6、创建分区表索引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
--7、创建GLOBAL分区索引
omm=# CREATE INDEX ds_customer_address_p1_index3 ON tpcds.customer_address_p1(CA_ADDRESS_ID) GLOBAL;
CREATE INDEX
--8、不指定关键字，默认创建GLOBAL分区索引
omm=# CREATE INDEX ds_customer_address_p1_index4 ON tpcds.customer_address_p1(CA_ADDRESS_ID);
CREATE INDEX
--9、查看索引信息
--方法一：\d+ 查看表信息
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_index2" btree (ca_address_sk) LOCAL(PARTITION ca_address_sk_index1, PARTITION ca_address_sk_index2 TABLESPACE exampl
e3, 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
"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 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
--方法二：查询pg_indexes 表
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_ad
dress_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) TABL
ESPACE pg_defaultdress_p1 USING btree (ca_address_sk) LOCAL(PARTITION ca_address_sk_index1, PARTITION ca_address_sk_index2 TABLESPACE example3, PARTITION ca_addr
ess_sk_index3 TABLESPACE example4) TABLESPACE pg_default
tpcds | customer_address_p1 | ds_customer_address_p1_index3 | | CREATE INDEX ds_customer_address_p1_index3 ON tpcds.customer_ad
tpcds | customer_address_p1 | ds_customer_address_p1_index4 | | CREATE INDEX ds_customer_address_p1_index4 ON tpcds.customer_ad
dress_p1 USING btree (ca_address_id) TABLESPACE pg_default
(4 rows)
dress_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_ad
--方法三：查询pg_partition表
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relal
lvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid
| intspnum | partkey | intervaltablespace | interval | boundaries | transit | reloptions | relfrozenxid6
4
----------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+------
---------+---------------+---------------+------------+-------------+---------------+-------------+----------------+--------------+-------------
-+----------+---------+--------------------+----------+------------+---------+---------------------------------------------------+--------------
--
customer_address_p1 | r | 16394 | 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 | 16394 | 0 | 0 | r | 16398 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9320
| | | | | {3000} | | {orientation=row,compression=no} | 932
0
p2 | p | 16394 | 0 | 0 | r | 16399 | 16389 | 0 | 0 |
| | | | | {5000} | | {orientation=row,compression=no} | 932
0
p3 | p | 16394 | 0 | 0 | r | 16400 | 16390 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9320
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9320
| | | | | {NULL} | | {orientation=row,compression=no} | 932
0
p1_ca_address_sk_idx | x | 16401 | 0 | 0 | n | 16402 | 0 | 1 | 0 |
0 | 0 | 0 | 16398 | t | 0 | 0 | 0 | 0 | 0
| | | | | | | |
0
p2_ca_address_sk_idx | x | 16401 | 0 | 0 | n | 16403 | 0 | 1 | 0 |
0 | 0 | 0 | 16399 | t | 0 | 0 | 0 | 0 | 0
| | | | | | | |
0
p3_ca_address_sk_idx | x | 16401 | 0 | 0 | n | 16404 | 0 | 1 | 0 |
0 | 0 | 0 | 16400 | t | 0 | 0 | 0 | 0 | 0
| | | | | | | |
0
ca_address_sk_index1 | x | 16405 | 0 | 0 | n | 16406 | 0 | 1 | 0 |
0 | 0 | 0 | 16398 | t | 0 | 0 | 0 | 0 | 0
| | | | | | | |
0
ca_address_sk_index2 | x | 16405 | 0 | 0 | n | 16407 | 16391 | 1 | 0 |
0 | 0 | 0 | 16399 | t | 0 | 0 | 0 | 0 | 0
| | | | | | | |
0
ca_address_sk_index3 | x | 16405 | 0 | 0 | n | 16408 | 16392 | 1 | 0 |
0 | 0 | 0 | 16400 | t | 0 | 0 | 0 | 0 | 0
| | | | | | | |
0
(10 rows)
2.修改分区表索引定义
--1、修改分区表索引CA_ADDRESS_SK_index2的表空间为example1
omm=# ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION CA_ADDRESS_SK_index2 TABLESPACE example1;
ALTER INDEX
--2、修改分区表索引CA_ADDRESS_SK_index3的表空间为example2
omm=# ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION CA_ADDRESS_SK_index3 TABLESPACE example2;
ALTER INDEX
--3、重命名分区表索引
omm=# ALTER INDEX tpcds.ds_customer_address_p1_index2 RENAME PARTITION CA_ADDRESS_SK_index1 TO CA_ADDRESS_SK_index4;
ALTER INDEX
3.重建索引分区
--1、重建单个索引分区
omm=# reindex index tpcds.ds_customer_address_p1_index1 PARTITION p1_ca_address_sk_idx;
REINDEX
--2、重建分区上的所有索引
omm=# reindex table tpcds.customer_address_p1 PARTITION p1;
REINDEX
4.删除索引
--删除索引
omm=# DROP INDEX tpcds.ds_customer_address_p1_index1;
DROP INDEX
omm=# DROP INDEX tpcds.ds_customer_address_p1_index2;
DROP INDEX
omm=# DROP INDEX tpcds.ds_customer_address_p1_index3;
DROP INDEX
omm=# DROP INDEX tpcds.ds_customer_address_p1_index4;
DROP INDEX
--验证索引是否被删除
omm=# \d+ tpcds.customer_address_p1;
Table "tpcds.customer_address_p1"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+-----------------------+-----------+----------+--------------+-------------
e | character(20) | | extended | |
Range partition by(ca_address_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no 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_typ
Options: orientation=row, compression=no
练习：
1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3
omm=# create schema junzi;
CREATE SCHEMA
omm=# CREATE TABLE junzi.products
omm-# (
omm(# CA_ADDRESS_SK INTEGER NOT NULL,
omm(# CA_ADDRESS_ID CHAR(16) NOT NULL,
omm(# CA_STREET_NUMBER CHAR(10) ,
omm(# CA_STREET_NAME VARCHAR(60) ,
omm(# CA_STREET_TYPE CHAR(15) ,
omm(# CA_SUITE_NUMBER CHAR(10) ,
omm(# CA_CITY VARCHAR(60) ,
omm(# CA_COUNTY VARCHAR(30) ,
omm(# CA_STATE CHAR(2) ,
omm(# CA_ZIP CHAR(10) ,
omm(# CA_COUNTRY VARCHAR(20) ,
omm(# CA_GMT_OFFSET DECIMAL(5,2) ,
omm(# CA_LOCATION_TYPE CHAR(20)
omm(# )
omm-# PARTITION BY RANGE(CA_ADDRESS_SK)
omm-# (
omm(# PARTITION p1 VALUES LESS THAN (3000),
omm(# PARTITION p2 VALUES LESS THAN (5000) TABLESPACE tbs_01,
omm(# PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE tbs_02
omm(# );
CREATE TABLE
omm=# CREATE INDEX products_index1 ON junzi.products(CA_ADDRESS_SK) LOCAL;
CREATE INDEX
omm=# CREATE INDEX products_index2 ON
omm-# junzi.products(CA_ADDRESS_SK) LOCAL
omm-# (
omm(# PARTITION CA_ADDRESS_SK_index1,
omm(# PARTITION CA_ADDRESS_SK_index2 TABLESPACE tbs_03,
omm(# PARTITION CA_ADDRESS_SK_index3 TABLESPACE tbs_04
omm(# );
CREATE INDEX
omm=# CREATE INDEX products_index3 ON junzi.products(CA_ADDRESS_ID) GLOBAL;
CREATE INDEX
2.在分区表索引2上，修改分区表索引的表空间，重命名分区表索引
omm=# ALTER INDEX junzi.products_index2 MOVE PARTITION
omm-# CA_ADDRESS_SK_index2 TABLESPACE tbs_01;
ALTER INDEX
omm=# ALTER INDEX junzi.products_index2 RENAME PARTITION
omm-# CA_ADDRESS_SK_index1 TO CA_ADDRESS_SK_index4;
ALTER INDEX
3.在分区表索引2上，重建单个索引分区和分区上的所有索引
omm=# reindex index junzi.products_index2 PARTITION CA_ADDRESS_SK_index2;
REINDEX
omm=# reindex table junzi.products PARTITION p1;
REINDEX
4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
omm=# \d+ junzi.products;
Table "junzi.products"
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 | |
"products_index3" btree (ca_address_id) TABLESPACE pg_default
Range partition by(ca_address_sk)
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:
"products_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
"products_index2" btree (ca_address_sk) LOCAL(PARTITION ca_address_sk_index4, PARTITION ca_address_sk_index2 TABLESPACE tbs_01, PARTITION ca_address_sk_index3 TABLESPACE tbs_04) TABLESPACE pg_default
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
------------+-----------+-----------------+------------+----------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------
-----
junzi | products | products_index1 | | CREATE INDEX products_index1 ON junzi.products USING btree (ca_address_sk) LOCAL(PARTIT
ION p1_ca_address_sk_idx, PARTITION p2_ca_address_sk_idx, PARTITION p3_ca_address_sk_idx) TABLESPACE pg_default
junzi | products | products_index2 | | CREATE INDEX products_index2 ON junzi.products USING btree (ca_address_sk) LOCAL(PARTIT
ION ca_address_sk_index4, PARTITION ca_address_sk_index2 TABLESPACE tbs_01, PARTITION ca_address_sk_index3 TABLESPACE tbs_04) TABLESPACE pg_def
ault
junzi | products | products_index3 | | CREATE INDEX products_index3 ON junzi.products USING btree (ca_address_id) TABLESPACE p
g_default
(3 rows)
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relal
lvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid
| intspnum | partkey | intervaltablespace | interval | boundaries | transit | reloptions | relfrozenxid6
4
----------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+------
---------+---------------+---------------+------------+-------------+---------------+-------------+----------------+--------------+-------------
-+----------+---------+--------------------+----------+------------+---------+---------------------------------------------------+--------------
--
customer_address_p1 | r | 16394 | 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 | 16394 | 0 | 0 | r | 16398 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9320
| | | | | {3000} | | {orientation=row,compression=no} | 932
0
p2 | p | 16394 | 0 | 0 | r | 16399 | 16389 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9320
| | | | | {5000} | | {orientation=row,compression=no} | 932
0
p3 | p | 16394 | 0 | 0 | r | 16400 | 16390 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9320
| | | | | {NULL} | | {orientation=row,compression=no} | 932
0
products | r | 16421 | 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 | 16421 | 0 | 0 | r | 16425 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9343
| | | | | {3000} | | {orientation=row,compression=no} | 934
3
p2 | p | 16421 | 0 | 0 | r | 16426 | 16416 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9343
| | | | | {5000} | | {orientation=row,compression=no} | 934
3
p3 | p | 16421 | 0 | 0 | r | 16427 | 16417 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9343
| | | | | {NULL} | | {orientation=row,compression=no} | 934
3
p2_ca_address_sk_idx | x | 16428 | 0 | 0 | n | 16430 | 0 | 1 | 0 |
0 | 0 | 0 | 16426 | t | 0 | 0 | 0 | 0 | 0
| | | | | | | |
0
p3_ca_address_sk_idx | x | 16428 | 0 | 0 | n | 16431 | 0 | 1 | 0 |
0 | 0 | 0 | 16427 | t | 0 | 0 | 0 | 0 | 0
| | | | | | | |
0
ca_address_sk_index3 | x | 16432 | 0 | 0 | n | 16435 | 16419 | 1 | 0 |
0 | 0 | 0 | 16427 | t | 0 | 0 | 0 | 0 | 0
| | | | | | | |
0
ca_address_sk_index2 | x | 16432 | 0 | 0 | n | 16438 | 16416 | 1 | 0 |
0 | 0 | 0 | 16426 | t | 0 | 0 | 0 | 0 | 0
| | | | | | | |
0
p1_ca_address_sk_idx | x | 16428 | 0 | 0 | n | 16439 | 0 | 1 | 0 |
0 | 0 | 0 | 16425 | t | 0 | 0 | 0 | 0 | 0
| | | | | | | |
0
ca_address_sk_index4 | x | 16432 | 0 | 0 | n | 16440 | 0 | 1 | 0 |
0 | 0 | 0 | 16425 | t | 0 | 0 | 0 | 0 | 0
| | | | | | | |
0
(14 rows)
5.删除索引、表和表空间
omm=# DROP INDEX junzi.products_index1;
DROP INDEX
omm=# DROP INDEX junzi.products_index2;
DROP INDEX
omm=# DROP INDEX junzi.products_index3;
DROP INDEX
omm=# drop table junzi.products;
DROP TABLE
omm-# drop tablespace tbs_01;
DROP TABLESPACE
omm=# drop tablespace tbs_02;
DROP TABLESPACE
omm=# drop tablespace tbs_03;
DROP TABLESPACE
omm=# drop tablespace tbs_04;
DROP TABLESPACE
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
