学习目标:学习openGauss分区表.
课程学习:
分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储，这张逻辑上的表称之为分区表，物理块称之为分区。
分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。
部分学习内容如下:
课程作业:
1.创建一个含有5个分区的范围分区表store，在每个分区中插入记录
omm=# create table store(id integer,name char(30)) partition by range(id)
omm-# (partition p1 values less than(100),partition p2 values less than(200),partition p3 values
omm(# less than (300),partition p4 values less than (400),partition p5 values less than (500));
CREATE TABLE
omm=# insert into store values(50,'a'),(150,'b'),(250,'c'),(350,'d'),(450,'e');
INSERT 0 5
2.查看分区1上的数据
omm=# select * from store partition(p1);
omm=# id | name
----+--------------------------------
50 | a
(1 row)
3.重命名分区2
omm=# alter table store rename partition p2 to p6;
ALTER TABLE
4.删除分区5
omm=# alter table store drop partition p5;
ALTER TABLE
5.增加分区6
omm=# alter table store add partition p7 values less than (600);
ALTER TABLE
6.在系统表pg_partition中查看分区信息
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples |
relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relc
udescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit | relop
tions | relfrozenxid64
---------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+-
--------------+---------------+---------------+------------+-------------+---------------+-------------+----------------+-----
---------+--------------+----------+---------+--------------------+----------+------------+---------+-------------------------
--------------------------+----------------
store | r | 16389 | 0 | 0 | r | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 0 | | 1 | | | | | {orientation=row,compres
sion=no,wait_clean_gpi=n} | 0
p1 | p | 16389 | 0 | 0 | r | 16393 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
p3 | p | 16389 | 0 | 0 | r | 16395 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9034 | | | | | {100} | | {orientation=row,compres
sion=no} | 9034
p4 | p | 16389 | 0 | 0 | r | 16396 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9034 | | | | | {400} | | {orientation=row,compres
--More-- 0 | 9034 | | | | | {300} | | {orientation=row,compres
sion=no} | 9034
sion=no} | 9034
p6 | p | 16389 | 0 | 0 | r | 16394 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9034 | | | | | {200} | | {orientation=row,compres
sion=no} | 9034
p7 | p | 16389 | 0 | 0 | r | 16398 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9323 | | | | | {600} | | {orientation=row,compres
sion=no} | 9323
(6 rows)
7.删除分区表
omm=# drop table store;
DROP TABLE
