将 Oracle 全局唯一索引迁移到分区表中的 Amazon RDS for PostgreSQL
从Oracle迁移分区表中的全局唯一索引到Amazon RDS和Amazon Aurora PostgreSQL
关键要点
在将数据从Oracle数据库迁移至Amazon Aurora PostgreSQL兼容版本或Amazon RDS for PostgreSQL时，您可能需要处理分区表，并确保各个分区之间的全局唯一性。Oracle支持全球索引，而PostgreSQL的分区策略则有所不同。本文将探讨在迁移过程中如何实现全局唯一性，同时提供几种解决方案供参考。
概述
在这篇文章中，我们将以Amazon RDS for Oracle数据库作为源，Amazon Aurora PostgreSQL兼容数据库作为目标进行示例。我们将定义Oracle的分区表以及全局唯一索引，并探讨在PostgreSQL环境中实现全局唯一性的逐步方法。
高层次的解决步骤如下：
将表结构部署到Oracle RDS数据库实例中。使用AWS Schema Conversion Tool 将表结构转换为Amazon Aurora的等效结构。将转换后的表结构部署到目标Amazon Aurora数据库中。选择本文中讨论的可能解决方案之一，按照步骤实现全局唯一性：通过定义使用唯一索引组合列的分区键来强制跨分区的唯一性。通过重构基本分区表来强制唯一性。通过创建一个扁平表非分区表来强制唯一性。下图展示了迁移架构。
该架构包含以下组成部分：
源Oracle RDS数据库实例AWS SCT用于将Oracle函数转换为PostgreSQL等效格式目标Amazon Aurora PostgreSQL兼容数据库前提条件
要实施此解决方案，您需要在VPC中拥有一个Oracle RDS数据库实例和版本11或更高的Amazon Aurora PostgreSQL兼容数据库。本文使用Oracle数据库19c19000和Aurora上的PostgreSQL数据库148进行演示。
在Amazon RDS for Oracle中的模式表结构
为我们的业务案例，客户在体育和娱乐行业运营。他们管理大量体育赛事门票，并收集详细的门票数据以进行分析和商业目的。其ticketpurchasehist表已显著增长，积累大量历史数据。该表按transactiondate列进行范围分区，为特定时间段创建分区。全局分区索引创建在ticketpurchasehist表上，利用分区结构使跨分区数据的访问高效，优化了涉及日期范围的查询。
在Amazon RDS for Oracle中的模式表结构如下：
TICKETPURCHASEHIST 表
sqlCREATE TABLE ticketpurchasehist ( sportingeventticketid NUMBER NOT NULL purchasedbyid NUMBER NOT NULL transferredfromid NUMBER NOT NULL locationid NUMBER NOT NULL purchaseprice NUMBER NOT NULL transactiondate DATE NOT NULL CONSTRAINT ticketpurchasehistpk PRIMARY KEY (sportingeventticketid) CONSTRAINT ticketpurchasehistuk UNIQUE (purchasedbyid transferredfromid) CONSTRAINT ticketpurchasehistfk1 FOREIGN KEY (locationid) REFERENCES location(locationid) CONSTRAINT ticketpurchasehistfk2 FOREIGN KEY (transferredfromid) REFERENCES person(id) ) PARTITION BY RANGE(transactiondate) ( PARTITION tph012020 VALUES LESS THAN (01FEB2020) PARTITION tph022020 VALUES LESS THAN (01MAR2020) PARTITION tph032020 VALUES LESS THAN (01APR2020))
LOCATION表
sqlCREATE TABLE location ( locationid NUMBER NOT NULL locationname VARCHAR2(100) NOT NULL CONSTRAINT locationpk PRIMARY KEY (locationid))
PERSON表
sqlCREATE TABLE person ( id NUMBER NOT NULL name VARCHAR2(100) NOT NULL CONSTRAINT personpk PRIMARY KEY (id))
EVENTHIST表
sqlCREATE TABLE eventhist ( eventid NUMBER NOT NULL eventticketid NUMBER NOT NULL CONSTRAINT eventhistpk PRIMARY KEY (eventid) CONSTRAINT eventhistfk FOREIGN KEY (eventticketid) REFERENCES ticketpurchasehist (sportingeventticketid))
在使用AWS SCT转换这些表并将其部署到Amazon Aurora时，我们将遇到以下报错，因为它期望唯一约束列是分区列的一部分：
ERROR unique constraint on partitioned table must include all partitioning columnsDETAIL PRIMARY KEY constraint on table ticketpurchasehist lacks column transactiondate which is part of the partition keySQL state 0A000
在PostgreSQL中，要为分区表创建唯一或主键约束，约束的列必须包括所有分区键列。这一限制存在的原因是构成约束的各个索引只能直接在各自分区内强制执行唯一性。因此，分区结构本身必须确保不同分区中没有重复项。
要实现从Oracle到PostgreSQL的这些表结构迁移，我们讨论了一些强制唯一性并克服PostgreSQL限制的方法。
方法一：通过定义唯一索引复合列的分区键跨分区强制唯一性
此方法适用于希望保持分区数据管理和查询性能的好处，但需要跨分区强制唯一性的情况。
考虑使用此方法当您的分区表的分区键与主键约束列不一致时。您可以在所需列上创建一个唯一复合索引，并据此列对表进行分区。
这种方法的好处在于它能让您保持分区的优势，同时确保全局唯一性。
Aurora PostgreSQL中的模式表结构
模式表的结构如下：
TICKETPURCHASEHIST
sqlCREATE TABLE ticketpurchasehist ( sportingeventticketid BIGINT NOT NULL purchasedbyid BIGINT NOT NULL transferredfromid BIGINT NOT NULL locationid BIGINT NOT NULL purchaseprice BIGINT NOT NULL transactiondate DATE NOT NULL CONSTRAINT ticketpurchasehistuk UNIQUE (purchasedbyid transferredfromid) CONSTRAINT ticketpurchasehistfk1 FOREIGN KEY (locationid) REFERENCES location(locationid) CONSTRAINT ticketpurchasehistfk2 FOREIGN KEY (transferredfromid) REFERENCES person(id) ) PARTITION BY HASH (purchasedbyid transferredfromid)
LOCATION
sqlCREATE TABLE location ( locationid BIGINT NOT NULL locationname CHARACTER VARYING(100) NOT NULL CONSTRAINT locationpk PRIMARY KEY (locationid))
PERSON
sqlCREATE TABLE person ( id BIGINT NOT NULL name CHARACTER VARYING(100) NOT NULL CONSTRAINT personpk PRIMARY KEY (id))
EVENTHIST
sqlCREATE TABLE eventhist ( eventid BIGINT NOT NULL eventticketid BIGINT NOT NULL CONSTRAINT eventhistpk PRIMARY KEY (eventid))
样本数据及唯一性验证
由于purchasedbyid和transferredfromid列是分区键的一部分，PostgreSQL会确保这些列在分区之间的唯一性。为验证这一点，插入一些数据：
sqlINSERT INTO person SELECT i namei FROM generateseries(110) i
INSERT INTO location SELECT i locationi FROM generateseries(110) i
INSERT INTO ticketpurchasehistSELECT iiiii20200105DATE i30 FROM generateseries(15) i
SELECT tableoidregclass FROM ticketpurchasehist
再次插入一条相同的purchasedbyid和transferredfromid值的记录应导致SQL错误：
sqlINSERT INTO ticketpurchasehist VALUES (6116620200705DATE)
输出结果如下：
SQL Error [23505] ERROR duplicate key value violates unique constraint ticketpurchasehistp1 purchasedbyidtransferredfromidkeyDetail Key (purchasedbyid transferredfromid)=(1 1) already exists
因此，通过重构表来使用Oracle全局唯一索引列定义分区可确保在Amazon Aurora中跨分区的唯一性。
分区修剪和索引考虑
需要注意的是：
在带有复合列的哈希分区表中，仅在查询时使用所有分区键的列才可能进行分区修剪。只有相等运算符支持分区修剪，因为其他运算符会扫描所有分区。对NULL值没有特别处理。注意事项
在每个分区内，主键仅能维持唯一性。可参考的外键关系需关系每个独立分区的主键，而不能直接和基础分区表的主键关联。方法二：通过重构基础分区表强制跨分区唯一性
此方法适合那些在重新定义表结构和分区策略上较灵活的情况下，让主键列与分区键对齐。
在现有分区和主键设计不理想的情况下，考虑重构表结构与分区策略，以确保主键跨分区强制唯一性。
云梯npv加速器Aurora PostgreSQL中的模式表结构
此方法结构如下：
TICKETPURCHASEHISTPKT
sqlCREATE TABLE ticketpurchasehistpkt ( sportingeventticketid BIGINT NOT NULL locationid BIGINT NOT NULL purchaseprice BIGINT NOT NULL transactiondate DATE NOT NULL CONSTRAINT ticketpurchasehistpktpk PRIMARY KEY (sportingeventticketid) CONSTRAINT ticketpurchasehistpktfk FOREIGN KEY (locationid) REFERENCES location(locationid)) PARTITION BY HASH (sportingeventticketid)
TICKETPURCHASEHISTUKT
sqlCREATE TABLE ticketpurchasehistukt ( sportingeventticketid BIGINT NOT NULL purchasedbyid BIGINT NOT NULL transferredfromid BIGINT NOT NULL transactiondate DATE NOT NULL CONSTRAINT ticketpurchasehistuktuk UNIQUE (purchasedbyid transferredfromid) CONSTRAINT ticketpurchasehistuktpktlink FOREIGN KEY (sportingeventticketid) REFERENCES ticketpurchasehistpkt (sportingeventticketid) ) PARTITION BY HASH (purchasedbyid transferredfromid)
样本数据及唯一性验证
由于TICKETPURCHASEHISTUKT表中的purchasedbyid和transferredfromid列是分区键的一部分，PostgreSQL可以确保这些列在分区之间的唯一性。
方法三：通过创建非分区的扁平表强制唯一性
如果数据量管理得当，且分区并非严格要求，采用扁平表或许是一个较好的选择。
适合于没有明确分区需求时，应考虑创建一个扁平表，以简化数据模型。
Aurora PostgreSQL中的模式表结构
扁平表结构如下：
sqlCREATE TABLE ticketpurchasehist ( sportingeventticketid BIGINT NOT NULL purchasedbyid BIGINT NOT NULL transferredfromid BIGINT NOT NULL locationid BIGINT NOT NULL purchaseprice BIGINT NOT NULL transactiondate DATE NOT NULL CONSTRAINT ticketpurchasehistp1pk PRIMARY KEY (sportingeventticketid) CONSTRAINT ticketpurchasehistuk UNIQUE (purchasedbyid transferredfromid) CONSTRAINT ticketpurchasehistfk1 FOREIGN KEY (locationid) REFERENCES location(locationid) CONSTRAINT ticketpurchasehistfk2 FOREIGN KEY (transferredfromid) REFERENCES person(id) )
注意事项
PostgreSQL数据库的最大允许大小是32TB，但可以提升到128TB。使用maintainworkmem参数建设索引。PostgreSQL使用VACUUM过程释放已删除数据的空间。结论
本文提供了在将Oracle分区表迁移至Amazon RDS for PostgreSQL或Amazon Aurora PostgreSQL兼容版本时实现全局唯一性的多个解决方案。每种方法都有其优缺点，推荐方案取决于现有模式、数据量、查询模式及保持分区对性能及数据重要性的需求。根据您的需求及系统应对限制的能力，可以选择适合的方案以满足迁移需求。