OceaBase 分割槽表建立技巧

小至尖尖發表於2024-05-28

最近遇在幹個核心的金融專案，規模很大，客戶主要是用oracle資料庫，現在需要適配ob，原來在oracle就是分割槽表的遷來ob以後需要進行改造。

oracle預設使用是堆表（ht），而ob使用的是索引組織表（iot），表原理不一樣所以分割槽表會稍微有點區別。

1、表無主鍵，建立範圍分割槽表

CREATE TABLE sales_orders (        
        order_id INT,
        customer_id INT,
        order_date DATE,
        total_amount DECIMAL(10, 2)
        )
PARTITION BY RANGE(order_date) 
            (  PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
            , PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
            , PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
            , PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
            , PARTITION MMAX VALUES LESS THAN (MAXVALUE));

表在無主鍵情況下建立分割槽表，只需要提前確認好分割槽列，分割槽列的型別，分割槽規則就行。

由於歷史原因，以前很多用堆表的業務都是沒有使用主鍵的，業務設計沒有嚴格使用三正規化，如果此類無主鍵的表遷移到ob資料庫的話，ob也會建議建立上必要的主鍵索引。

如果業務無法找到合適的列建立主鍵索引，上面sql 也可以相容oracle，在ob_oracle 租戶可以直接建立成功。

2、表有主鍵，建立範圍分割槽表

但是表有主鍵的情況下，情況會稍微複雜點，oracle由於是堆表可以按照以下方式建立分割槽表，主鍵和分割槽鍵之間可以沒有關聯

CREATE TABLE sales_orders (        
        order_id INT,            -- 訂單號
        customer_id INT,
        order_date DATE,         -- 訂單日期
        total_amount DECIMAL(10, 2),
        PRIMARY KEY(order_id)
        )
        PARTITION BY RANGE(order_date)   -- 使用訂單日期進行分割槽
            (  PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
            , PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
            , PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
            , PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
            , PARTITION MMAX VALUES LESS THAN (MAXVALUE)
            );

但是在ob裡面，如果需要建立帶有主鍵的分割槽表，分割槽列必須是主鍵列的一部分。

參考官方文件：https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000820771

如果 ob_oracle 建立分割槽表使用單獨的列會報錯：ORA-00600: internal error code, arguments: -5261, A PRIMARY KEY must include all columns in the table's partitioning function

ob_oracle 正確建立分割槽表方式：

CREATE TABLE sales_orders (        
        order_id INT,        -- 訂單號
        customer_id INT,
        order_date DATE,    -- 訂單日期
        total_amount DECIMAL(10, 2),
        PRIMARY KEY(order_id,order_date)   -- 聯合主鍵
        )
        PARTITION BY RANGE(order_date)      -- order_date 是主鍵一部分，使用訂單日期進行分割槽
            (  PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
            , PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
            , PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
            , PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
            , PARTITION MMAX VALUES LESS THAN (MAXVALUE)
            );

但是這樣會有一個問題，就是原來oracle上order_id 是主鍵，現在在ob以後是order_id,order_date成為組合索引。

如果業務程式碼層面沒有做資料校驗的邏輯，會在order_id上產生重複資料，這樣不合符原有的業務邏輯

其實很簡單，只需要對order_id加個唯一索引即可實現在oracle上一樣的邏輯。

CREATE unique index idx_uniq_order_id on sales_orders(order_id);

這樣一來，order_id就不會出現重複資料了，而且ob分割槽表的邏輯也能和oracle 對得上。

相關文章