OceaBase 分区表创建技巧

oceabase · 浏览次数 : 10

小编点评

**1. 创建范围分区表** * 创建分区列：`order_date` * 使用`ORDER BY RANGE()`函数进行分区。 * 例如：`PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))` **2. 创建主键** * 如果主键列已存在，创建分区键时需要包括主键列。 * 例如：`PRIMARY KEY(order_id,order_date)` **3. 创建索引** * 在分区表中创建索引，以加快查询。 * 例如：`CREATE INDEX idx_uniq_order_id ON sales_orders(order_id)` **4. 注意** * 在创建分区表时，确保主键列包含所有与分区键相关的列。 * 如果主键列已存在，请在分区键中添加主键列。 * 如果业务逻辑允许，可以在分区键中创建多个索引。

正文

最近遇在干个核心的金融项目，规模很大，客户主要是用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 对得上。