openGauss每日一练第8天 | 学习openGauss分区表-学习笔记

学习目标
学习openGauss分区表

分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储，这张逻辑上的表称之为分区表，物理块称之为分区。

分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。

课程学习
连接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.

1.创建分区表
–范围分区表
omm=# create table update_table
omm-# (
omm(# c1 int,
omm(# c2 CHAR(2)
)
omm-# partition by range (c1)
omm(# 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(# );
CREATE TABLE

–查看分区表信息
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;
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
-----------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+--------------
update_table | r | 16410 | 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
-+---------------+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+---------
-+---------+--------------------+----------+------------+---------+---------------------------------------------------+----------------
update_table_p0 | p | 16410 | 0 | 0 | r | 16414 | 0 | 0 | 0 | 0
| 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9336 |
| | | | {50} | | {orientation=row,compression=no} | 9336
| 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9336 |
update_table_p1 | p | 16410 | 0 | 0 | r | 16415 | 0 | 0 | 0 | 0
update_table_p2 | p | 16410 | 0 | 0 | r | 16416 | 0 | 0 | 0 | 0
| | | | {100} | | {orientation=row,compression=no} | 9336
| | | | {150} | | {orientation=row,compression=no} | 9336
(4 rows)

| 0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9336 |
omm=#

–插入数据
omm=# insert into update_table values (1, 'a'), (50, 'b'), (100, 'c');
INSERT 0 3
–超出范围的数据会报错
omm=# insert into update_table values (150, 'd');
ERROR: inserted partition key does not map to any table partition
–查看各区上的数据
omm=# select * from update_table partition(update_table_p0);
c1 | c2
----+----
1 | a
(1 row)
omm=# select * from update_table partition(update_table_p1);
c1 | c2
----+----
50 | b
(1 row)

omm=#
omm=# select * from update_table partition(update_table_p2);
c1 | c2
-----+----
100 | c
(1 row)
2.创建分区
omm=# alter table update_table add partition update_table_p3 values less than (200);
ALTER TABLE
omm=# insert into update_table values (150, 'd');
INSERT 0 1
3.修改分区属性
omm=# alter table update_table rename partition update_table_p1 to update_table_p1_1;
ALTER TABLE
4.删除分区
omm=# alter table update_table drop partition update_table_p0;
ALTER TABLE
5.删除分区表
omm=# drop table update_table;
DROP TABLE

课程作业
1.创建一个含有5个分区的范围分区表store，在每个分区中插入记录
create table store
(
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 (250)
);
insert into store values (1, 'a'), (50, 'b'), (100, 'c'), (150, 'd'), (200, 'e');
omm=# create table store
omm-# (
omm(# c1 int,
omm(# c2 CHAR(2)
omm(# )
omm-# partition by range (c1)
omm-# (
omm(# partition store_p0 values less than (50),
omm(# partition store_p1 values less than (100),
omm(# partition store_p2 values less than (150),
omm(# partition store_p3 values less than (200),
omm(# partition store_p4 values less than (250)
omm(# );
CREATE TABLE
omm=# insert into store values (1, 'a'), (50, 'b'), (100, 'c'), (150, 'd'), (200, 'e');
INSERT 0 5

2.查看分区1上的数据
select * from store partition(store_p0);
select * from store partition(store_p1);
select * from store partition(store_p2);
select * from store partition(store_p3);
select * from store partition(store_p4);
omm=# select * from store partition(store_p0);
c1 | c2
----+----
1 | a
(1 row)

omm=# select * from store partition(store_p1);
c1 | c2
----+----
50 | b
(1 row)

omm=# select * from store partition(store_p2);
c1 | c2
-----+----
100 | c
(1 row)

omm=# select * from store partition(store_p3);
c1 | c2
-----+----
150 | d
(1 row)

omm=# select * from store partition(store_p4);
c1 | c2
-----+----
200 | e
(1 row)
3.重命名分区2
alter table store rename partition store_p1 to store_p1_1;
omm=# alter table store rename partition store_p1 to store_p1_1;
ALTER TABLE
4.删除分区5
alter table store drop partition store_p4;
omm=# alter table store drop partition store_p4;
ALTER TABLE
5.增加分区6
alter table store add partition store_p5 values less than (300);
omm=# alter table store add partition store_p5 values less than (300);
ALTER TABLE
6.在系统表pg_partition中查看分区信息
select * from pg_partition;
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | re
ltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | pa
rtkey | intervaltablespace | interval | boundaries | transit | reloptions | relfrozenxid64
------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+---
store | r | 16418 | 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_p0 | p | 16418 | 0 | 0 | r | 16422 | 0 | 0 | 0 | 0 |
------------+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+---
------+--------------------+----------+------------+---------+---------------------------------------------------+----------------
| | | {50} | | {orientation=row,compression=no} | 9343
store_p2 | p | 16418 | 0 | 0 | r | 16424 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9343 | |
| | | {150} | | {orientation=row,compression=no} | 9343
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9343 | |
| | | {200} | | {orientation=row,compression=no} | 9343
store_p1_1 | p | 16418 | 0 | 0 | r | 16423 | 0 | 0 | 0 | 0 |
store_p3 | p | 16418 | 0 | 0 | r | 16425 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9343 | |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9343 | |
| | | {100} | | {orientation=row,compression=no} | 9343
store_p5 | p | 16418 | 0 | 0 | r | 16427 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9348 | |
| | | {300} | | {orientation=row,compression=no} | 9348
(6 rows)
7.删除分区表
drop table store;
omm=# drop table store;
DROP TABLE