Create a table

You can execute the CREATE TABLE statement to create tables. This topic describes how to create non-partitioned tables.

For information about how to create partitioned tables, see Create a partitioned table.

Prerequisites

You have the user-level or database-level CREATE privilege.

Create a table

Syntax

CREATE TABLE [IF NOT EXISTS] table_name
      (table_definition_list) [table_option_list] [partition_option] [AS] select;

CREATE TABLE [IF NOT EXISTS] table_name
      LIKE table_name;

table_definition_list:
    table_definition [, table_definition ...]

table_definition:
      column_definition
    | [CONSTRAINT [constraint_name]] PRIMARY KEY index_desc
    | [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} 
            [index_name] index_desc
    | [CONSTRAINT [constraint_name]] FOREIGN KEY 
            [index_name] index_desc 
            REFERENCES reference_definition 
            [match_action][opt_reference_option_list]
    | {INDEX | KEY} [index_name] index_desc

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
     column_name data_type
         [DEFAULT const_value] [AUTO_INCREMENT]
         [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment
   | column_name data_type
         [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]
         [opt_generated_column_attribute] 

index_desc:
   (column_desc_list) [index_type] [index_option_list]

match_action:
   MATCH {SIMPLE | FULL | PARTIAL}

opt_reference_option_list:
   reference_option [,reference_option...]

reference_option:
   ON {DELETE | UPDATE} {RESTRICT | CASCADE | SET NULLX | NO ACTION | SET DEFAULT}

column_desc_list:
    column_desc [, column_desc ...]

column_desc:
     column_name [(length)] [ASC | DESC]

index_type:
    USING BTREE

index_option_list:
    index_option [ index_option ...]

index_option:
      [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)
    | comment

table_option_list:
    table_option [ table_option ...]

table_option:
      [DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name
    | [DEFAULT] COLLATE [=] collation_name
    | primary_zone
    | replica_num
    | table_tablegroup
    | block_size
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | comment
    | DUPLICATE_SCOPE [=] "none|zone|region|cluster"
    | LOCALITY [=] "locality description"
    | ROW_FORMAT [=] REDUNDANT|COMPACT|DYNAMIC|COMPRESSED|DEFAULT
    | PCTFREE [=] num
    | parallel_clause

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

partition_option:
      PARTITION BY HASH(expression)
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY KEY([column_name_list])
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] (range_partition_list)
    | PARTITION BY LIST {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] PARTITIONS partition_count

subpartition_option:
      SUBPARTITION BY HASH(expression)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY KEY(column_name_list)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      (range_subpartition_list)
    | SUBPARTITION BY LIST(expression)

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE

Parameters

Create a non-partitioned table

A non-partitioned table is a table that has only one partition.

For example, each of the following statements creates a non-partitioned table:

Example 1: Create a table named student.

obclient> CREATE TABLE student(
            id number,
            name varchar2(18),
            sex char(1),
            age number,
            address varchar2(200),
            email varchar2(100),
            c_date date,
            PRIMARY KEY (id)
     );
Query OK, 0 rows affected 

Example 2: Create a table named staff.

obclient> CREATE TABLE staff(
           id number not null,
           s_id number not null,
           name varchar(18),
           sex char(1) default '0' check(
           sex = '0'
           or sex = '1'
           ),
          age int not null,
          address varchar(200),
         email varchar(100) unique,
         c_date date,
         wages number(7,3),
         Entry_time TIMESTAMP(6),
         PRIMARY KEY (id),
         FOREIGN KEY (s_id) REFERENCES student(id),
         index idx2 (c_date)
     );
Query OK, 0 rows affected 

In the preceding example, two tables are created. Some constraints, including primary keys and a foreign key, are defined on different columns. For more information about primary keys and foreign keys, see About types of column constraints.

Pay attention to data types when you create table columns. For more information about SQL data types, see OceanBase Database SQL Reference (Oracle Mode).

Note

• To ensure performance and facilitate maintenance, we recommend that you specify a primary key or a unique key when you create a table. If no existing field can be used as the primary key, you can add a numeric column as the primary key, and use the Sequence object of the Oracle tenant to generate sequential values for this column. For more information about sequences, see Create a sequence, Modify a sequence, and Drop a sequence.

• You cannot add a primary key to a table by using the ALTER TABLE statement. Therefore, you must specify the primary key when you create a table.

Create a table by copying the data in an existing table

Copy table data

You can execute the CREATE TABLE AS SELECT statement to copy data from a table. The constraints, indexes, default values, and partitions are lost when you copy the data.

Examples

Copy the data in the staff table to the staff_copy table. Statement:

CREATE TABLE staff_copy AS SELECT * FROM staff;

• Compare data of two tables

  • Data in the staff table:

    obclient> SELECT * FROM staff;
    +----+------+--------+------+-----+-------------------+----------------------+-----------+-------+------------------------------+
    | ID | S_ID | NAME   | SEX  | AGE | ADDRESS           | EMAIL                | C_DATE    | WAGES | ENTRY_TIME                   |
    +----+------+--------+------+-----+-------------------+----------------------+-----------+-------+------------------------------+
    |  1 |    1 | Zhang San   | 1    |  15 | Community XX in Hangzhou      | 2********@163.com    | 11-NOV-70 |   100 | 12-DEC-70 12.12.12.000000 PM |
    |  2 |    2 | Li Si   | 0    |  15 | Community XX in Hangzhou      | 1********@QQ.com     | 11-DEC-70 |   100 | 12-DEC-70 12.12.12.000000 PM |
    |  3 |    3 | Wang Wu   | 0    |  33 | Community XX in Beijing      | 3********@shouhu.com | 11-DEC-71 |   100 | 12-DEC-70 12.12.12.000000 PM |
    +----+------+--------+------+-----+-------------------+----------------------+-----------+-------+------------------------------+
    3 rows in set
    

  • Data in the staff_copy table:

    obclient> SELECT * FROM staff_copy;
    +----+------+--------+------+-----+-------------------+----------------------+-----------+-------+------------------------------+
    | ID | S_ID | NAME   | SEX  | AGE | ADDRESS           | EMAIL                | C_DATE    | WAGES | ENTRY_TIME                   |
    +----+------+--------+------+-----+-------------------+----------------------+-----------+-------+------------------------------+
    |  1 |    1 | Zhang San   | 1    |  15 | Community XX in Hangzhou      | 2********@163.com    | 11-NOV-70 |   100 | 12-DEC-70 12.12.12.000000 PM |
    |  2 |    2 | Li Si   | 0    |  15 | Community XX in Hangzhou      | 1********@QQ.com     | 11-DEC-70 |   100 | 12-DEC-70 12.12.12.000000 PM |
    |  3 |    3 | Wang Wu   | 0    |  33 | Community XX in Beijing      | 3********@shouhu.com | 11-DEC-71 |   100 | 12-DEC-70 12.12.12.000000 PM |
    +----+------+--------+------+-----+-------------------+----------------------+-----------+-------+------------------------------+
    3 rows in set
    

• Compare table structures

  • Structure of the staff table:

    obclient> DESC staff;
    +------------+---------------+------+-----+---------+-------+
    | FIELD      | TYPE          | NULL | KEY | DEFAULT | EXTRA |
    +------------+---------------+------+-----+---------+-------+
    | ID         | NUMBER        | NO   | PRI | NULL    | NULL  |
    | S_ID       | NUMBER        | NO   | NULL | NULL    | NULL  |
    | NAME       | VARCHAR2(18)  | YES  | NULL | NULL    | NULL  |
    | SEX        | CHAR(1)       | YES  | NULL | '0'     | NULL  |
    | AGE        | NUMBER(38)    | NO   | NULL | NULL    | NULL  |
    | ADDRESS    | VARCHAR2(200) | YES  | NULL | NULL    | NULL  |
    | EMAIL      | VARCHAR2(100) | YES  | UNI | NULL    | NULL  |
    | C_DATE     | DATE          | YES  | MUL | NULL    | NULL  |
    | WAGES      | NUMBER(7,3)   | YES  | NULL | NULL    | NULL  |
    | ENTRY_TIME | TIMESTAMP(6)  | YES  | NULL | NULL    | NULL  |
    +------------+---------------+------+-----+---------+-------+
    10 rows in set
    

  • Structure of the staff_copy table:

    obclient> DESC staff_copy;
    +------------+---------------+------+-----+---------+-------+
    | FIELD      | TYPE          | NULL | KEY | DEFAULT | EXTRA |
    +------------+---------------+------+-----+---------+-------+
    | ID         | NUMBER        | NO   | NULL | NULL    | NULL  |
    | S_ID       | NUMBER        | NO   | NULL | NULL    | NULL  |
    | NAME       | VARCHAR2(18)  | YES  | NULL | NULL    | NULL  |
    | SEX        | CHAR(1)       | YES  | NULL | NULL    | NULL  |
    | AGE        | NUMBER(38)    | NO   | NULL | NULL    | NULL  |
    | ADDRESS    | VARCHAR2(200) | YES  | NULL | NULL    | NULL  |
    | EMAIL      | VARCHAR2(100) | YES  | NULL | NULL    | NULL  |
    | C_DATE     | DATE          | YES  | NULL | NULL    | NULL  |
    | WAGES      | NUMBER(7,3)   | YES  | NULL | NULL    | NULL  |
    | ENTRY_TIME | TIMESTAMP(6)  | YES  | NULL | NULL    | NULL  |
    +------------+---------------+------+-----+---------+-------+
    10 rows in set
    

Note

The examples show that the data in the original and replica tables is exactly the same, but the constraints, indexes, and default values are missing in the structure of the staff_copy table.

Create a replica table

Table replication is an advanced tuning technique in OceanBase Database.

In general, OceanBase uses a three-replica architecture. By default, each partition of a table exists in three replicas. One of the replicas is the leader, and the others are followers. By default, the leader provides write and read services.

You can create a replica table on every server in the tenant. You can have only one leader and two or more followers in the tenant. Full data synchronization is implemented to maintain strong consistency between the leader and followers. This allows your application to execute specific SQL JOIN queries on the same server for better performance.

To create a replica table, add a DUPLICATE_SCOPE clause to the CREATE TABLE statement.

Example:

Create a table named staff_DC that uses the zstd compression algorithm. Enable the encoding storage format and set the table as a replica table, the reserved space for macroblocks to 0, the size of microblocks to 16384, and the locality to F@zone1.

CREATE TABLE staff_DC(
  id number not null,
  name varchar(18),
  sex char(1) default '0' check(
    sex = '0'
    or sex = '1'
  ),
  age int not null,
  address varchar(200),
  email varchar(100) unique,
  c_date date,
  wages number(7, 3),
  Entry_time TIMESTAMP(6),
  PRIMARY KEY (id)
) COMPRESS FOR QUERY PCTFREE 0 BLOCK_SIZE = 16384 
  DUPLICATE_SCOPE = 'cluster' locality = 'F@zone1';