通常当表的数据量非常大，以致于可能使数据库空间紧张，或者由于表非常大导致相关 SQL 查询性能变慢时，可以考虑使用分区表。

唯一索引和拆分键

使用分区表时要选择合适的拆分键以及拆分策略。如果是日志类型的大表，根据时间类型的列做 RANGE 分区是最合适的。如果是并发访问非常高的表，结合业务特点选择能满足绝大部分核心业务查询的列作为拆分键是最合适的。无论选哪个列做为分区键，都不大可能满足所有的查询性能。

分区表中的全局唯一性需求可以通过主键约束和唯一约束实现。OceanBase 数据库的分区表的主键约束和唯一键约束必须包含拆分键。唯一约束也是一个全局索引。全局唯一的需求也可以通过本地唯一索引实现，只需要在唯一索引里包含拆分键。

示例：创建有唯一性需求的分区表

obclient> CREATE TABLE account(
    id number NOT NULL PRIMARY KEY
    , name varchar2(50) NOT NULL UNIQUE 
    , value number NOT NULL
    , gmt_create date DEFAULT sysdate NOT NULL 
    , gmt_modified date DEFAULT sysdate NOT NULL  
) PARTITION BY HASH(id) PARTITIONS 16;
Query OK, 0 rows affected (0.22 sec)
obclient> CREATE TABLE account2(
    id number NOT NULL PRIMARY KEY
    , name varchar2(50) NOT NULL  
    , value number NOT NULL
    , gmt_create date DEFAULT sysdate NOT NULL 
    , gmt_modified date DEFAULT sysdate NOT NULL  
) PARTITION BY HASH(id) PARTITIONS 16;
Query OK, 0 rows affected (0.14 sec)
obclient> CREATE UNIQUE INDEX account2_uk ON account2(name, id) LOCAL ;
Query OK, 0 rows affected (0.75 sec)
obclient> SELECT table_Name,index_name,uniqueness,partitioned FROM user_Indexes WHERE table_name LIKE 'ACCOUNT%';
+------------+-----------------------------------+------------+-------------+
| TABLE_NAME | INDEX_NAME                        | UNIQUENESS | PARTITIONED |
+------------+-----------------------------------+------------+-------------+
| ACCOUNT    | ACCOUNT_OBPK_1585823071504331     | UNIQUE     | NO          |
| ACCOUNT    | ACCOUNT_OBUNIQUE_1585823071505517 | UNIQUE     | NO          |
| ACCOUNT2   | ACCOUNT2_OBPK_1585823071631837    | UNIQUE     | NO          |
| ACCOUNT2   | ACCOUNT2_UK                       | UNIQUE     | YES         |
+------------+-----------------------------------+------------+-------------+
4 rows in set (0.03 sec)
obclient> SELECT table_name, constraint_name, constraint_type, status, index_name FROM user_constraints t WHERE table_name LIKE 'ACCOUNT%'\G
*************************** 1. row ***************************
     TABLE_NAME: ACCOUNT
CONSTRAINT_NAME: ACCOUNT_OBUNIQUE_1585385880987688
CONSTRAINT_TYPE: U
         STATUS: NULL
     INDEX_NAME: ACCOUNT_OBUNIQUE_1585385880987688
*************************** 2. row ***************************
     TABLE_NAME: ACCOUNT2
CONSTRAINT_NAME: ACCOUNT2_UK
CONSTRAINT_TYPE: U
         STATUS: NULL
     INDEX_NAME: ACCOUNT2_UK
*************************** 3. row ***************************
     TABLE_NAME: ACCOUNT
CONSTRAINT_NAME: ACCOUNT_OBPK_1585385880986752
CONSTRAINT_TYPE: P
         STATUS: ENABLED
     INDEX_NAME: ACCOUNT_OBPK_1585385880986752
*************************** 4. row ***************************
     TABLE_NAME: ACCOUNT2
CONSTRAINT_NAME: ACCOUNT2_OBPK_1585386277230890
CONSTRAINT_TYPE: P
         STATUS: ENABLED
     INDEX_NAME: ACCOUNT2_OBPK_1585386277230890
4 rows in set (0.01 sec)

更新分区键

在针对分区表做 UPDATE 时，如果更新了分区键，理论上记录有可能需要从一个分区移动到另外一个分区。如果系统检测到 UPDATE 操作会导致分区移动，则会禁止这种更新。

obclient> create table t_part(id number not null , c1 varchar2(10) not null, c2 varchar2(100), primary key(id,c1)) partition by hash(c1) partitions 8;
Query OK, 0 rows affected (0.29 sec)
obclient> insert into t_part(id,c1,c2) values(1,'A','aaaaaaaa'),(2,'B','bbbbbbbbb'),(3,'C','ccccccccc'),(4,'D','dddddddd');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0
obclient> select * from t_part;
+----+----+-----------+
| ID | C1 | C2        |
+----+----+-----------+
|  3 | C  | ccccccccc |
|  2 | B  | bbbbbbbbb |
|  4 | D  | dddddddd  |
|  1 | A  | aaaaaaaa  |
+----+----+-----------+
4 rows in set (0.05 sec)
obclient> update t_part set c1='CC' where c1='C*';
ORA-14402: updating partition key column would cause a partition change

如果分区键的值没有变化，建议不要更新它。如果分区键的值有变化，则需要开启表的如下属性：

obclient> alter table t_part enable row movement;
Query OK, 0 rows affected (0.02 sec)
obclient> update t_part set c1='CC' where c1='C';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0