Suggestions on using partitioned tables

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 partitioning key

To use partitioned tables, you need to use the appropriate partitioning 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 partitioning key based on the business characteristics. No column can meet all query requirements.

You can use the PRIMARY KEY and UNIQUE constraints to ensure global uniqueness in a partitioned table. In OceanBase Database, the PRIMARY KEY and UNIQUE constraint of a partitioned table must contain the partitioning 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 partitioning key in the UNIQUE index.

Examples

Create a partitioned table with a UNIQUE constraint.

1. Create a table named account.

   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;
   

2. Create an index named account_uk.

   obclient> CREATE UNIQUE INDEX account_uk ON account(name, id) LOCAL ;
   Query OK, 0 rows affected
   

3. View the constraints on the account table.

   obclient> SELECT table_Name,index_name,uniqueness,partitioned FROM user_Indexes WHERE table_name= 'ACCOUNT';
   +------------+-----------------------------------+------------+-------------+
   | TABLE_NAME | INDEX_NAME                        | UNIQUENESS | PARTITIONED |
   +------------+-----------------------------------+------------+-------------+
   | ACCOUNT    | ACCOUNT_OBPK_1650952912935194     | UNIQUE     | YES         |
   | ACCOUNT    | ACCOUNT_OBUNIQUE_1650952912936028 | UNIQUE     | NO          |
   | ACCOUNT    | ACCOUNT_UK                        | UNIQUE     | YES         |
   +------------+-----------------------------------+------------+-------------+
   3 rows in set
   

4. View the index and constraint on the account table.

   obclient> SELECT table_name, constraint_name, constraint_type, status, index_name FROM user_constraints t WHERE table_name= 'ACCOUNT'\G
   *************************** 1. row ***************************
        TABLE_NAME: ACCOUNT
   CONSTRAINT_NAME: ACCOUNT_OBUNIQUE_1650952912936028
   CONSTRAINT_TYPE: U
            STATUS: ENABLED
        INDEX_NAME: ACCOUNT_OBUNIQUE_1650952912936028
   *************************** 2. row ***************************
        TABLE_NAME: ACCOUNT
   CONSTRAINT_NAME: ACCOUNT_UK
   CONSTRAINT_TYPE: U
            STATUS: ENABLED
        INDEX_NAME: ACCOUNT_UK
   *************************** 3. row ***************************
        TABLE_NAME: ACCOUNT
   CONSTRAINT_NAME: ACCOUNT_OBNOTNULL_1650952912935181
   CONSTRAINT_TYPE: C
            STATUS: ENABLED
        INDEX_NAME: NULL
   *************************** 4. row ***************************
        TABLE_NAME: ACCOUNT
   CONSTRAINT_NAME: ACCOUNT_OBPK_1650952912935194
   CONSTRAINT_TYPE: P
            STATUS: ENABLED
        INDEX_NAME: ACCOUNT_OBPK_1650952912935194
   *************************** 5. row ***************************
        TABLE_NAME: ACCOUNT
   CONSTRAINT_NAME: ACCOUNT_OBNOTNULL_1650952912935211
   CONSTRAINT_TYPE: C
            STATUS: ENABLED
        INDEX_NAME: NULL
   *************************** 6. row ***************************
        TABLE_NAME: ACCOUNT
   CONSTRAINT_NAME: ACCOUNT_OBNOTNULL_1650952912935219
   CONSTRAINT_TYPE: C
            STATUS: ENABLED
        INDEX_NAME: NULL
   *************************** 7. row ***************************
        TABLE_NAME: ACCOUNT
   CONSTRAINT_NAME: ACCOUNT_OBNOTNULL_1650952912935225
   CONSTRAINT_TYPE: C
            STATUS: ENABLED
        INDEX_NAME: NULL
   *************************** 8. row ***************************
        TABLE_NAME: ACCOUNT
   CONSTRAINT_NAME: ACCOUNT_OBNOTNULL_1650952912935421
   CONSTRAINT_TYPE: C
            STATUS: ENABLED
        INDEX_NAME: NULL
   8 rows in set
   

Update the partitioning key

If you update the partitioning 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.

Examples

1. Create a table named t_prat.

   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;
   

2. Insert data into the t_part table.

   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
   Records: 4  Duplicates: 0  Warnings: 0
   

3. View the data in the t_part table.

   obclient> select * from t_part;
   +----+----+-----------+
   | ID | C1 | C2        |
   +----+----+-----------+
   |  4 | D  | dddddddd  |
   |  3 | C  | ccccccccc |
   |  2 | B  | bbbbbbbbb |
   |  1 | A  | aaaaaaaa  |
   +----+----+-----------+
   4 rows in set
   

4. Update the partitioning key c1.

   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 partitioning key if its value is not changed. If the partitioning key value changes, enable the following attributes of the table:

Examples

1. Enable the row movement feature.

   obclient>  alter table t_part enable row movement;
   Query OK, 0 rows affected
   

2. Update the partitioning key c1 again.

   obclient> update t_part set c1='CC' where c1='C';
   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0