OceanBase 数据库 Oracle 模式分区表介绍

本文介绍 OceanBase 数据库 Oracle 模式中分区表的使用方法及注意事项。

有关 OceanBase 数据库中分区表的基本概念与优化方法，请参见 OceanBase 数据库文档的《应用开发》章节。

OceanBase 数据库支持的分区功能

• 按照数据分区的规则，可以将分区分为以下三类：

  • Hash 分区：适合随机分布的数据，Hash算法会将分区列的数据进行重分布使得相近的值被打散到不同分区，如销售记录表以商品编号进行 Hash 分区。

    obclient> CREATE TABLE t1 (c1 int, c2 int) PARTITION BY HASH(c1) PARTITIONS 5;
    

  • Range 分区：Range 分区适合处理相似的、与时间有关的数据，或者需要定期导入新数据删除历史数据的场景。

    obclient> CREATE TABLE t1 (c1 int, c2 int) PARTITION BY RANGE(c1)(
    PARTITION p0 VALUES LESS THAN(100), 
    PARTITION p1 VALUES LESS THAN(500), 
    PARTITION p2 VALUES LESS THAN(MAXVALUE));
    

  • List 分区：List 分区适合分区值明确的情形，如顾客信息表中以区域字段作为 List 分区列。

    obclient> CREATE TABLE t3 (c1 number ,c2 int) PARTITION BY list(c2)(
    PARTITION P01 VALUES (1),
    PARTITION P02 VALUES (2));
    

  目前，OceanBase 数据库暂不支持 Interval 分区。

• 按照分区划分的维度，可以分为如下两类:

  • 一级分区

  • 二级分区

    OceanBase 数据库支持以下二级分区组合：

    • Hash + Range 分区

    • Range + Hash 分区

    • List + Range 分区

    • Range + List 分区

    • Hash + List 分区

    • List+Hash 分区

    • Hash +Hash分区

    • Range +Range 分区

    • List + List 分区

  说明

  OceanBase 数据库从 V2.2.7x 版本开始支持 Hash +Hash 分区、Range +Range 分区与 List + List 分区。

• 支持模板化的和非模板化的二级分区

  --创建非模板化的二级分区
  obclient> CREATE TABLE t1 (c1 int, c2 int, c3 int) PARTITION BY RANGE(c1)
  SUBPARTITION BY RANGE(c2)(
    PARTITION p0 VALUES LESS THAN (100)(
      SUBPARTITION p0_r1 VALUES LESS THAN (100),
      SUBPARTITION p0_r2 VALUES LESS THAN (200),
      SUBPARTITION p0_r3 VALUES LESS THAN (300)),
    PARTITION p1 VALUES LESS THAN (200)(
      SUBPARTITION p1_r1 VALUES LESS THAN (100),
      SUBPARTITION p1_r2 VALUES LESS THAN (200),
      SUBPARTITION p1_r3 VALUES LESS THAN (300)),
    PARTITION p2 VALUES LESS THAN (300)(
      SUBPARTITION p2_r1 VALUES LESS THAN (100),
      SUBPARTITION p2_r2 VALUES LESS THAN (200),
      SUBPARTITION p2_r3 VALUES LESS THAN (300)));
  --创建模板化的二级分区
  obclient> CREATE TABLE t1 (c1 number, c2 number, c3 varchar2(10)) PARTITION BY RANGE(c1)
  SUBPARTITION BY LIST(c2)
  SUBPARTITION TEMPLATE(
    SUBPARTITION sp0 VALUES (1, 2, 3),
    SUBPARTITION sp1 VALUES (4, 5, 6))(
    PARTITION p0 VALUES less than (100),
    PARTITION p1 VALUES less than (200));
  

  说明

  对于 OceanBase 数据库 V2.2.7x 之前的版本，仅支持模板化二级分区；OceanBase 数据库 V2.2.7x 及之后版本同时支持模板化和非模板化二级分区。

支持的查询优化方法

OceanBase 数据库支持分区裁剪、分区间并行、使用 RANGE 与 RANGE COLUMN 分区、Partition-Wise Join 等优化方法、创建局部索引或全局索引。

• 分区裁剪 ：优化器根据用户表的分区信息和 SQL 中给定的条件，抽取出相关的分区信息。可以通过 EXPLAIN 语句查看分区裁剪的结果。

  obclient> CREATE TABLE t1 (c1 int , c2 int) PARTITION BY HASH(c1) 
  SUBPARTITION BY range(c2) 
  SUBPARTITION TEMPLATE (
    SUBPARTITION sp0 VALUES LESS THAN(100), 
    SUBPARTITION sp1 VALUES LESS THAN(200)
  ) partitions 5
  
  obclient> EXPLAIN SELECT * FROM t1 WHERE c1 = 1 \G
  *************************** 1. row ***************************
  Query Plan: ===================================
  |ID|OPERATOR  |NAME|EST. ROWS|COST|
  -----------------------------------
  |0 |TABLE SCAN|t1  |1        |1303|
  ===================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([t1.c1], [t1.c2]), filter([t1.c1 = 1]),
        access([t1.c1], [t1.c2]), partitions(p1)
  

• 分区间并行： 需要使用 SQL Hint /*+parallel(3) */。并且在 OBServer 上修改容量规划系统变量 parallel_max_servers 与 parallel_servers_target。

  有关这两个变量的详细信息，参见《OceanBase 数据库 参考指南》中的 系统配置项 章节。

• 使用 Range 与 Range Column 分区 ：消除查询计划中的排序过程。

• Partition-Wise Join ：当需要连接的表是按照连接条件进行分区的时候，连接只需对连接表对应分区进行连接操作，能极大提高连接的性能。可以通过 EXPLAIN 语句查看 Partition-Wise Join 的结果。

  obclient> CREATE TABLE t1 (c1 int, c2 int) PARTITION BY HASH(c1) PARTITIONS 5;
  obclient> CREATE TABLE t2 (c1 int, c2 int) PARTITION BY HASH(c1) PARTITIONS 5;
  
  obclient> EXPLAIN SELECT * FROM t1 JOIN t2 USING (c1) \G
  *************************** 1. row ***************************
  Query Plan: =============================================
  |ID|OPERATOR           |NAME|EST. ROWS|COST |
  ---------------------------------------------
  |0 |EXCHANGE IN DISTR  |    |49500    |52169|
  |1 | EXCHANGE OUT DISTR|    |49500    |42798|
  |2 |  HASH JOIN        |    |49500    |42798|
  |3 |   TABLE SCAN      |t1  |5000     |499  |
  |4 |   TABLE SCAN      |t2  |5000     |499  |
  =============================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([t1.c1], [t1.c2], [t2.c2]), filter(nil)
    1 - output([t1.c1], [t1.c2], [t2.c2]), filter(nil)
    2 - output([t1.c1], [t1.c2], [t2.c2]), filter(nil),
        equal_conds([t1.c1 = t2.c1]), other_conds(nil)
    3 - output([t1.c1], [t1.c2]), filter(nil),
        access([t1.c1], [t1.c2]), partitions(p[0-4])
    4 - output([t2.c1], [t2.c2]), filter(nil),
        access([t2.c1], [t2.c2]), partitions(p[0-4])
  

• 局部索引与全局索引

  • 局部索引： 局部索引同样支持分区裁剪。合理的指定建表的分区键，使每个局部索引的查询条件都能够覆盖住分区键能够很大程度上的提升索引检索效率。

  • 全局索引 ：全局索引的分区键一定是索引键本身，因此在使用全局索引的过程中就会指定索引分区键的查询条件，我们可以针对索引的分区规则进行分区裁剪，在查询到索引键值后可以利用索引表中存储的主键信息计算出主表的分区位置，进而对主表也能进行快速的分区定位，避免扫描主表的所有分区，因此对于无法指定主表分区键的查询而言，全局索引在一定条件下能够加速查询的检索效率。

  同局部索引相比，由于全局索引有独立的分区规则，因此索引表中一个分区的索引值可能对应着主表的多个分区内的数据，由于索引的分区规则和主表的分区规则不一定相同，因此在分布式环境中，索引数据和主表数据存储的位置也无法保证始终在一起，不可避免的会引入读写的 RPC 代价和分布式事务的代价。若要保证物理位置相同，可以将全局索引和主表指定在一个表组中，实际上等同于一个局部索引，因此分区表应当尽可能的避免使用全局索引。

分区数建议

• 分区表的分区数目必须大于集群的节点总数。

• 在确定分区数量时应考虑以下因素：

  • 单个节点的磁盘、CPU、内存等物理资源容量和请求量是否满足需求。

  • 对于 Oracle 租户，建表语句指定创建的分区数量必须小于等于 65536。

  • 整个 OceanBase 集群范围内，每台物理机上的 分区总数 必须小于等于 50000。