数据库环境
openGauss:2.0.0 - 数据库实训平台
学习目标
分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储，这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。
学习笔记
- 创建分区表（范围分区表）
omm=# create table update_table
omm-# (
omm(# c1 int,
omm(#
omm-# c2 CHAR(2)
omm(# )partition by range (c1)
omm-# (
omm(# partition update_table_p0 values less than (50),
omm(# partition update_table_p1 values less than (100),
omm(# partition update_table_p2 values less than (150)
omm(# );
- 查看分区表信息
omm=# \d+ update_table;
Table "public.update_table"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------+-----------+----------+--------------+-------------
c1 | integer | | plain | |
c2 | character(2) | | extended | |
Range partition by(c1)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
omm=# select * from pg_partition;
课后作业
1.创建一个含有5个分区的范围分区表store，在每个分区中插入记录
omm=# create table store(store_id integer,store_name char(30))
partition by range(store_id)(
partition store_id_60 values less than(60),
partition store_id_70 values less than(70),
partition store_id_80 values less than(80),
partition store_id_90 values less than(90),
partition store_id_95 values less than(95));
CREATE TABLE
omm=# \d+ store;
Table "public.store"
Column | Type | Modifiers | Storage | Stats target | Description
------------+---------------+-----------+----------+--------------+-------------
store_id | integer | | plain | |
store_name | character(30) | | extended | |
Range partition by(store_id)
Number of partition: 5 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
omm=# insert into store values (59,'lily'),(66,'rose'),(75,'tom'),(84,'jerry'),(94,'tim');
INSERT 0 5
omm=# select * from store;
store_id | store_name
----------+--------------------------------
59 | lily
66 | rose
75 | tom
84 | jerry
94 | tim
(5 rows)
2.查看分区1上的数据
omm=# select * from store partition(store_id_60);
store_id | store_name
----------+--------------------------------
59 | lily
(1 row)
3.重命名分区2
omm=# alter table store rename partition store_id_60 to store_id_60_new;
ALTER TABLE
omm=# select relname from pg_partition;
relname
-----------------
store
store_id_70
store_id_80
store_id_90
store_id_95
store_id_60_new
(6 rows)
4.删除分区5
omm=# alter table store drop partition store_id_95;
ALTER TABLE
omm=# select relname from pg_partition;
relname
-----------------
store
store_id_70
store_id_80
store_id_90
store_id_60_new
(5 rows)
5.增加分区6
omm=# alter table store add partition store_id_100 values less than(100);
ALTER TABLE
omm=# select relname from pg_partition;
relname
-----------------
store
store_id_70
store_id_80
store_id_90
store_id_60_new
store_id_100
(6 rows)
omm=# \d+ store
Table "public.store"
Column | Type | Modifiers | Storage | Stats target | Description
------------+---------------+-----------+----------+--------------+-------------
store_id | integer | | plain | |
store_name | character(30) | | extended | |
Range partition by(store_id)
Number of partition: 5 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
6.在系统表pg_partition中查看分区信息
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoast
relid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervalt
ablespace | interval | boundaries | transit | reloptions | relfrozenxid64
-----------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+---------
------+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+----------
----------+----------+------------+---------+---------------------------------------------------+----------------
store | r | 16404 | 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
store_id_70 | p | 16404 | 0 | 0 | r | 16409 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9327 | | |
| | {70} | | {orientation=row,compression=no} | 9327
store_id_80 | p | 16404 | 0 | 0 | r | 16410 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9327 | | |
store_id_90 | p | 16404 | 0 | 0 | r | 16411 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9327 | | |
| | {80} | | {orientation=row,compression=no} | 9327
| | {90} | | {orientation=row,compression=no} | 9327
store_id_60_new | p | 16404 | 0 | 0 | r | 16408 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9327 | | |
| | {60} | | {orientation=row,compression=no} | 9327
store_id_100 | p | 16404 | 0 | 0 | r | 16413 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9332 | | |
| | {100} | | {orientation=row,compression=no} | 9332
(6 rows)
7.删除分区表
omm=# drop table store;
DROP TABLE
omm=# 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
---------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+---------------+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+--------------------+----------+------------+---------+------------+----------------
(0 rows)
学习资源
- openGauss SQL学习参考资料
- 每日一练：openGauss数据库在线实训课程
- openGauss每日一练 | 21期养成好习惯，提升技术能力！
- 墨天轮Markdown编辑器使用介绍
- 墨天轮数据库在线实训平台V1.0操作手册
- 墨天轮数据社区
欢迎各位同学一起来交流学习心得！
最后修改时间：2021-12-16 18:37:38
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
