openGauss每日一练第8天|《学习openGauss分区表》学习心得体会和课后练习

openGauss每日一练第8天|《学习openGauss分区表》学习心得体会和课后练习

学习openGauss分区表

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

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

课程学习

连接openGauss

#第一次进入等待15秒
#数据库启动中...
su - omm
gsql -r

1.创建分区表

–范围分区表

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


omm=# 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_omm-# p2 values less than (150)
);omm(# omm(# omm(# omm-# omm-# omm(# omm(# omm(# 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

–查看分区表信息

\d+ update_table;
select * from pg_partition;

Expanded display is on.
omm=# select * from pg_partition;
-[ RECORD 1 ]------+--------------------------------------------------
relname            | update_table
parttype           | r
parentid           | 16389
rangenum           | 0
intervalnum        | 0
partstrategy       | r
relfilenode        | 0relcudescidx       | 0
relfrozenxid       | 0

reltablespace      | 0
relpages           | 0
reltuples          | 0
relallvisible      | 0
reltoastrelid      | 0
reltoastidxid      | 0
indextblid         | 0
indisusable        | t
reldeltarelid      | 0
reldeltaidx        | 0
relcudescrelid     | 0
--More--intspnum           | 
partkey            | 1
intervaltablespace | 
interval           | 
boundaries         | 
transit            | 
reloptions         | {orientation=row,compression=no,wait_clean_gpi=n}
relfrozenxid64     | 0
-[ RECORD 2 ]------+--------------------------------------------------
relname            | update_table_p0
parttype           | p
parentid           | 16389
rangenum           | 0
intervalnum        | 0
partstrategy       | r
relfilenode        | 16393
reltablespace      | 0
relpages           | 0
reltuples          | 0
relallvisible      | 0
reltoastrelid      | 0
reltoastidxid      | 0
indextblid         | 0
indisusable        | t
reldeltarelid      | 0
reldeltaidx        | 0
relcudescrelid     | 0
relcudescidx       | 0
relfrozenxid       | 9034
intspnum           | 
partkey            | 
intervaltablespace | 
interval           | 
boundaries         | {50}
transit            | 
reloptions         | {orientation=row,compression=no}
relfrozenxid64     | 9034
-[ RECORD 3 ]------+--------------------------------------------------
relname            | update_table_p1
parttype           | p
parentid           | 16389
rangenum           | 0
intervalnum        | 0
partstrategy       | r
reltuples          | 0
relfilenode        | 16394
reltablespace      | 0
relpages           | 0
relallvisible      | 0
reltoastrelid      | 0
reltoastidxid      | 0
indextblid         | 0
indisusable        | t
reldeltarelid      | 0
reldeltaidx        | 0
relcudescrelid     | 0
relcudescidx       | 0
relfrozenxid       | 9034
intspnum           | 
partkey            | 
intervaltablespace | 
interval           | 
boundaries         | {100}
transit            | 
-[ RECORD 4 ]------+--------------------------------------------------
relname            | update_table_p2
parttype           | p
parentid           | 16389
rangenum           | 0
reloptions         | {orientation=row,compression=no}
relfrozenxid64     | 9034
intervalnum        | 0
partstrategy       | r
relfilenode        | 16395
reltablespace      | 0
relpages           | 0
reltuples          | 0
relallvisible      | 0
reltoastrelid      | 0
reltoastidxid      | 0
indextblid         | 0
indisusable        | t
reldeltarelid      | 0
reldeltaidx        | 0
relcudescrelid     | 0
relcudescidx       | 0
relfrozenxid       | 9034
intspnum           | 
partkey            | 
intervaltablespace | 
interval           | 
boundaries         | {150}
transit            | 
reloptions         | {orientation=row,compression=no}
relfrozenxid64     | 9034

–插入数据

insert into update_table values (1, 'a'), (50, 'b'), (100, 'c');

–超出范围的数据会报错

insert into update_table values (150, 'd');

–查看各区上的数据

select * from update_table partition(update_table_p0);
mm=# select * from update_table partition(update_table_p0);
omm=# -[ RECORD 1 ]
c1 | 1
c2 | a 

omm=# 
omm=# 
omm=# select * from update_table partition(update_table_p1);
-[ RECORD 1 ]
c1 | 50
c2 | b 

omm=# 
omm=# select * from update_table partition(update_table_p2);
-[ RECORD 1 ]
c1 | 100
c2 | c 

omm=# 

2.创建分区

alter table update_table add partition update_table_p3 values less than (200);
insert into update_table values (150, 'd');
select * from update_table partition(update_table_p3);

3.修改分区属性

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
(
        c1 int,
        c2 CHAR(2)
)
partition by range (c1)
(
        partition store_p0 values less than (10),
        partition store_p1 values less than (20),
        partition store_p2 values less than (30),
        partition store_p3 values less than (40),
        partition store_p4 values less than (100)		
);



insert into store values (1, '1x'), (11, '1b'), (21, '2c'),(31, '3c'),(41, '4c');

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

3.重命名分区2

alter table store rename partition store_p2  to store_p2_02;

4.删除分区5

alter table store drop partition store_p4;

5.增加分区6

alter table store add partition update_table_p5 values less than (200);

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

select * from pg_partition where relname='store'

7.删除分区表

drop table store;