学习目标
学习openGauss分区表
分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储，这张逻辑上的表称之为分区表，物理块称之为分区。
分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。
课程学习
连接openGauss
#第一次进入等待15秒 #数据库启动中... su - omm gsql -r
1.创建分区表
–范围分区表
create table update_table
(
c1 int,
c2 CHAR(2)
)
partition by range (c1)
(
partition update_table_p0 values less than (50),
partition update_table_p1 values less than (100),
partition update_table_p2 values less than (150)
);
–查看分区表信息
\d+ update_table;
select * from pg_partition;
–插入数据
insert into update_table values (1, 'a'), (50, 'b'), (100, 'c');
–超出范围的数据会报错
insert into update_table values (150, 'd');
–查看各区上的数据
select * from update_table partition(update_table_p0);
2.创建分区
alter table update_table add partition update_table_p3 values less than (200);
insert into update_table values (150, 'd');
3.修改分区属性
alter table update_table rename partition update_table_p1 to update_table_p1_1;
4.删除分区
alter table update_table drop partition update_table_p0;
5.删除分区表
drop table update_table;
课程作业
1.创建一个含有5个分区的范围分区表store，在每个分区中插入记录
omm=# create table store
(store_id int,
store_name varchar(20)
)
partition by range(store_id)
(partition store_p1 values less than (50),
partition store_p2 values less than (100),
partition store_p3 values less than (150),
partition store_p4 vaomm-# lues less than (200),
partition store_p5 values less than (250)
);omm(# omm(# omm-# omm-# omm(# omm(# omm(# omm(# omm(#
CREATE TABLEomm=# insert into store values(1,'a'),(51,'b'),(101,'c'),(151,'d'),(201,'e');
INSERT 0 5
omm=# select * from store;
store_id | store_name
----------+------------
1 | a
51 | b
101 | c
151 | d
201 | e
(5 rows)2.查看分区1上的数据
omm=# select * from store partition(store_p1);
store_id | store_name
----------+------------
1 | a
(1 row)3.重命名分区2
omm=# alter table store rename partition store_p2 to store_p22;
ALTER TABLE
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indext
blid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit |
reloptions | relfrozenxid64
-----------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+---------------+---------------+-------
-----+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+--------------------+----------+------------+---------+---------
------------------------------------------+----------------
store | r | 16408 | 0 | 0 | r | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | t | 0 | 0 | 0 | 0 | 0 | | 1 | | | | | {orienta
tion=row,compression=no,wait_clean_gpi=n} | 0
store_p1 | p | 16408 | 0 | 0 | r | 16412 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | t | 0 | 0 | 0 | 0 | 9338 | | | | | {50} | | {orienta
tion=row,compression=no} | 9338
tion=row,compression=no} | 9338
store_p4 | p | 16408 | 0 | 0 | r | 16415 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | t | 0 | 0 | 0 | 0 | 9338 | | | | | {200} | | {orienta
tion=row,compression=no} | 9338
store_p3 | p | 16408 | 0 | 0 | r | 16414 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | t | 0 | 0 | 0 | 0 | 9338 | | | | | {150} | | {orienta
store_p5 | p | 16408 | 0 | 0 | r | 16416 | 0 | 0 | 0 | 0 | 0 | 0 |
tion=row,compression=no} | 9338
store_p22 | p | 16408 | 0 | 0 | r | 16413 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | t | 0 | 0 | 0 | 0 | 9338 | | | | | {100} | | {orienta
--More-- 0 | t | 0 | 0 | 0 | 0 | 9338 | | | | | {250} | | {orienta
tion=row,compression=no} | 9338
(6 rows)
4.删除分区5
omm=# alter table store drop partition store_p5;
ALTER TABLE
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indext
blid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit |
reloptions | relfrozenxid64
-----------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+---------------+---------------+-------
-----+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+--------------------+----------+------------+---------+---------
------------------------------------------+----------------
store | r | 16408 | 0 | 0 | r | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
store_p1 | p | 16408 | 0 | 0 | r | 16412 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | t | 0 | 0 | 0 | 0 | 0 | | 1 | | | | | {orienta
tion=row,compression=no,wait_clean_gpi=n} | 0
0 | t | 0 | 0 | 0 | 0 | 9338 | | | | | {50} | | {orienta
tion=row,compression=no} | 9338
0 |
0 | t | 0 | 0 | 0 | 0 | 9338 | | | | | {100} | | {orienta
tion=row,compression=no} | 9338
(5 rows)
store_p3 | p | 16408 | 0 | 0 | r | 16414 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | t | 0 | 0 | 0 | 0 | 9338 | | | | | {150} | | {orienta
tion=row,compression=no} | 9338
store_p4 | p | 16408 | 0 | 0 | r | 16415 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | t | 0 | 0 | 0 | 0 | 9338 | | | | | {200} | | {orienta
tion=row,compression=no} | 9338
store_p22 | p | 16408 | 0 | 0 | r | 16413 | 0 | 0 | 0 | 0 | 0 |5.增加分区6
omm=# alter table store add partition store_p6 values less than (300);
ALTER TABLE6.在系统表pg_partition中查看分区信息
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indext
blid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit |
reloptions | relfrozenxid64
-----------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+---------------+---------------+-------
-----+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+--------------------+----------+------------+---------+---------
------------------------------------------+----------------
store | r | 16408 | 0 | 0 | r | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | t | 0 | 0 | 0 | 0 | 0 | | 1 | | | | | {orienta
tion=row,compression=no,wait_clean_gpi=n} | 0
0 | t | 0 | 0 | 0 | 0 | 9338 | | | | | {50} | | {orienta
tion=row,compression=no} | 9338
store_p1 | p | 16408 | 0 | 0 | r | 16412 | 0 | 0 | 0 | 0 | 0 | 0 |
store_p3 | p | 16408 | 0 | 0 | r | 16414 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | t | 0 | 0 | 0 | 0 | 9338 | | | | | {150} | | {orienta
tion=row,compression=no} | 9338
store_p22 | p | 16408 | 0 | 0 | r | 16413 | 0 | 0 | 0 | 0 | 0 | 0 |
store_p4 | p | 16408 | 0 | 0 | r | 16415 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | t | 0 | 0 | 0 | 0 | 9338 | | | | | {100} | | {orienta
tion=row,compression=no} | 9338
0 | t | 0 | 0 | 0 | 0 | 9338 | | | | | {200} | | {orienta
tion=row,compression=no} | 9338
store_p6 | p | 16408 | 0 | 0 | r | 16417 | 0 | 0 | 0 | 0 | 0 | 0 |
--More-- 0 | t | 0 | 0 | 0 | 0 | 9342 | | | | | {300} | | {orienta
tion=row,compression=no} | 9342
(6 rows)7.删除分区表
omm=# drop table store;
DROP TABLE分区表注意合理设置partition的范围。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
