本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # 將 Oracle ROWID 功能遷移至 AWS 上的 PostgreSQL *Rakesh Raghav 和 Ramesh Pathuri，Amazon Web Services* ## 總結 此模式說明將 Oracle 資料庫中的`ROWID`虛擬資料欄功能遷移至 PostgreSQL (Amazon Relational Database Service RDS) for PostgreSQL、Amazon Aurora PostgreSQL 相容版本或 Amazon Elastic Compute Cloud (Amazon EC2) 中 PostgreSQL 資料庫的選項。 在 Oracle 資料庫中，`ROWID`虛擬資料欄是資料表中資料列的實體地址。即使資料表上沒有主索引鍵，此虛擬資料欄也會用來唯一識別資料列。PostgreSQL 具有稱為 的類似虛擬資料欄`ctid`，但無法用作 `ROWID`。如 [PostgreSQL 文件](https://www.postgresql.org/docs/current/ddl-system-columns.html)所述，如果更新或在每次`VACUUM`程序之後， `ctid`可能會變更。 您可以透過三種方式在 PostgreSQL `ROWID` 中建立虛擬資料欄功能： + 使用主索引鍵欄而非 `ROWID`來識別資料表中的資料列。 + 在 資料表中使用邏輯主要/唯一金鑰 （可能是複合金鑰）。  + 新增具有自動產生值的資料欄，使其成為要模擬的主要/唯一金鑰`ROWID`。 此模式會逐步解說這三個實作，並說明每個選項的優點和缺點。 ## 先決條件和限制 **先決條件** + 作用中的 AWS 帳戶 + 程序語言/PostgreSQL (PL/pgSQL) 編碼專業知識 + 來源 Oracle 資料庫 + Amazon RDS for PostgreSQL 或 Aurora PostgreSQL 相容叢集，或託管 PostgreSQL 資料庫的 EC2 執行個體 **限制** + 此模式提供 `ROWID`功能的解決方法。PostgreSQL 在 `ROWID` Oracle 資料庫中不提供 的同等 。 **產品版本** + PostgreSQL 11.9 或更新版本 ## Architecture **來源技術堆疊** + Oracle Database **目標技術堆疊** + Aurora PostgreSQL 相容、Amazon RDS for PostgreSQL 或具有 PostgreSQL 資料庫的 EC2 執行個體 ![將 Oracle 資料庫轉換為 AWS 上的 PostgreSQL](http://docs.aws.amazon.com/zh_tw/prescriptive-guidance/latest/patterns/images/pattern-img/9a2ce994-4f68-4975-aab2-796cc20a3c82/images/6e7c2ef6-f440-476a-9003-f1f166718e15.png) **實作選項** 根據您的資料表是否具有主索引鍵或唯一索引、邏輯主索引鍵或身分屬性，有三種選項可以解決 PostgreSQL 中缺乏`ROWID`支援的問題。您的選擇取決於您的專案時間表、目前的遷移階段，以及應用程式和資料庫程式碼的相依性。 | | | 選項 | Description | 優點 | 缺點 | | --- |--- |--- |--- | | **主索引鍵或唯一索引** | 如果您的 Oracle 資料表具有主索引鍵，您可以使用此索引鍵的屬性來唯一識別資料列。  | 不依賴專屬資料庫功能。對效能的影響最小，因為主索引鍵欄位會編製索引。 | 需要變更依賴 `ROWID`切換到主索引鍵欄位的應用程式和資料庫程式碼。  | | **邏輯主要/唯一金鑰** | 如果您的 Oracle 資料表具有邏輯主索引鍵，您可以使用此索引鍵的屬性來唯一識別資料列。邏輯主索引鍵由屬性或一組屬性組成，這些屬性可以唯一識別資料列，但不會透過限制在資料庫上強制執行。 | 不依賴專屬資料庫功能。 | 需要變更依賴 `ROWID`切換到主索引鍵欄位的應用程式和資料庫程式碼。如果邏輯主索引鍵的屬性未編製索引，會對效能造成重大影響。不過，您可以新增唯一的索引以防止效能問題。 | | **身分屬性** | 如果您的 Oracle 資料表沒有主索引鍵，您可以將其他欄位建立為 `GENERATED ALWAYS AS IDENTITY`。此屬性會在資料插入資料表時產生唯一值，因此可用於唯一識別資料控制語言 (DML) 操作的資料列。 | 不依賴專屬資料庫功能。PostgreSQL 資料庫會填入 屬性並維護其唯一性。 | 需要變更依賴 `ROWID`切換到身分屬性的應用程式和資料庫程式碼。如果其他欄位未編製索引，則對效能有重大影響。不過，您可以新增索引以防止效能問題。 | ## 工具 + [適用於 PostgreSQL 的 Amazon Relational Database Service (Amazon RDS)](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html) 可協助您在 AWS 雲端中設定、操作和擴展 PostgreSQL 關聯式資料庫。 + [Amazon Aurora PostgreSQL 相容版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html)是完全受管的 ACID 相容關聯式資料庫引擎，可協助您設定、操作和擴展 PostgreSQL 部署。 + [AWS Command Line Interface (AWS CLI)](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html) 是一種開放原始碼工具，可協助您透過命令列 shell 中的命令與 AWS 服務互動。在此模式中，您可以使用 AWS CLI 透過 **pgAdmin** 執行 SQL 命令。 + [pgAdmin](https://www.pgadmin.org/) 是 PostgreSQL 的開放原始碼管理工具。它提供圖形界面，可協助您建立、維護和使用資料庫物件。 + [AWS Schema Conversion Tool (AWS SCT)](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) 會自動將來源資料庫結構描述和大部分自訂程式碼轉換為與目標資料庫相容的格式，以支援異質資料庫遷移。 ## 史詩 ### 識別來源資料表 | 任務 | Description | 所需的技能 | | --- | --- | --- | | 識別使用 `ROWID` 屬性的 Oracle 資料表。 | 使用 AWS Schema Conversion Tool (AWS SCT) 來識別具有 `ROWID`功能的 Oracle 資料表。如需詳細資訊，請參閱 [AWS SCT 文件](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.Oracle.ToPostgreSQL.html#CHAP_Source.Oracle.ToPostgreSQL.ConvertRowID)。
—或—
在 Oracle 中，使用 `DBA_TAB_COLUMNS` 檢視來識別具有 `ROWID` 屬性的資料表。這些欄位可用於存放英數 10 位元組字元。判斷用量，並適時將其轉換為`VARCHAR`欄位。 | DBA 或開發人員 | | 識別參考這些資料表的程式碼。 | 使用 AWS SCT 產生遷移評估報告，以識別受 影響的程序`ROWID`。如需詳細資訊，請參閱 [AWS SCT 文件](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_AssessmentReport.html)。
—或—
在來源 Oracle 資料庫中，使用`dba_source`資料表的文字欄位來識別使用 `ROWID`功能的物件。 | DBA 或開發人員 | ### 判斷主索引鍵用量 | 任務 | Description | 所需的技能 | | --- | --- | --- | | 識別沒有主索引鍵的資料表。 | 在來源 Oracle 資料庫中，使用 `DBA_CONSTRAINTS` 來識別沒有主索引鍵的資料表。此資訊將協助您判斷每個資料表的策略。例如：
select dt.*
from dba_tables dt
where not exists (select 1
                  from all_constraints ct
                  where ct.owner = Dt.owner
                    and ct.table_name = Dt.table_name
                    and ct.constraint_type = 'P'
                  )
and dt.owner = '{schema}'
| DBA 或開發人員 | ### 識別並套用解決方案 | 任務 | Description | 所需的技能 | | --- | --- | --- | | 針對具有已定義或邏輯主索引鍵的資料表套用變更。 | 進行[其他資訊](#migrate-oracle-rowid-functionality-to-postgresql-on-aws-additional)區段中顯示的應用程式和資料庫程式碼變更，以使用唯一的主索引鍵或邏輯主索引鍵來識別資料表中的資料列。 | DBA 或開發人員 | | 將其他欄位新增至沒有已定義或邏輯主索引鍵的資料表。 | 新增 類型的屬性`GENERATED ALWAYS AS IDENTITY`。進行[其他資訊](#migrate-oracle-rowid-functionality-to-postgresql-on-aws-additional)區段中顯示的應用程式和資料庫程式碼變更。 | DBA 或開發人員 | | 視需要新增索引。 | 將索引新增至其他欄位或邏輯主索引鍵，以改善 SQL 效能。 | DBA 或開發人員 | ## 相關資源 + [PostgreSQL CTID](https://www.postgresql.org/docs/current/ddl-system-columns.html) (PostgreSQL 文件） + [產生的資料欄 ](https://www.postgresql.org/docs/current/ddl-generated-columns.html)(PostgreSQL 文件） + [ROWID 虛擬資料欄 ](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWID-Pseudocolumn.html#GUID-F6E0FBD2-983C-495D-9856-5E113A17FAF1)(Oracle 文件） ## 其他資訊 下列各節提供 Oracle 和 PostgreSQL 程式碼範例，以說明這三種方法。 **案例 1：使用主要唯一金鑰** 在下列範例中，您會使用 建立`testrowid_s1`資料表`emp_id`做為主索引鍵。 *Oracle 程式碼：* ``` create table testrowid_s1 (emp_id integer, name varchar2(10), CONSTRAINT testrowid_pk PRIMARY KEY (emp_id)); INSERT INTO testrowid_s1(emp_id,name) values (1,'empname1'); INSERT INTO testrowid_s1(emp_id,name) values (2,'empname2'); INSERT INTO testrowid_s1(emp_id,name) values (3,'empname3'); INSERT INTO testrowid_s1(emp_id,name) values (4,'empname4'); commit; SELECT rowid,emp_id,name FROM testrowid_s1; ROWID EMP_ID NAME ------------------ ---------- ---------- AAAF3pAAAAAAAMOAAA 1 empname1 AAAF3pAAAAAAAMOAAB 2 empname2 AAAF3pAAAAAAAMOAAC 3 empname3 AAAF3pAAAAAAAMOAAD 4 empname4 UPDATE testrowid_s1 SET name = 'Ramesh' WHERE rowid = 'AAAF3pAAAAAAAMOAAB' ; commit; SELECT rowid,emp_id,name FROM testrowid_s1; ROWID EMP_ID NAME ------------------ ---------- ---------- AAAF3pAAAAAAAMOAAA 1 empname1 AAAF3pAAAAAAAMOAAB 2 Ramesh AAAF3pAAAAAAAMOAAC 3 empname3 AAAF3pAAAAAAAMOAAD 4 empname4 ``` *PostgreSQL 程式碼：* ``` CREATE TABLE public.testrowid_s1 ( emp_id integer, name character varying, primary key (emp_id) ); insert into public.testrowid_s1 (emp_id,name) values (1,'empname1'),(2,'empname2'),(3,'empname3'),(4,'empname4'); select emp_id,name from testrowid_s1; emp_id | name --------+---------- 1 | empname1 2 | empname2 3 | empname3 4 | empname4 update testrowid_s1 set name = 'Ramesh' where emp_id = 2 ; select emp_id,name from testrowid_s1; emp_id | name --------+---------- 1 | empname1 3 | empname3 4 | empname4 2 | Ramesh ``` **案例 2：使用邏輯主索引鍵** 在下列範例中，您會使用 建立資料表`testrowid_s2``emp_id`做為邏輯主索引鍵。 *Oracle 程式碼：* ``` create table testrowid_s2 (emp_id integer, name varchar2(10) ); INSERT INTO testrowid_s2(emp_id,name) values (1,'empname1'); INSERT INTO testrowid_s2(emp_id,name) values (2,'empname2'); INSERT INTO testrowid_s2(emp_id,name) values (3,'empname3'); INSERT INTO testrowid_s2(emp_id,name) values (4,'empname4'); commit; SELECT rowid,emp_id,name FROM testrowid_s2; ROWID EMP_ID NAME ------------------ ---------- ---------- AAAF3rAAAAAAAMeAAA 1 empname1 AAAF3rAAAAAAAMeAAB 2 empname2 AAAF3rAAAAAAAMeAAC 3 empname3 AAAF3rAAAAAAAMeAAD 4 empname4 UPDATE testrowid_s2 SET name = 'Ramesh' WHERE rowid = 'AAAF3rAAAAAAAMeAAB' ; commit; SELECT rowid,emp_id,name FROM testrowid_s2; ROWID EMP_ID NAME ------------------ ---------- ---------- AAAF3rAAAAAAAMeAAA 1 empname1 AAAF3rAAAAAAAMeAAB 2 Ramesh AAAF3rAAAAAAAMeAAC 3 empname3 AAAF3rAAAAAAAMeAAD 4 empname4 ``` *PostgreSQL 程式碼：* ``` CREATE TABLE public.testrowid_s2 ( emp_id integer, name character varying ); insert into public.testrowid_s2 (emp_id,name) values (1,'empname1'),(2,'empname2'),(3,'empname3'),(4,'empname4'); select emp_id,name from testrowid_s2; emp_id | name --------+---------- 1 | empname1 2 | empname2 3 | empname3 4 | empname4 update testrowid_s2 set name = 'Ramesh' where emp_id = 2 ; select emp_id,name from testrowid_s2; emp_id | name --------+---------- 1 | empname1 3 | empname3 4 | empname4 2 | Ramesh ``` **案例 3：使用身分屬性** 在下列範例中，您使用身分屬性建立不含主索引鍵的資料表`testrowid_s3`。 *Oracle 程式碼：* ``` create table testrowid_s3 (name varchar2(10)); INSERT INTO testrowid_s3(name) values ('empname1'); INSERT INTO testrowid_s3(name) values ('empname2'); INSERT INTO testrowid_s3(name) values ('empname3'); INSERT INTO testrowid_s3(name) values ('empname4'); commit; SELECT rowid,name FROM testrowid_s3; ROWID NAME ------------------ ---------- AAAF3sAAAAAAAMmAAA empname1 AAAF3sAAAAAAAMmAAB empname2 AAAF3sAAAAAAAMmAAC empname3 AAAF3sAAAAAAAMmAAD empname4 UPDATE testrowid_s3 SET name = 'Ramesh' WHERE rowid = 'AAAF3sAAAAAAAMmAAB' ; commit; SELECT rowid,name FROM testrowid_s3; ROWID NAME ------------------ ---------- AAAF3sAAAAAAAMmAAA empname1 AAAF3sAAAAAAAMmAAB Ramesh AAAF3sAAAAAAAMmAAC empname3 AAAF3sAAAAAAAMmAAD empname4 ``` *PostgreSQL 程式碼：* ``` CREATE TABLE public.testrowid_s3 ( rowid_seq bigint generated always as identity, name character varying ); insert into public.testrowid_s3 (name) values ('empname1'),('empname2'),('empname3'),('empname4'); select rowid_seq,name from testrowid_s3; rowid_seq | name -----------+---------- 1 | empname1 2 | empname2 3 | empname3 4 | empname4 update testrowid_s3 set name = 'Ramesh' where rowid_seq = 2 ; select rowid_seq,name from testrowid_s3; rowid_seq | name -----------+---------- 1 | empname1 3 | empname3 4 | empname4 2 | Ramesh ```