openGauss每日一练第 8 天 |openGauss创建删除分区表，增加删除分区，更改分区属性

学习openGauss的第八天

主要内容是openGauss创建删除分区表，增加删除分区，更改分区属性

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

登录数据库
su - omm
gsql -r

1.创建一个含有5个分区的范围分区表store，在每个分区中插入记录
omm=# create table store
omm-# (
omm(# c1 int , col2 char(20)
omm(# )
omm-# partition by range(c1)
omm-# (
omm(# partition col1_p1 values less than(10),
omm(# partition col1_p2 values less than(20),
omm(# partition col1_p3 values less than(30),
omm(# partition col1_p4 values less than(40),
omm(# partition col1_p5 values less than(50)
omm(# );
CREATE TABLE
omm=#

omm=# insert into store values(5,'li'),(12,'wang'),(26,'s'),(31,'z'),(49,'q');
INSERT 0 5

omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltu
ples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcude
screlid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit |
reloptions | relfrozenxid64
---------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+------
-----------------------------------------------+----------------
store | r | 16389 | 0 | 0 | r | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
-----+---------------+---------------+---------------+------------+-------------+---------------+-------------+--------
--------+--------------+--------------+----------+---------+--------------------+----------+------------+---------+----
0 | 0 | 0 | | 1 | | | | | {or
ientation=row,compression=no,wait_clean_gpi=n} | 0
col1_p1 | p | 16389 | 0 | 0 | r | 16393 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9034 | | | | | {10} | | {or
ientation=row,compression=no} | 9034
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9034 | | | | | {20} | | {or
ientation=row,compression=no} | 9034
col1_p3 | p | 16389 | 0 | 0 | r | 16395 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9034 | | | | | {30} | | {or
col1_p2 | p | 16389 | 0 | 0 | r | 16394 | 0 | 0 |
col1_p4 | p | 16389 | 0 | 0 | r | 16396 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9034 | | | | | {40} | | {or
ientation=row,compression=no} | 9034
col1_p5 | p | 16389 | 0 | 0 | r | 16397 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9034 | | | | | {50} | | {or
ientation=row,compression=no} | 9034
(6 rows)

2.查看分区1上的数据

omm=# select * from store partition(col1_p1) ;
(1 row)

omm=# c1 | col2
----+----------------------
5 | li


3.重命名分区2

omm=# alter table store rename partition col1_p2 to col1_p22 ;
ALTER TABLE


4.删除分区5

omm=# alter table store drop partition col1_p5 ;
ALTER TABLE

5.增加分区6

omm=# alter table store add partition col1_p6 values less than(60) ;
ALTER TABLE

6.在系统表pg_partition中查看分区信息
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | relt
uples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcud
escrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit |
reloptions | relfrozenxid64
----------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----
------+---------------+---------------+---------------+------------+-------------+---------------+-------------+-------
---------+--------------+--------------+----------+---------+--------------------+----------+------------+---------+---
------------------------------------------------+----------------
store | r | 16389 | 0 | 0 | r | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 0 | | 1 | | | | | {o
rientation=row,compression=no,wait_clean_gpi=n} | 0
col1_p1 | p | 16389 | 0 | 0 | r | 16393 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9034 | | | | | {10} | | {o
rientation=row,compression=no} | 9034
col1_p3 | p | 16389 | 0 | 0 | r | 16395 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9034 | | | | | {30} | | {o
rientation=row,compression=no} | 9034
col1_p4 | p | 16389 | 0 | 0 | r | 16396 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9034 | | | | | {40} | | {o
rientation=row,compression=no} | 9034
col1_p22 | p | 16389 | 0 | 0 | r | 16394 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9034 | | | | | {20} | | {o
rientation=row,compression=no} | 9034
col1_p6 | p | 16389 | 0 | 0 | r | 16398 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9323 | | | | | {60} | | {o
rientation=row,compression=no} | 9323
(6 rows)

7.删除分区表

omm=# drop table store ;
DROP TABLE