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.
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 bigint NOT NULL PRIMARY KEY
, name varchar(50) NOT NULL UNIQUE
, value number NOT NULL
, gmt_create timestamp DEFAULT current_timestamp NOT NULL
, gmt_modified timestamp DEFAULT current_timestamp NOT NULL
) PARTITION BY HASH(id) PARTITIONS 16;
Query OK, 0 rows affected (0.16 sec)
obclient> CREATE TABLE account2(
id bigint NOT NULL PRIMARY KEY
, name varchar(50) NOT NULL
, value number NOT NULL
, gmt_create timestamp DEFAULT current_timestamp NOT NULL
, gmt_modified timestamp DEFAULT current_timestamp NOT NULL
) PARTITION BY HASH(id) PARTITIONS 16;
Query OK, 0 rows affected (0.11 sec)
obclient> CREATE UNIQUE INDEX account2_uk ON account2(name, id) LOCAL ;
Query OK, 0 rows affected (0.73 sec)
obclient> show indexes from account;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| account | 0 | PRIMARY | 1 | id | A | NULL | NULL | NULL | | BTREE | available | | YES |
| account | 0 | name | 1 | name | A | NULL | NULL | NULL | | BTREE | available | | YES |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
2 rows in set (0.01 sec)
obclient> show indexes from account2;
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| account2 | 0 | PRIMARY | 1 | id | A | NULL | NULL | NULL | | BTREE | available | | YES |
| account2 | 0 | account2_uk | 1 | name | A | NULL | NULL | NULL | | BTREE | available | | YES |
| account2 | 0 | account2_uk | 2 | id | A | NULL | NULL | NULL | | BTREE | available | | YES |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
3 rows in set (0.00 sec)
obclient> SELECT * FROM information_schema.`TABLE_CONSTRAINTS` WHERE table_schema='TPCCDB' AND table_name LIKE 'ACCOUNT%';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def | tpccdb | PRIMARY | tpccdb | account | PRIMARY KEY |
| def | tpccdb | name | tpccdb | account | UNIQUE |
| def | tpccdb | PRIMARY | tpccdb | account2 | PRIMARY KEY |
| def | tpccdb | account2_uk | tpccdb | account2 | UNIQUE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
4 rows in set (0.02 sec)