Create a table and import data

Background information

The table is defined as follows:

Concepts

In OceanBase Database, you can divide the data of a table into separate blocks based on specific rules. Data within the same block is stored together physically. This type of table is called a partitioned table, and each block is a partition. In the MySQL mode of OceanBase Database, a single table can have a maximum of 8,192 partitions.

Hash partitioning

Hash partitioning is suitable for scenarios where you cannot use range or list partitioning. The implementation is simple. It distributes records to different partitions based on the hash value of the partition key. Hash partitioning is a good choice if your data has the following characteristics. For more information, see Partitioning overview.

• You cannot specify a list of values for the partition key.

• The data volume varies greatly across different ranges, and it is difficult to manually balance the data.

• Range partitioning results in severe data skew.

• Performance is critical for features such as parallel Data Manipulation Language (DML), partition pruning, and partition-wise joins.

• The partition key for hash partitioning must be an `INT` or `YEAR` data type and can be an expression.

CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 INT) 
 PARTITION BY KEY() 
 PARTITIONS 5;
Query OK, 0 rows affected

Procedure

1. In the navigation pane on the left, click Database Management.

2. In the upper-right corner, click Log on to Database.

3. In the dialog box that appears, select admin_user and click OK.

4. In the dialog box that appears, enter the password and click OK.

5. After the connection is successful, click information_schema to switch to the tutorial_database.

6. In the SQL window, enter the following SQL statement:

   CREATE TABLE `orders` 
    (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `customer_id` int(11) NOT NULL,
    `order_date` datetime NOT NULL,
    `shipping_date` datetime DEFAULT NULL,
    `status` varchar(10) NOT NULL,
    `subtotal` decimal(10,2) NOT NULL,
    `tax` decimal(10,2) NOT NULL,
    `shipping` decimal(10,2) NOT NULL,
    `total` decimal(10,2) NOT NULL,
    `address` varchar(200) NOT NULL,
    `city` varchar(100) NOT NULL,
    `state` varchar(50) NOT NULL,
    `country` varchar(50) NOT NULL,
    `zip_code` varchar(20) NOT NULL,
    `phone` varchar(20) DEFAULT NULL,
    `email` varchar(100) DEFAULT NULL,
    `created_at` datetime NOT NULL,
    `updated_at` datetime NOT NULL,
    `deleted_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) 
    AUTO_INCREMENT = 520001 
    partition by hash(`id`)
    (
    partition p0,
    partition p1,
    partition p2,
    partition p3,
    partition p4,
    partition p5,
    partition p6,
    partition p7,
    partition p8,
    partition p9,
    partition p10,
    partition p11,
    partition p12,
    partition p13,
    partition p14,
    partition p15
    );

7. Click the Run button and view the execution result.