简介PG库分区表及与Oracle库对比

原创已于 2023-03-18 18:14:27 修改 · 2.9k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库 #postgresql

于 2023-03-18 18:06:49 首次发布

本文介绍了PG库分区表的创建、查询和删除方法，并与Oracle的分区表进行了对比。在PG中，分区本质上是独立的表，而Oracle的分区是表的特性。PG允许灵活的操作，但不同表的分区名不能相同，而Oracle可以。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近公司需要将oralce数据库替换掉，作为造型研究，去研究了一下PG库，作为研究成果，以文章形式固化下来。

由于本地电脑上安装的PG库版本为10.20，所以有些功能（比如默认分区）无法测试。

一、PG库分区表简介

一、PG的分区表创建

创建一张表，并指定分区字段，如果不指定，则默认为非分区字段

我们创建一张有两层分区的表，但此时建表时，指定第一级分区为partition_name

CREATE TABLE partition_test_table (
    partition_name VARCHAR ( 20 ),
    subpartitoin_name NUMERIC,
    segment1 VARCHAR ( 20 ),
    segment2 VARCHAR ( 20 ),
    segment3 VARCHAR ( 20 ),
    segment4 VARCHAR ( 20 ),
    segment5 VARCHAR ( 20 ) 
) PARTITION BY list ( partition_name );

开始创建分区及指定二级分区字段

注意，如创建表一样，此时需要用partition by list 来指定二级分区，相当于为分区表创建分区。

create table p_a001 partition of partition_test_table for values in ('a001') partition by list(subpartitoin_name);

此时发现，创建分区的语句居然是create table。所以此时发现，在PG库里，分区本质上就是一张表，其结构与原表的表结构是一致的。

partition_test_table的表结构：

select * from pg_attribute a, pg_class b where a.attrelid = b.oid and b.relname = 'partition_test_table';

p_a001的表结构：

select * from pg_attribute a, pg_class b where a.attrelid = b.oid and b.relname = 'p_a001';

p_a001的表结构

创建二级分区

注意，此时就不再需要partition by list()语句了，因为不需要再向下创建分区。

create table p_a001_0 partition of p_a001 for values in (0);
create table p_a001_1 partition of p_a001 for values in (1);

p_a001_0的表结构：

select * from pg_attribute a, pg_class b where a.attrelid = b.oid and b.relname = 'p_a001_0';

多补充几条分区：

create table p_a002 partition of partition_test_table for values in ('a002') partition by list(subpartitoin_name);
create table p_a002_1 partition of p_a002 for values in (1);
create table p_a002_1 partition of p_a001 for values in (1);

至此，创建表及建分区就完成了。

删除表分区

操作方式和删除表一样，使用drop table

二、查询表分区

我们想知道一张表是不是分区表，怎么查询呢？

使用pg_partitioned_table，但是这个表里只有编号，没有表名，所以，再关联一下pg_class

select a.*, b.relname from pg_partitioned_table a, pg_class b where a.partrelid = b.oid;

如何查询一个表下具体有哪些分区呢？

这时就要用到分区层级关联的表 pg_inherits

至此，我们就可以再通过关联pg_class这个表，查询出对应的分区表名了

直接上代码，我使用 with table_name as 的语句包装一下，让语句看着更简洁明了

WITH allresult AS (
    WITH midtable AS (
        SELECT p.inhparent,
            c1.relname AS relname1,
            P.inhrelid,
            c2.relname AS relname2 
        FROM
            pg_inherits p,
            pg_class c1,
            pg_class c2 
        WHERE
            p.inhparent = c1.oid 
            AND p.inhrelid = c2.oid 
        ) SELECT a.relname1 AS TABLE_NAME,
        a.relname2 AS partition_name,
        b.relname2 AS subpartition_name 
    FROM
        midtable a,
        midtable b 
    WHERE
        a.inhrelid = b.inhparent 
    ) SELECT
    * 
FROM
    allresult;

由于我只创建了两个层级的分区，所以只显示两个分区层级。如果有多层分区，可自行编写代码实现。

如果用到的地方多，还可以直接创建一个view，这样就更方便使用了。

二、与ORACLE中的分区表的区别

此为近期研究总结，不保证全面

oracle建二级分区表

CREATE TABLE partition_test_table (
  partition_name VARCHAR2 ( 20 ),
  subpartitoin_name number,
  segment1 VARCHAR2 ( 20 ),
  segment2 VARCHAR2 ( 20 ),
  segment3 VARCHAR2 ( 20 ),
  segment4 VARCHAR2 ( 20 ),
  segment5 VARCHAR2 ( 20 ) 
) PARTITION BY list ( partition_name ) subpartition by list(subpartitoin_name)
(PARTITION p_a001 VALUES ('a001') 
 ( SUBPARTITION p_a001_0  VALUES (0)) );

ALTER TABLE partition_test_table modify PARTITION p_a001 ADD SUBPARTITION p_a001_1  VALUES (1));

ALTER TABLE partition_test_table ADD PARTITION  p_a002 VALUES ('a002') 
(
SUBPARTITION p_a002_0 values (0),
SUBPARTITION p_a002_1 values (1));

查看分区及二级分区

select * from USER_TAB_PARTITIONS where table_name = 'PARTITION_TEST_TABLE';
select * from USER_TAB_SUBPARTITIONS WHERE table_name = 'PARTITION_TEST_TABLE';

再建一个partition_test_table2表，并赋予同样的分区

CREATE TABLE partition_test_table2 (
  partition_name VARCHAR2 ( 20 ),
  subpartitoin_name number,
  segment1 VARCHAR2 ( 20 ),
  segment2 VARCHAR2 ( 20 ),
  segment3 VARCHAR2 ( 20 ),
  segment4 VARCHAR2 ( 20 ),
  segment5 VARCHAR2 ( 20 ) 
) PARTITION BY list ( partition_name ) subpartition by list(subpartitoin_name)
(PARTITION p_a001 VALUES ('a001') 
 ( SUBPARTITION p_a001_0  VALUES (0), 
  SUBPARTITION p_a001_1  VALUES (1)));

ALTER TABLE partition_test_table2 ADD PARTITION  p_a002 VALUES ('a002') 
(
SUBPARTITION p_a002_0 values (0),
SUBPARTITION p_a002_1 values (1));

查看partition_test_table2 分区

结论：不同表可的分区名是可以一样的。意思则是一个分区只属于某一张表。

在PG库里创建partition_test_table2表，并赋予p_a001分区

CREATE TABLE partition_test_table2 (
    partition_name VARCHAR ( 20 ),
    subpartitoin_name NUMERIC,
    segment1 VARCHAR ( 20 ),
    segment2 VARCHAR ( 20 ),
    segment3 VARCHAR ( 20 ),
    segment4 VARCHAR ( 20 ),
    segment5 VARCHAR ( 20 ) 
) PARTITION BY list ( partition_name );
create table p_a001 partition of partition_test_table2 for values in ('a001') partition by list(subpartitoin_name);