CREATE TABLE

Description

This statement is used to create a table in a database.

Syntax

CREATE [GLOBAL TEMPORARY] TABLE table_name
      (table_definition_list) [table_option_list] [partition_option] [on_commit_option]
CREATE [GLOBAL TEMPORARY] TABLE table_name
      (table_definition_list) [table_option_list] [partition_option] [AS] select;

table_definition_list:
    table_definition [, table_definition ...]

table_definition:
      column_definition
     | [,
        | INDEX [index_name] index_desc
        | [CONSTRAINT [constraint_name]] { PRIMARY KEY|UNIQUE } (column_name) //Add a constraint after all columns are created.
        | [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name, column_name ...) references_clause constraint_state
        | [CONSTRAINT [constraint_name]] CHECK(expression) constraint_state
       ]

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
    column_name data_type
    [VISIBLE|INVISIBLE] [GENERATED BY DEFAULT AS IDENTITY | GENERATED ALWAYS AS IDENTITY]
    {
    [DEFAULT expression]
    [NULL | NOT NULL]
    [CONSTRAINT [constraint_name]] [ PRIMARY KEY|UNIQUE ]       //Add a constraint when the column is created.
    [CONSTRAINT [constraint_name] CHECK(expression) constraint_state]
    [CONSTRAINT [constraint_name] references_clause]
    |
    [GENERATED ALWAYS] AS (expression) [VIRTUAL]
    [NULL | NOT NULL] [UNIQUE KEY] [[PRIMARY] KEY] [UNIQUE LOWER_KEY]
    }

references_clause:
     REFERENCES table_name [ (column_name, column_name ...) ] [ON DELETE {CASCADE}]

constraint_state:
    [RELY|NORELY] [USING INDEX index_option_list] [ENABLE|DISABLE] [VALIDATE|NOVALIDATE]

index_desc:
   (column_name [, column_name ...]) [index_option_list]

index_option_list:
    index_option [ index_option ...]

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

table_option_list:
    table_option [ table_option ...]

table_option:
    primary_zone
    | TABLEGROUP = tablegroup_name
    | block_size
    | compression
    | DUPLICATE_SCOPE [=] "none|cluster"
    | LOCALITY [=] "locality description"
    | ENABLE ROW MOVEMENT
    | DISABLE ROW MOVEMENT
    | physical_attribute
    | parallel_clause

physical_attribute_list:
    physical_attribute [physical_attribute]

physical_attribute:
    PCTFREE [=] num
    | PCTUSED num
    | INITRANS num
    | MAXTRANS num
    | STORAGE(storage_option [storage_option] ...)
    | TABLESPACE tablespace

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

compression:
    NOCOMPRESS
    | COMPRESS { BASIC | FOR OLTP | FOR QUERY [LOW|HIGH]  | FOR ARCHIVE [LOW|HIGH]}

storage_option:
    INITIAL num [K|M|G|T|P|E]
    | NEXT num [K|M|G|T|P|E]
    | MINEXTENTS num [K|M|G|T|P|E]
    | MAXEXTENTS num [K|M|G|T|P|E]

partition_option:
      PARTITION BY HASH(column_name_list)
      [subpartition_option] hash_partition_define
    | PARTITION BY RANGE (column_name_list)
      [subpartition_option] (range_partition_list)
    | PARTITION BY LIST (column_name_list)
      [subpartition_option] (list_partition_list)

/*Template-based subpartitioning*/
subpartition_option:
      SUBPARTITION BY HASH (column_name_list) hash_subpartition_define
    | SUBPARTITION BY RANGE (column_name_list) SUBPARTITION TEMPLATE
      (range_subpartition_list)
    | SUBPARTITION BY LIST (column_name_list) SUBPARTITION TEMPLATE
      (list_subpartition_list)

/*Non-template-based subpartitioning*/
subpartition_option:
      SUBPARTITION BY HASH (column_name_list)
    | SUBPARTITION BY RANGE (column_name_list)
    | SUBPARTITION BY LIST (column_name_list)

subpartition_list:
      (hash_subpartition_list)
    | (range_subpartition_list)
    | (list_subpartition_list)

hash_partition_define:
      PARTITIONS partition_count [TABLESPACE tablespace] [compression]
    | (hash_partition_list)

hash_partition_list:
    hash_partition [, hash_partition ...]

hash_partition:
    partition [partition_name] [subpartition_list/*Only for non-template-based subpartitioning.*/]

hash_subpartition_define:
      SUBPARTITIONS subpartition_count
    | SUBPARTITION TEMPLATE (hash_subpartition_list)

hash_subpartition_list:
    hash_subpartition [, hash_subpartition ...]

hash_subpartition:
    subpartition [subpartition_name]

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION [partition_name]
    VALUES LESS THAN {(expression_list) | (MAXVALUE)}
    [subpartition_list/*Only for non-template-based subpartitioning.*/]
    [ID = num] [physical_attribute_list] [compression]

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

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

list_partition_list:
    list_partition [, list_partition] ...

list_partition:
    PARTITION [partition_name]
    VALUES (DEFAULT|expression_list)
    [subpartition_list/*Only for non-template-based subpartitioning.*/]
    [ID num] [physical_attribute_list] [compression]

list_subpartition_list:
    list_subpartition [, list_subpartition] ...

list_subpartition:
    SUBPARTITION [partition_name] VALUES (DEFAULT|expression_list) [physical_attribute_list]

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE

on_commit_option:
  ON COMMIT DELETE ROWS
  | ON COMMIT PRESERVE ROWS

Parameter description

Examples

• Create a table named tbl1 and set the primary zone to zone1.

  obclient> CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50)) PRIMARY_ZONE = 'zone1';
  Query OK, 0 rows affected
  

• Create a replicated table.

  obclient> CREATE TABLE tbl2 (i_id INT,i_name VARCHAR(24), i_price DECIMAL(5,2),
                   i_data VARCHAR(50),i_im_id INT,PRIMARY KEY(i_id))
                   COMPRESS FOR QUERY
                   PCTFREE = 0
                   BLOCK_SIZE = 16384
                   DUPLICATE_SCOPE='cluster'
                   LOCALITY='F@zone1,R{all_server}@zone2'
                   PRIMARY_ZONE = 'zone1';
  Query OK, 0 rows affected
  

• Create a table with an index.

  obclient> CREATE TABLE tbl3 (col1 INT PRIMARY KEY, col2 INT, col3 INT, INDEX idx1 (col2));
  Query OK, 0 rows affected
  

• Create a table with 8 partitions by hash.

  obclient> CREATE TABLE tbl4 (col1 INT PRIMARY KEY, col2 INT) PARTITION BY HASH(col1) PARTITIONS 8;
  Query OK, 0 rows affected
  

• Create a table with partitioning by RANGE and subpartitioning by HASH.

  obclient> CREATE TABLE tbl5 (col1 INT, col2 INT, col3 INT)
                   PARTITION BY RANGE(col1)
                   SUBPARTITION BY HASH(col2)  SUBPARTITIONS  5
                   (PARTITION p0 VALUES LESS THAN(0),
                    PARTITION p1 VALUES LESS THAN(100));
  Query OK, 0 rows affected
  

• Enable encoding, use zstd as the compression algorithm, and set the reserved space of macroblocks to 5%.

  obclient> CREATE TABLE tbl6 (col1 INT, col2 INT, col3 VARCHAR(64))
                   COMPRESS FOR ARCHIVE
                   PCTFREE 5;
  Query OK, 0 rows affected
  

• Create a transaction-level temporary table.

  obclient> CREATE GLOBAL TEMPORARY TABLE tbl7(col1 INT) ON COMMIT DELETE ROWS;
  Query OK, 0 rows affected
  

• Create a table with constraints.

  obclient> CREATE TABLE tbl8 (col1 INT, col2 INT, col3 INT,CONSTRAINT equal_check1 CHECK(col2 = col3 * 2) ENABLE VALIDATE);
  Query OK, 0 rows affected
  

• Create a table with subpartitioning by RANGE and partitioning by HASH.

  obclient> CREATE TABLE tbl9 (col1 INT, col2 INT, col3 INT)
                   PARTITION BY RANGE(col1)
                   SUBPARTITION BY RANGE(col2)
  (
    PARTITION p0 VALUES LESS THAN(100)
    (
      SUBPARTITION p0_r1 VALUES LESS THAN(2019),
      SUBPARTITION p0_r2 VALUES LESS THAN(2020),
      SUBPARTITION p0_r3 VALUES LESS THAN(2021)
    ),
    PARTITION p1 VALUES LESS THAN(200)
    (
      SUBPARTITION p1_r1 VALUES LESS THAN(2019),
      SUBPARTITION p1_r2 VALUES LESS THAN(2020),
      SUBPARTITION p1_r3 VALUES LESS THAN(2021)
    ),
    PARTITION p2 VALUES LESS THAN(300)
    (
      SUBPARTITION p2_r1 VALUES LESS THAN(2019),
      SUBPARTITION p2_r2 VALUES LESS THAN(2020),
      SUBPARTITION p2_r3 VALUES LESS THAN(2021)
    )
  );
  Query OK, 0 rows affected
  

• Create a table named tbl10 with a DOP of 3.

  obclient> CREATE TABLE tbl10(col1 INT PRIMARY KEY, col2 INT) PARALLEL 3;
  Query OK, 0 rows affected
  

• Create a table named test_tbl1, set the col1 column to an auto-increment column, and specify it as the primary key.

  CREATE TABLE test_tbl1 (
    col1 INT GENERATED BY DEFAULT AS IDENTITY,
    col2 VARCHAR2(50),
    PRIMARY KEY (col1)
  );  
  

Limitations on global temporary tables in Oracle-compatible mode

• Temporary tables are used in various business scenarios in Oracle-compatible mode and have basic accuracy and functionality guarantees.
• Generally, temporary tables are used for compatibility and to reduce the workload of business modification. If the business scenarios are limited and the performance requirements for temporary tables are not high, you can use temporary tables. It is better to convert the business scenarios to use regular tables.

Performance and stability

• The SQL execution efficiency of temporary tables is similar to that of normal tables, without any particular advantages.
• When a transaction is committed or a session is disconnected, additional work, such as data cleanup, needs to be performed for temporary tables, which causes extra overheads.
• The data check and cleanup performed on login for temporary tables may put pressure on the login thread, thereby increasing the login time. In severe cases, login may fail.

Create a temporary table

When a temporary table is created, the system automatically modifies the table creation statement:

• An additional SYS_SESSION_ID column is added as the primary key.
• An additional SYS_SESS_CREATE_TIME column is added as a normal column.
• A hash-partitioned table is created with SYS_SESSION_ID as the partitioning key. The number of partitions is fixed at 16.

For example:

CREATE GLOBAL TEMPORARY TABLE t1(
  c1 INT,
  c2 INT,
  PRIMARY KEY(c1)
);

is automatically modified into the following statement.

CREATE GLOBAL TEMPORARY TABLE t1(
  SYS_SESSION_ID INT,
  SYS_SESS_CREATE_TIME INT,
  c1 INT,
  c2 INT,
  PRIMARY KEY(SYS_SESSION_ID, c1)
)
PARTITION BY HASH(SYS_SESSION_ID) PARTITIONS 16;

DML/Query statements for temporary tables

When the INSERT statement is executed, the system automatically inserts the session ID and session creation time of the current session into the SYS_SESSION_ID column and SYS_SESS_CREATE_TIME column. For UPDATE, DELETE, and SELECT statements, the system automatically modifies the statements by adding the filter condition "SYS_SESSION_ID = session ID of the current session" to limit the execution results of the statements to the current session. This condition enables the SQL optimizer to perform partition pruning and extract the query range.

Data cleanup for temporary tables

• For temporary tables with the ON COMMIT DELETE ROWS option (transaction temporary tables, which is also the default option), at the end of the transaction, a new transaction is started and the DELETE statement is executed to delete the data in the temporary table.
• For temporary tables with the ON COMMIT PRESERVE ROWS option (session temporary tables), when the session disconnects, the DELETE statement is executed to delete the data in the temporary table.
• Since session IDs can be reused, in OceanBase Database V3.2.4 BP4 and earlier, when you log in, the system checks the data of the current session ID and, if necessary, performs an additional cleanup.
• The data check and cleanup performed during login based on session IDs that are not unique may result in login failures (cluster unavailable).

Routing of queries for temporary tables

• For transaction temporary tables (ON COMMIT DELETE ROWS) Accesses to a transaction temporary table can only be routed to the node where the transaction is initiated.
• For session temporary tables (ON COMMIT PRESERVE ROWS) After a session accesses a temporary table, the OBServer node notifies the Proxy. Then, subsequent requests from the Proxy can only be sent to the current session.

Drop a temporary table

You can successfully execute the DROP statement while executing an DML statement, and the data of the temporary table is deleted.

You cannot wait until all sessions stop using the temporary table resources before you execute the DROP statement, which is different from Oracle.

Cross-version support

Workarounds for severe issues

Unable to log in

1. Stop the business related to temporary tables and drop or merge the temporary tables. Generally, the issue can be resolved. If not, proceed to step 2.
2. Restart the server where the login is failed.

Expansion of the PL cache due to plan reusability

For example, a temporary table is included in the procedure definition of a PL statement:

CREATE PROCEDUCE p1 () (
  INSERT INTO temp_table VALUES (xxx);
  SELECT * FROM temp_table WHERE ROWNUM = 1 INTO var1;
);

Since different sessions cannot share plans for accessing temporary tables, each session needs to compile the procedure p1 to generate a cache for it. This may cause stability issues. You can rewrite the preceding PL statement as follows:

EXCUTE IMMEDIATE 'SELECT * FROM temp_table WHERE ROWNUM = 1 INTO var1;'

You can also modify the SQL statement for a temporary table into a dynamic SQL statement to bypass this issue.

Data not cleared after a failure

A failure may result in data residue. At present, there is no automatic cleanup method. Generally, this does not affect the use of the temporary table. If a large amount of data is residue, you can drop the temporary table and rebuild it.