You can use partitioned tables when the amount of data is so large that it uses up the storage space or when the tables are so large that they slow down the performance of SQL queries.
UNIQUE index and partition key
To use partitioned tables, you need to use the appropriate partition key and strategy. For large logs, RANGE partitioning with datetime columns is the best choice. For tables with high concurrency, it is best to choose one or more columns that satisfy most of the core business query requirements as the partition key based on the business characteristics. No column can meet all query requirements.
You can use the PRIMARY KEY and a UNIQUE constraint to ensure global uniqueness in a partitioned table. In OceanBase Database, the PRIMARY KEY and UNIQUE constraint of a partitioned table must contain the partition key. The UNIQUE constraint is also a global index. You can also use a local UNIQUE index to ensure global uniqueness. To do this, you only need to include the partition key in the UNIQUE index.
Example: Creating a partitioned table that has the uniqueness requirement
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 the partition key
If you update the partition key when you update a partitioned table, you may need to move the records from one partition to another. If the system detects that an update would cause a partition change, it stops the 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
We recommend that you do not update the partition key if its value is not changed. If the partition key value changes, enable the following attributes of the table:
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