Oracle 到 PostgreSQL參考分割槽實現

在遷移資料庫到 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配置。