openGauss每日一练第8天 | 分区表管理及课后作业

openGauss 每日一练第 8 天学习打卡，巩固 openGauss 数据库表空间管理基本操作！

学习目标

学习 openGauss分 区表

前面每日一练链接：

openGauss每日一练第1天 | 数据库和表的基本操作（一）
openGauss每日一练第2天 | 数据库和表的基本操作（二）
openGauss每日一练第3天 | 前三课作业实操练习
openGauss每日一练第4天 | 角色管理及课后作业
openGauss每日一练第5天 | 用户管理及课后作业
openGauss每日一练第6天 | 模式管理及课后作业
openGauss每日一练第7天 | 表空间管理及课后作业

课程学习

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

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

注意：在启动 gsql 的命令行中加上“-E”参数，就可以把 gsql 中各种以“\”开头的命令执行的实际 SQL 语句打印出来.如果在已运行的 gsql 中显示了某个命令实际执行的 SQL 语句后又想关闭此功能，该怎么办？这时可以使用“\set ECHO_HIDDEN on|off”命令。

连接 openGauss

su - omm
gsql -r

gsql -r -h localhost -U user1 -W JiekeXu12 -d postgres

1.创建分区表

–范围分区表

create database jiekexu;
\c jiekexu

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');

ERROR:  inserted partition key does not map to any table partition

–查看各区上的数据

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');

--注意不超过 200 时不包括 200.
insert into update_table values (200,'e');

注意事项

添加分区的表空间不能是PG_GLOBAL。
添加分区的名称不能与该分区表已有分区的名称相同。
添加分区的分区键值要和分区表的分区键的类型一致，且要大于分区表中最后一个范围分区的上边界。

如果目标分区表中已有分区数达到了最大值，则不能继续添加分区（范围分区表的分区数最大值是32767，哈希/列表分区表最大值是64）。

当分区表只有一个分区时，不能删除该分区。

选择分区使用PARTITION FOR()，括号里指定值个数应该与定义分区时使用的列个数相同，并且一一对应。

Value分区表不支持相应的Alter Partition操作。

列存分区表不支持切割分区。

间隔分区表不支持添加分区。

哈希分区表不支持切割分区，不支持合成分区，不支持添加和删除分区。

列表分区表不支持切割分区，不支持合成分区。

只有分区表的所有者或者被授予了分区表ALTER权限的用户有权限执行ALTER TABLE PARTITION命令，系统管理员默认拥有此权限。

3.修改分区属性

语法格式

ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name  )}
    RENAME PARTITION { partion_name | FOR ( partition_value [, ...] ) } TO partition_new_name;

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，在每个分区中插入记录

create table store
(
        id int,
        name CHAR(20)
)
partition by range (id)
(
        partition store_p0 values less than (500),
        partition store_p1 values less than (1000),
        partition store_p2 values less than (1500),
        partition store_p3 values less than (2000),
        partition store_p4 values less than (2500)
);

insert into store values (100, 'scott');
insert into store values (500, 'tiger');
insert into store values (700, 'postgres');
insert into store values (1200, 'MySQL');
insert into store values (2000, 'Oracle');

jiekexu=# \d+ store
                            Table "public.store"
 Column |     Type      | Modifiers | Storage  | Stats target | Description 
--------+---------------+-----------+----------+--------------+-------------
 id     | integer       |           | plain    |              | 
 name   | character(20) |           | extended |              | 
Range partition by(id)
Number of partition: 5 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no

select relname,parttype,parentid,rangenum,partstrategy from pg_partition;

2.查看分区1上的数据

select * from store partition (store_p0);

select * from store partition (store_p2);

3.重命名分区2

alter table store rename partition store_p1 to store_new_p1; 

4.删除分区5

alter table store drop partition store_p4; 

5.增加分区6

alter table store add partition store_p5 values less than (3000); 

6.在系统表 pg_partition 中查看分区信息

jiekexu=# \d+ store
                            Table "public.store"
 Column |     Type      | Modifiers | Storage  | Stats target | Description 
--------+---------------+-----------+----------+--------------+-------------
 id     | integer       |           | plain    |              | 
 name   | character(20) |           | extended |              | 
Range partition by(id)
Number of partition: 5 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no

jiekexu=#                                                                   
select relname,parttype,parentid,rangenum,partstrategy from pg_partition;
   relname    | parttype | parentid | rangenum | partstrategy 
--------------+----------+----------+----------+--------------
 store        | r        |    16440 |        0 | r
 store_p0     | p        |    16440 |        0 | r
 store_p2     | p        |    16440 |        0 | r
 store_p3     | p        |    16440 |        0 | r
 store_new_p1 | p        |    16440 |        0 | r
 store_p5     | p        |    16440 |        0 | r
(6 rows)

7.删除分区表

drop table store;

select relname,parttype,parentid,rangenum,partstrategy from pg_partition;
 relname | parttype | parentid | rangenum | partstrategy 
---------+----------+----------+----------+--------------
(0 rows)

欧耶，第八课分区表管理实操及课后作业练习题完成啦！