CREATE TABLE

Purpose

You can use this statement to create a table in the 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] [table_column_group_option] [AS] select;

table_definition_list:
    table_definition [, table_definition ...]

table_definition:
      column_definition
     | [,
        | [CONSTRAINT [constraint_name]] { PRIMARY KEY|UNIQUE } (column_name) // Add constraints 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]
    {
    [DEFAULT expression]
    [NULL | NOT NULL]
    [CONSTRAINT [constraint_name]] [ PRIMARY KEY|UNIQUE ]       // Add constraints when creating columns.
    [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] [COMMENT string] [SKIP_INDEX(skip_index_option_list)]
    }

skip_index_option_list:
    skip_index_option [,skip_index_option ...]

skip_index_option:
    MIN_MAX
    | SUM

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

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

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:
      TABLEGROUP = tablegroup_name
    | block_size
    | compression
    | comment
    | ENABLE ROW MOVEMENT
    | DISABLE ROW MOVEMENT
    | physical_attribute
    | parallel_clause
    | DUPLICATE_SCOPE [=] 'none|cluster'

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 subpartition*/
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 subpartition*/
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 non-template-based subpartitions can be defined.*/]

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 non-template-based subpartitions can be defined.*/]
    [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 non-template-based subpartitions can be defined.*/]
    [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

table_column_group_option:
    WITH COLUMN GROUP(all columns)
  | WITH COLUMN GROUP(each column)
  | WITH COLUMN GROUP(all columns, each column)

Parameters

Examples

• Create a table named tbl1.

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

• Create a HASH-partitioned table with eight partitions.

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

• Create a RANGE-HASH-subpartitioned table.

  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 and zstd compression for the table and reserve 5% of space in each macroblock.

  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
  

• Specify a foreign key for the ref_t2 table. Specify to execute the SET NULL action when a DELETE operation affects a key value in the parent table that has matching rows in the child table.

  obclient> CREATE TABLE ref_t1(c1 INT PRIMARY KEY,C2 INT);
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE ref_t2(c1 INT PRIMARY KEY, C2 INT,FOREIGN KEY(c2) REFERENCES ref_t1(c1) ON DELETE SET NULL);
  Query OK, 0 rows affected
  

• Create a non-template-based RANGE-RANGE-subpartitioned table.

  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 and set its DOP to 3.

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

• Define the default value of a column by using a function.

  obclient> CREATE SEQUENCE SEQ_PERSONIPTVSEQ START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30;
  Query OK, 0 rows affected
  
  obclient> SELECT LPAD(SEQ_PERSONIPTVSEQ.NEXTVAL,18,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;
  +----------------------------------------------------------------------------+
  | LPAD(SEQ_PERSONIPTVSEQ.NEXTVAL,18,TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')) |
  +----------------------------------------------------------------------------+
  | 2022-07-05 11:34:1                                                         |
  +----------------------------------------------------------------------------+
  1 row in set
  
  obclient> CREATE TABLE FUNC_DEFAULT_TEST ( OID NUMBER(20,0) DEFAULT LPAD(SEQ_PERSONIPTVSEQ.NEXTVAL,18,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')) NOT NULL);
  Query OK, 0 rows affected
  

• Create a cluster-level replicated table named dup_t1. Insert data into, read data from, and write data to the replicated table. If you connect to the database by using an OceanBase Database Proxy (ODP), the read request may be routed to any OBServer node. If you directly connect to an OBServer node, the read request is executed on the connected OBServer node as long as the local replica is readable.

  obclient> CREATE TABLE dup_t1(c1 INT) DUPLICATE_SCOPE = 'cluster';
  Query OK, 0 rows affected
  
  obclient> INSERT INTO dup_t1 VALUES(1);
  Query OK, 1 row affected
  
  obclient> SELECT * FROM dup_t1;
  +------+
  | c1   |
  +------+
  |    1 |
  +------+
  1 row in set
  

• Create a columnstore table named tbl1_cg.

  CREATE TABLE tbl1_cg (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(50)) WITH COLUMN GROUP(each column);
  

• Create a table and specify the skip index attribute for a column.

  CREATE TABLE test_skidx(
    col1 NUMBER SKIP_INDEX(MIN_MAX, SUM),
    col2 FLOAT SKIP_INDEX(MIN_MAX),
    col3 VARCHAR2(1024) SKIP_INDEX(MIN_MAX),
    col4 CHAR(10)
  );
  

Limitations on global temporary tables in Oracle mode

• With basic data correctness and functionality guarantees, temporary tables in the Oracle mode of OceanBase Database are used in a lot of business scenarios.
• Generally, temporary tables are used for compatibility purposes with less business construction. You can use temporary tables in limited business scenarios with lower performance requirements. If the business scenarios support normal tables, we recommend that you use normal tables.

Performance and stability

• The SQL execution efficiency of temporary tables is basically the same as that of normal tables.
• However, temporary tables require data cleanup after a transaction ends or a session is disconnected, which causes additional overhead.
• Check and cleanup operations on temporary tables during logon may cause pressure on the logon thread, resulting in longer logon time, and in severe cases even a logon failure.

Temporary table creation

When you use a statement to create a temporary table, the statement is rewritten as follows by default:

• The SYS_SESSION_ID column is added as the primary key.
• The SYS_SESS_CREATE_TIME column is added as a normal column.
• A HASH-partitioned table is created, with SYS_SESSION_ID being the partitioning key and a fixed number of 16 partitions.

Here is an example:

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

The statement will be rewritten as follows:

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 database executes an INSERT statement, the session ID and session creation time of the current session are inserted into the SYS_SESSION_ID and SYS_SESS_CREATE_TIME columns respectively by default.

When the database executes an UPDATE, DELETE, or SELECT statement that contains a temporary table, the filter condition, SYS_SESSION_ID = session_id of the current session, is added by default to the statement. By using this filter condition, the SQL optimizer can perform partition pruning and extract the query range.

Data cleanup for temporary tables

• For a temporary table with the ON COMMIT DELETE ROWS option (which is the default option that indicates a transaction temporary table), when the transaction ends, a new transaction is started and the DELETE statement is executed to clean up data in the temporary table.
• For a temporary table with the ON COMMIT PRESERVE ROWS option (which indicates a session temporary table), when the session is disconnected, the DELETE statement is executed to clean up data in the temporary table.
• Session IDs may be reused. In OceanBase Database V3.2.4 BP4 and earlier versions, data of the current session ID is checked during logon. If the data exists, an additional cleanup is performed.
• Logon check and cleanup performed due to a session ID conflict may cause a fault, resulting in a cluster logon failure.

Routing for temporary tables

• Transaction temporary tables (ON COMMIT DELETE ROWS)

  Access to a temporary table within a transaction can only be routed to the node that starts the transaction.

• Session temporary tables (ON COMMIT PRESERVE ROWS)

  After a session accesses a temporary table, the OBServer node instructs the ODP to forward subsequent requests only to the current session.

Drop a temporary table

You can drop a temporary table during the execution of DML statements, which is the same as dropping a normal table. Then, all data is deleted from the temporary table. However, Oracle databases allow you to drop a temporary table only after all sessions do not hold the temporary table.

Feature support by different OceanBase Database versions

Workaround for serious issues

Logon failure

1. Stop all business related to temporary tables, and drop temporary tables or perform a major compaction. Generally, the fault is cleared automatically. If the fault persists, go to step 2.
2. Restart the OBServer node that you cannot log on.

PL cache bloat due to a plan reuse failure

For example, a PL procedure is defined with statements that use a temporary table.

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

Different sessions cannot share the plans for the access to temporary tables. Therefore, each session needs to compile the p1 procedure to generate the corresponding cache, which may lead to stability issues. You can rewrite the SQL statement as follows:

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

This way, you have changed SQL statements involving temporary tables to dynamic SQL statements to avoid this problem.

Data is not cleaned up due to a fault

Data may not be fully cleaned up due to a fault. Currently, OceanBase Database does not support automatic cleanup in this case. Generally, this problem does not affect the use of the database. To remove excessive residual data, you can drop the temporary table and rebuild it.