openGauss每日一练第8天｜openGauss分区表的操作

坚持学习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.
omm=#

1.创建一个含有5个分区的范围分区表store，在每个分区中插入记录

omm=# 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)
 );
omm=# CREATE TABLE
–插入数据
omm=# insert into store values (1,'xx'), (50,'yy'), (100,'zz'),(150,'xy'),(200,'yz');
INSERT 0 5
–超出范围的数据会报错
omm=# insert into store values (250, 'xyz');
ERROR:  value too long for type character(2)
CONTEXT:  referenced column: c2

2.查看分区1上的数据

omm=# select * from store partition(store_p0);
omm=#  c1 | c2 
----+----
  1 | xx
(1 row)

3.重命名分区2

omm=# alter table store rename partition store_p1 to store_p1_1;
ALTER TABLE

4.删除分区5

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

5.增加分区6

omm=# alter table store add partition store_p5 values less than (300);
ALTER TABLE
omm=# insert into store values (250, 'xz');
INSERT 0 1

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

–查看分区表信息
omm=# \d+ store;
                           Table "public.store"
 Column |     Type     | Modifiers | Storage  | Stats target | Description 
--------+--------------+-----------+----------+--------------+-------------
 c1     | integer      |           | plain    |              | 
 c2     | character(2) |           | extended |              | 
Range partition by(c1)
Number of partition: 5 (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 | r
elcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit |                    re
loptions                     | relfrozenxid64 
------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+----------
-+---------------+---------------+---------------+------------+-------------+---------------+-------------+----------------+--
------------+--------------+----------+---------+--------------------+----------+------------+---------+----------------------
-----------------------------+----------------
 store      | r        |    16408 |        0 |           0 | r            |           0 |             0 |        0 |         0
 |             0 |             0 |             0 |          0 | t           |             0 |           0 |              0 |  
          0 | 9327         |          |         |                    |          | {50}       |         | {orientation=row,comp
ression=no}                  |           9327
 |             0 |             0 |             0 |          0 | t           |             0 |           0 |              0 |  
          0 | 0            |          | 1       |                    |          |            |         | {orientation=row,comp
ression=no,wait_clean_gpi=n} |              0
 store_p0   | p        |    16408 |        0 |           0 | r            |       16412 |             0 |        0 |         0
 |             0 |             0 |             0 |          0 | t           |             0 |           0 |              0 |  
          0 | 9327         |          |         |                    |          | {150}      |         | {orientation=row,comp
ression=no}                  |           9327
 store_p3   | p        |    16408 |        0 |           0 | r            |       16415 |             0 |        0 |         0
 |             0 |             0 |             0 |          0 | t           |             0 |           0 |              0 |  
          0 | 9327         |          |         |                    |          | {200}      |         | {orientation=row,comp
--More-- store_p2   | p        |    16408 |        0 |           0 | r            |       16414 |             0 |        0 |         0
ression=no}                  |           9327
 store_p5   | p        |    16408 |        0 |           0 | r            |       16417 |             0 |        0 |         0
 |             0 |             0 |             0 |          0 | t           |             0 |           0 |              0 |  
          0 | 9329         |          |         |                    |          | {300}      |         | {orientation=row,comp
ression=no}                  |           9329
 store_p1_1 | p        |    16408 |        0 |           0 | r            |       16413 |             0 |        0 |         0
 |             0 |             0 |             0 |          0 | t           |             0 |           0 |              0 |  
          0 | 9327         |          |         |                    |          | {100}      |         | {orientation=row,comp
ression=no}                  |           9327
(6 rows)

7.删除分区表

omm=# drop table store;
DROP TABLE

通过学习和作业，更加深入的了解了openGauss分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储，这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。