在迁移数据库到 PostgreSQL 时，有时会遇到一些 PostgreSQL 中尚不支持的功能。这种情况特别容易发生在没有现成扩展能够模拟所需要特性的情况下，在 HexaCluster，经常面临这类情况。如果有可能为 PostgreSQL 创建一个扩展以提供所需功能，例下一下扩展：

pg_utl_smtp

pg_dbms_lock

pg_dbms_metadata

pgtt

pg_dbms_job

并非所有特性都能通过创建扩展得以实现。目前讨论其从 Oracle 迁移到 PostgreSQL

时关于“参考分区”支持的问题。这种类型的分区在 Oracle 数据库自 11g 版本开始就已提供，除了在 PostgreSQL 中同样可用的常规分区类型，如 RANGE、LIST 和 HASH 分区。

###参考分区说明

参考分区（Partitioning by reference）允许根据并非来自被分区表中的列来创建分区，而是通过外键引用另一张不同表中的列进行分区。这一特性对于以相关方式对相关联的表进行分区非常有用，即使这些表之间并未共享相同的列。

如下：

```

CREATE TABLE orders (

order_id NUMBER(12),

order_date DATE,

customer_id NUMBER(6),

order_status NUMBER(2),

order_total NUMBER(8,2),

CONSTRAINT orders_pk PRIMARY KEY(order_id)

)

PARTITION BY RANGE(order_date) (

PARTITION Q1_2024 VALUES LESS THAN (TO_DATE('01-JAN-2024','DD-MON-YYYY')),

PARTITION Q2_2024 VALUES LESS THAN (TO_DATE('01-FEB-2024','DD-MON-YYYY')),

PARTITION Q3_2024 VALUES LESS THAN (TO_DATE('01-MAR-2024','DD-MON-YYYY')),

PARTITION Q4_2024 VALUES LESS THAN (TO_DATE('01-APR-2024','DD-MON-YYYY'))

);

CREATE TABLE order_items (

order_id NUMBER(12) NOT NULL,

line_item_id NUMBER(3) NOT NULL,

product_id NUMBER(6) NOT NULL,

unit_price NUMBER(8,2),

quantity NUMBER(8),

CONSTRAINT order_items_fk FOREIGN KEY(order_id) REFERENCES orders(order_id)

)

PARTITION BY REFERENCE(order_items_fk);

```

在这个示例中，ORDERS 表包含了一个 ORDER_DATE 列，用于记录订单的下单日期。订单的明细项目也可能存储在 ORDERS_ITEMS 表中，但从规范化角度看，在明细级别上直接存储 ORDER_DATE 是不合适的。然而，如果 ORDER_DATE 列不在 ORDERS_ITEMS 表中，如何能根据订单日期对 ORDERS_ITEMS 表进行分区呢？这就是参考分区发挥作用的地方。

举例来说，此时 ORDERS 表是基于 ORDER_DATE 列按月范围进行分区的。而 ORDERS_ITEMS 表虽然没有 DATE 类型的列，但它通过 ORDER_NUMBER 列与 ORDERS 表建立了外键关联。

通过使用参考分区的方法，即使 ORDER_DATE 列不在 ORDERS_ITEMS 表中，ORDER_ITEMS 表也能依据与 ORDERS 表相同的列（即 ORDER_DATE 列）进行分区。这样一来，两个表就会按照相同的分区规则同步地进行分区，确保数据管理和查询性能的一致性。

####PostgreSQL 对于分区引用的解决方案

参考分区功能在 PostgreSQL 中并未实现。针对这一情况，对于 PostgreSQL 数据库系统，有以下3种替代方案：

1.不为子表进行分区：

方法描述：直接不对与父表相关的子表执行任何分区操作。

优点：实施简单，无需额外配置和维护分区逻辑。

缺点：无法利用分区带来的查询性能优化和数据管理便捷性；当子表数据量增长时，可能会导致整体查询效率下降，以及备份、恢复和维护任务更为复杂。

2.复制父表的分区键列到子表并应用相同分区策略：

方法描述：将父表中的用于分区的列复制到子表中，并根据该复制的列对子表应用相同的分区方法（例如范围分区或列表分区）。

优点：能够间接地基于业务逻辑进行子表分区，有助于提升查询速度和存储管理效率。

缺点：可能增加数据冗余，需要额外的存储空间和维护成本，并确保两个表间的数据一致性；此外，如果原始设计中并不希望子表包含此列，则可能导致数据库设计偏离规范化原则。

3.基于子表中外键列进行哈希分区：

方法描述：即使子表没有可以直接用来作为范围或列表分区依据的列，也可以选择在子表中存放外键列的基础上对其进行哈希分区。

优点：通过哈希算法可以均匀分布数据，提高并发读写性能和减少热点问题。

缺点：哈希分区不能支持按照特定顺序或范围查询，因为哈希值是无序的；同时，调整分区数或者重新组织数据时可能较为复杂，且很难预测数据在物理存储上的具体分布。

接下来我们将逐一讨论这三种方法的具体应用场景及其优劣之处。

###1：不进行分区

当使用 Ora2Pg 从 Oracle 迁移到 PostgreSQL 时，Ora2Pg 在表和分区导出过程中的默认行为是：不对子表进行分区处理。这可以视为一种策略，但并非总是推荐的做法。

在使用 Ora2Pg 进行迁移时，配置文件中的设置项必须为 "PARTITION_BY_REFERENCE none"。

这样得到的结果代码如下所示：

```

CREATE TABLE orders (

order_id bigint NOT NULL,

order_date timestamp(0),

customer_id integer,

order_status smallint,

order_total double precision

) PARTITION BY RANGE (order_date) ;

ALTER TABLE orders ADD PRIMARY KEY (order_id,order_date);

CREATE TABLE order_items (

order_id bigint NOT NULL,

line_item_id smallint NOT NULL,

product_id integer NOT NULL,

unit_price double precision,

quantity integer

);

ALTER TABLE order_items ADD CONSTRAINT order_items_fk FOREIGN KEY (order_id) REFERENCES orders(order_id);

```

这种方法的问题在于，当 ORDER_ITEMS 表中的记录数量非常高时，会损失性能优势。在这种情况下，我们将无法利用 PostgreSQL 分区的优点，如简化归档和保留策略，以及其他与分区相关的性能优化。这意味着在处理大量数据时，不进行分区可能导致查询速度降低、存储管理和维护效率不高，同时难以实现基于时间或其他业务需求的高效数据管理。

####方法2：将列复制到待分区的表中

这种解决方案是将被引用表中的列复制到需要通过引用进行分区的子表中。这样，该复制列可以作为分区键，并采用与被引用表相同的分区类型来进行分区。

对于父表（即被引用表）而言，相应的生成代码如下所示：

```

CREATE TABLE orders (

order_id bigint NOT NULL,

order_date timestamp(0),

customer_id integer,

order_status smallint,

order_total double precision

) PARTITION BY RANGE (order_date) ;

ALTER TABLE orders ADD PRIMARY KEY (order_id,order_date);

CREATE TABLE order_items (

order_id bigint NOT NULL,

line_item_id smallint NOT NULL,

product_id integer NOT NULL,

unit_price double precision,

quantity integer,

order_date timestamp(0) NOT NULL -- *** column duplicated ***

) PARTITION BY RANGE (order_date) ;

ALTER TABLE order_items ADD CONSTRAINT order_items_fk FOREIGN KEY (order_id, order_date) REFERENCES orders(order_id, order_date);

、、、

现在，针对我们示例中考虑的父表来创建分区，可以使用以下语句：

```

CREATE TABLE orders_part1 PARTITION OF orders

FOR VALUES FROM (MINVALUE) TO (' 2024-03-01 00:00:00');

CREATE TABLE orders_part2 PARTITION OF orders

FOR VALUES FROM (' 2024-03-01 00:00:00') TO (' 2024-04-01 00:00:00');

CREATE TABLE orders_part3 PARTITION OF orders

FOR VALUES FROM (' 2024-04-01 00:00:00') TO (' 2024-05-01 00:00:00');

CREATE TABLE orders_part4 PARTITION OF orders

FOR VALUES FROM (' 2024-05-01 00:00:00') TO (' 2024-06-01 00:00:00');

CREATE TABLE order_items_part1 PARTITION OF order_items

FOR VALUES FROM (MINVALUE) TO (' 2024-03-01 00:00:00');

CREATE TABLE order_items_part2 PARTITION OF order_items

FOR VALUES FROM (' 2024-03-01 00:00:00') TO (' 2024-04-01 00:00:00');

CREATE TABLE order_items_part3 PARTITION OF order_items

FOR VALUES FROM (' 2024-04-01 00:00:00') TO (' 2024-05-01 00:00:00');

CREATE TABLE order_items_part4 PARTITION OF order_items

FOR VALUES FROM (' 2024-05-01 00:00:00') TO (' 2024-06-01 00:00:00');

```

这种方法可以最接近地模拟在Oracle中进行的分区操作，并且对于性能来说最为高效。然而，由于分区键的重复，将占用更多的磁盘空间。需要注意的是，在被引用表上添加或删除任何“分区”时，都必须相应地在已分区的表上执行相同操作，目前尚无自动化工具来实现这一同步。

此外，为了确保子表（ORDER_ITEMS）能够正确使用分区键，还需要在查询中应用一个过滤条件，即 orders.order_date = order_items.order_date。如果不这样做，ORDER_ITEMS 表的所有分区都将受到影响，查询性能可能会变得很差。因此，需要修改查询语句以确保只触及到适当的分区。

```

pg=# EXPLAIN (COSTS off) SELECT a.*,b.* FROM orders a

JOIN order_items b

ON (a.order_id = b.order_id AND a.order_date = b.order_date)

WHERE a.order_date = '2024-04-15 00:00:00';

QUERY PLAN

---------------------------------------------------------------------------------------------------

Hash Join

Hash Cond: (b.order_id = a.order_id)

-> Seq Scan on order_items_part2 b

Filter: (order_date = '2024-04-15 00:00:00'::timestamp without time zone)

-> Hash

-> Bitmap Heap Scan on orders_part2 a

Recheck Cond: (order_date = '2024-04-15 00:00:00'::timestamp without time zone)

-> Bitmap Index Scan on orders_part2_pkey

Index Cond: (order_date = '2024-04-15 00:00:00'::timestamp without time zone)

(9 lines)

```

在使用 Ora2Pg 进行从 Oracle 数据库迁移到 PostgreSQL 的过程中，若要让 Ora2Pg 自动应用上述复制列进行分区的解决方案，配置文件中的设置项必须为 "PARTITION_BY_REFERENCE duplicate"。这意味着 Ora2Pg 将自动识别并复制父表的分区键列到子表中，并基于此创建与父表一致的分区策略。不过请注意，Ora2Pg 是否实际支持这一功能以及如何具体实现，需要参照 Ora2Pg 当前版本的官方文档和功能说明。

####3、哈希分区

通过在表上创建哈希类型的分区，将记录分布到一定数量的分区中，从而减轻第一种解决方案可能带来的性能损失影响。

以上述 ORDER_ITEMS 表为例，可以使用以下哈希分区语法来创建子表

```

CREATE TABLE order_items (

order_id bigint NOT NULL,

line_item_id smallint NOT NULL,

product_id integer NOT NULL,

unit_price double precision,

quantity integer

) PARTITION BY HASH (order_id) ;

```

可以使用以下语法为这个子表创建分区

```

CREATE TABLE order_items_part1 PARTITION OF order_items

FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE order_items_part2 PARTITION OF order_items

FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE order_items_part3 PARTITION OF order_items

FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE order_items_part4 PARTITION OF order_items

FOR VALUES WITH (MODULUS 4, REMAINDER 3);

```

这种方法不太实用，原因在于：

我们失去了外键约束，因为子表中没有 order_date 列，而外键必须引用父表中的两列（例如 order_id 和 order_date）。对于分区表来说，其主键通常是由原始主键和分区键组合而成的。

在过滤条件中，我们也需要使用子表的哈希分区键，这增加了查询时的复杂性。

增加哈希分区的数量会导致数据分布更加分散。随着哈希分区数量的增长，虽然可以更均匀地分配数据，但可能会使得单个查询无法高效利用索引，且管理大量分区也会增加维护成本和复杂度。此外，由于哈希分区不支持基于范围的查询优化，查询效率可能受到一定影响。

```

pg=# EXPLAIN (COSTS off) SELECT a.*,b.*

FROM orders a

JOIN order_items b

ON (a.order_id = b.order_id)

WHERE a.order_date = '2024-04-15 00:00:00' AND a.order_id=150;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------

Nested Loop

-> Index Scan using orders_part2_pkey on orders_part2 a

Index Cond: ((order_id = 150) AND (order_date = '2024-04-15 00:00:00'::timestamp without time zone))

-> Seq Scan on order_items_part3 b

Filter: (order_id = 150)

(5 lines)

```

####结论

通过使用Ora2Pg v15.0版本，可以根据上述讨论的配置设置选择相应的解决方案，从而节省在使用Oracle分区引用进行迁移时所花费的时间。请记住，在ora2pg.conf文件中使用PARTITION_BY_REFERENCE配置。

ITPub博客

Oracle 到 PostgreSQL参考分区实现

最新文章