openGauss每日一练第8天 | 数据库分区表的操作

openGauss分区表的学习：

从官方对分区表的描述和要相关操作，跟oracle分区表差不多，分区类型少了个组合分区。

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

分区表可分为范围分区（Range Partitioning）、间隔分区（Interval Partitioning）、哈希分区（Hash Partitioning）、列表分区（List Partitioning）、数值分区（Value Partition）等。

目前行存表支持范围分区、间隔分区、哈希分区、列表分区，列存表仅支持范围分区。

分区表好处：

    可以减少数据的搜索空间，提高数据访问效率。

    如需大量加载或者删除的记录位于某个分区上可通过直接读取或删除那个分区以获得性能提升，且可以避免由于大量DELETE导致的VACUUM超载（仅范围分区）。

注意事项：

• 唯一约束和主键约束的约束键包含所有分区键将为约束创建LOCAL索引，否则创建GLOBAL索引。
• 目前哈希分区和列表分区仅支持单列构建分区键，暂不支持多列构建分区键。
• 只需要有间隔分区表的INSERT权限，往该表INSERT数据时就可以自动创建分区。

 https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/CREATE-TABLE-PARTITION.html

语法格式

CREATE TABLE [ IF NOT EXISTS ] partition_table_name
( [ 
    { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option [...] ] }[, ... ]
] )
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ COMPRESS | NOCOMPRESS ]
    [ TABLESPACE tablespace_name ]
     PARTITION BY { 
        {RANGE (partition_key) [ INTERVAL ('interval_expr') [ STORE IN (tablespace_name [, ... ] ) ] ] ( partition_less_than_item [, ... ] )} |
        {RANGE (partition_key) [ INTERVAL ('interval_expr') [ STORE IN (tablespace_name [, ... ] ) ] ] ( partition_start_end_item [, ... ] )} |
        {LIST | HASH (partition_key) (PARTITION partition_name [VALUES (list_values_clause)] opt_table_space )}
    } [ { ENABLE | DISABLE } ROW MOVEMENT ]; 
复制

这一练的一些命令和视图：

pg_partition 查看分区信息
\d+或\d：查看所有表信息
复制

课程练习

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

openGauss=# create table store
openGauss-# (
openGauss(#         c1 int,
openGauss(#         c2 CHAR(2)
openGauss(# )
openGauss-# partition by range (c1)
openGauss-# (
openGauss(#         partition store_table_p0 values less than (50),
openGauss(#         partition store_table_p1 values less than (100),
openGauss(#         partition store_table_p2 values less than (150),
openGauss(#         partition store_table_p3 values less than (200),
openGauss(#         partition store_table_p4 values less than (300)
openGauss(# );
CREATE TABLE
openGauss=# 
openGauss=# insert into store values (1, 'a'), (50, 'b'), (100, 'c'),(200, 'a'), (290, 'b');
INSERT 0 5
openGauss=# 
复制

2.查看分区1上的数据

openGauss=# select * from store partition(store_table_p0);
 c1 | c2 
----+----
  1 | a 
(1 row)
复制

3.重命名分区2

openGauss=# alter table store rename partition store_table_p1 to store_table_p10;
ALTER TABLE
openGauss=# 
复制

4.删除分区5

openGauss=# alter table store drop partition store_table_p4;
ALTER TABLE
复制

5.增加分区6

openGauss=# alter table store add partition store_table_p5 values less than (400);
ALTER TABLE
复制

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

openGauss=# select * from pg_partition;
     relname     | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | re
ltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey
 | intervaltablespace | interval | boundaries | transit |                    reloptions                     | relfrozenxid64 
-----------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+---
------------+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+--------
-+--------------------+----------+------------+---------+---------------------------------------------------+----------------
 store           | r        |    24646 |        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_table_p0  | p        |    24646 |        0 |           0 | r            |       24650 |             0 |        0 |         0 |             0 |   
          0 |             0 |          0 | t           |             0 |           0 |              0 |            0 | 93652        |          |        
 |                    |          | {50}       |         | {orientation=row,compression=no}                  |          93652
 store_table_p2  | p        |    24646 |        0 |           0 | r            |       24652 |             0 |        0 |         0 |             0 |   
          0 |             0 |          0 | t           |             0 |           0 |              0 |            0 | 93652        |          |        
 |                    |          | {150}      |         | {orientation=row,compression=no}                  |          93652
 store_table_p3  | p        |    24646 |        0 |           0 | r            |       24653 |             0 |        0 |         0 |             0 |   
          0 |             0 |          0 | t           |             0 |           0 |              0 |            0 | 93652        |          |        
 |                    |          | {200}      |         | {orientation=row,compression=no}                  |          93652
 store_table_p10 | p        |    24646 |        0 |           0 | r            |       24651 |             0 |        0 |         0 |             0 |   
          0 |             0 |          0 | t           |             0 |           0 |              0 |            0 | 93652        |          |        
 |                    |          | {100}      |         | {orientation=row,compression=no}                  |          93652
 store_table_p5  | p        |    24646 |        0 |           0 | r            |       24655 |             0 |        0 |         0 |             0 |   
          0 |             0 |          0 | t           |             0 |           0 |              0 |            0 | 99771        |          |        
 |                    |          | {400}      |         | {orientation=row,compression=no}                  |          99771
(6 rows)

openGauss=# 
复制

7.删除分区表

openGauss=# drop table store;
DROP TABLE
复制