openGauss第8天|学习分区表

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

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

课程学习

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

• 查看分区表信息

\d+ update_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

select * from pg_partition;

• 插入数据

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

 c1 | c2 
----+----
  1 | a 
(1 row)

2.创建分区

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

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 (
    id int,
    name char(30)
) partition by range(id) (
    partition store_p1 values less than (50),
    partition store_p2 values less than (100),
    partition store_p3 values less than (150),
    partition store_p4 values less than (200),
    partition store_p5 values less than (250)
);
insert into store values
(1, 'store a'),
(51, 'store b'),
(101, 'store c'),
(151, 'store d'),
(201, 'store e');

2.查看分区1上的数据

select * from store partition(store_p1);

omm=# select * from store partition(store_p1);
 id |              name              
----+--------------------------------
  1 | store a                       
(1 row)

3.重命名分区2

alter table store rename partition store_p2 to store_p2_1;

4.删除分区5

alter table store drop partition store_p5;

5.增加分区6

alter table store add partition store_p6 values less than (300);

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

select * from pg_partition;

omm=# select * from pg_partition;

| relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | t
ransit |                    reloptions                     | relfrozenxid64 
------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+
  relname   | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages |
 reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx +----------------+--------------+--------------+----------+---------+--------------------+----------+------------+--
-------+---------------------------------------------------+----------------
-----------+---------------+---------------+---------------+------------+-------------+---------------+-------------
 store      | r        |    16397 |        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
         0 |             0 |             0 |             0 |          0 | t           |             0 |           0 
|              0 |            0 | 9326         |          |         |                    |          | {50}       |  
       | {orientation=row,compression=no}                  |           9326
 store_p3   | p        |    16397 |        0 |           0 | r            |       16403 |             0 |        0 |
 store_p1   | p        |    16397 |        0 |           0 | r            |       16401 |             0 |        0 |
|              0 |            0 | 9326         |          |         |                    |          | {150}      |  
       | {orientation=row,compression=no}                  |           9326
 store_p4   | p        |    16397 |        0 |           0 | r            |       16404 |             0 |        0 |
         0 |             0 |             0 |             0 |          0 | t           |             0 |           0 
|              0 |            0 | 9326         |          |         |                    |          | {200}      |  
         0 |             0 |             0 |             0 |          0 | t           |             0 |           0 
       | {orientation=row,compression=no}                  |           9326
 store_p2_1 | p        |    16397 |        0 |           0 | r            |       16402 |             0 |        0 |
         0 |             0 |             0 |             0 |          0 | t           |             0 |           0 
|              0 |            0 | 9326         |          |         |                    |          | {100}      |  
       | {orientation=row,compression=no}                  |           9326
 store_p6   | p        |    16397 |        0 |           0 | r            |       16406 |             0 |        0 |
         0 |             0 |             0 |             0 |          0 | t           |             0 |           0 
|              0 |            0 | 9330         |          |         |                    |          | {300}      |  
       | {orientation=row,compression=no}                  |           9330
(6 rows)

7.删除分区表

drop table store;