openGauss每日一练第8天课程笔记和作业

原创云散月明 All China Database Union 2021-12-14

594

数据库环境

openGauss:2.0.0 - 数据库实训平台

学习目标

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

学习笔记

创建分区表（范围分区表）

omm=# create table update_table
omm-# (
omm(#         c1 int,
omm(# 
omm-#         c2 CHAR(2)
omm(# )partition by range (c1)
omm-# (
omm(#         partition update_table_p0 values less than (50),
omm(#         partition update_table_p1 values less than (100),
omm(#         partition update_table_p2 values less than (150)
omm(# );
复制

查看分区表信息

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

omm=# select * from pg_partition;
复制

课后作业

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

omm=# create table store(store_id integer,store_name char(30))
partition by range(store_id)(
partition store_id_60 values less than(60),
partition store_id_70 values less than(70),
partition store_id_80 values less than(80),
partition store_id_90 values less than(90),
partition store_id_95 values less than(95));
CREATE TABLE
omm=# \d+ store;
                              Table "public.store"
   Column   |     Type      | Modifiers | Storage  | Stats target | Description 
------------+---------------+-----------+----------+--------------+-------------
 store_id   | integer       |           | plain    |              | 
 store_name | character(30) |           | extended |              | 
Range partition by(store_id)
Number of partition: 5 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
omm=# insert into store values (59,'lily'),(66,'rose'),(75,'tom'),(84,'jerry'),(94,'tim');
INSERT 0 5
omm=# select * from store; 
 store_id |           store_name           
----------+--------------------------------
       59 | lily                          
       66 | rose                          
       75 | tom                           
       84 | jerry                         
       94 | tim                           
(5 rows)
复制

2.查看分区1上的数据

omm=# select * from store partition(store_id_60);
 store_id |           store_name           
----------+--------------------------------
       59 | lily                          
(1 row)
复制

3.重命名分区2

omm=# alter table store rename partition store_id_60 to store_id_60_new;
ALTER TABLE
omm=# select relname from pg_partition;
     relname     
-----------------
 store
 store_id_70
 store_id_80
 store_id_90
 store_id_95
 store_id_60_new
(6 rows)
复制

4.删除分区5

omm=# alter table store drop partition store_id_95;
ALTER TABLE
omm=# select relname from pg_partition;
     relname     
-----------------
 store
 store_id_70
 store_id_80
 store_id_90
 store_id_60_new
(5 rows)
复制

5.增加分区6

omm=# alter table store add partition store_id_100 values less than(100);
ALTER TABLE
omm=# select relname from pg_partition;
     relname     
-----------------
 store
 store_id_70
 store_id_80
 store_id_90
 store_id_60_new
 store_id_100
(6 rows)

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

复制

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

omm=# select * from pg_partition;
     relname     | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoast
relid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervalt
ablespace | interval | boundaries | transit |                    reloptions                     | relfrozenxid64 
-----------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+---------
------+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+----------
----------+----------+------------+---------+---------------------------------------------------+----------------
 store           | r        |    16404 |        0 |           0 | r            |           0 |             0 |        0 |         0 |             0 |         
    0 |             0 |          0 | t           |             0 |           0 |              0 |            0 | 0            |          | 1       |          
          |          |            |         | {orientation=row,compression=no,wait_clean_gpi=n} |              0
 store_id_70     | p        |    16404 |        0 |           0 | r            |       16409 |             0 |        0 |         0 |             0 |         
    0 |             0 |          0 | t           |             0 |           0 |              0 |            0 | 9327         |          |         |          
          |          | {70}       |         | {orientation=row,compression=no}                  |           9327
 store_id_80     | p        |    16404 |        0 |           0 | r            |       16410 |             0 |        0 |         0 |             0 |         
    0 |             0 |          0 | t           |             0 |           0 |              0 |            0 | 9327         |          |         |          
 store_id_90     | p        |    16404 |        0 |           0 | r            |       16411 |             0 |        0 |         0 |             0 |         
    0 |             0 |          0 | t           |             0 |           0 |              0 |            0 | 9327         |          |         |          
          |          | {80}       |         | {orientation=row,compression=no}                  |           9327
          |          | {90}       |         | {orientation=row,compression=no}                  |           9327
 store_id_60_new | p        |    16404 |        0 |           0 | r            |       16408 |             0 |        0 |         0 |             0 |         
    0 |             0 |          0 | t           |             0 |           0 |              0 |            0 | 9327         |          |         |          
          |          | {60}       |         | {orientation=row,compression=no}                  |           9327
 store_id_100    | p        |    16404 |        0 |           0 | r            |       16413 |             0 |        0 |         0 |             0 |         
    0 |             0 |          0 | t           |             0 |           0 |              0 |            0 | 9332         |          |         |          
          |          | {100}      |         | {orientation=row,compression=no}                  |           9332
(6 rows)

复制

7.删除分区表

omm=# drop table store;
DROP TABLE
omm=# select * from pg_partition;
 relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit | reloptions | relfrozenxid64 
---------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+---------------+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+--------------------+----------+------------+---------+------------+----------------
(0 rows)
复制