CREATE TABLE

Purpose

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

Syntax

CREATE [hint_options] [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
     | [,
        | [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 {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)

table_option_list:
    table_option [ table_option ...]

table_option:
      TABLEGROUP = tablegroup_name
    | block_size
    | compression
    | ENABLE ROW MOVEMENT
    | DISABLE ROW MOVEMENT
    | physical_attribute
    | parallel_clause
    | DUPLICATE_SCOPE [=] 'none|cluster'
    | TABLE_MODE [=] 'table_mode_value'

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}

table_mode_value:
    NORMAL
    | QUEUING
    | MODERATE
    | SUPER
    | EXTREME

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 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 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 non-template 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 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 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

Parameters

table_mode_value

• NORMAL: the default value, indicating normal behavior. In this mode, the probability of triggering a merge after data is dumped is extremely low.
• QUEUING: in this mode, the probability of triggering a merge after data is dumped is low.
• MODERATE: indicates a moderate strategy. In this mode, the probability of triggering a merge after data is dumped is moderate.
• SUPER: indicates a super strategy. In this mode, the probability of triggering a merge after data is dumped is high.
• EXTREME: indicates an extreme strategy. In this mode, the probability of triggering a merge after data is dumped is very high.

For more information about data compaction, see Adaptive compaction.

Examples

• Create a table named tbl1.

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

• Create a table with 8 hash partitions.

  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 range partitions as the primary partitions and hash partitions as the subpartitions.

  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 use zstd compression. Set the reserved space for 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
  

• Specify a foreign key for the ref_t2 table. When a DELETE operation affects the parent table's key values that match the rows in the child table, execute the SET NULL operation.

  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-templated 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 with a parallelism level of 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 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 and read/write data in the replicated table as you would in a regular table. For a read request, if you use a proxy, the request may be routed to any OBServer node; if you connect directly to an OBServer node, the request will be 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 table named tbl1 with an integer column named col1 and specify that the operation uses 5 parallel threads to complete. Also, specify that the data content of the new table tbl1 will be sourced from the query results of table tbl2.

  CREATE /*+ parallel(5) */ TABLE tbl1 (col1 NUMBER) AS SELECT col1 FROM tbl2;
  

• Create a table named test_tbl1 with a column named col1 set as 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 of global temporary tables in Oracle compatible mode

• Temporary tables in Oracle compatible mode are applicable in various business scenarios and provide basic correctness and functionality guarantees.
• Temporary tables are generally used for compatibility and to reduce the need for business modifications. They can be used in limited scenarios where performance requirements are not high. If the business scenario can be converted to a regular table, it is better to do so.

Performance and stability

• The SQL execution efficiency of temporary tables is similar to that of regular tables, with no significant advantages.
• Temporary tables require additional work, such as data cleanup, when transactions end or sessions disconnect, which incurs extra overhead.
• The checks and cleanup actions performed on temporary tables during login may put pressure on login threads, leading to longer login times and, in severe cases, login failures.

Creating temporary tables

When creating a temporary table, the system automatically modifies the create table statement:

• Adds a SYS_SESSION_ID column as the primary key.
• Adds a SYS_SESS_CREATE_TIME column as a regular column.
• Creates a hash-partitioned table using SYS_SESSION_ID as the partitioning key, with a fixed number of 16 partitions.

For example:

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

is modified to the following form.

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 and query statements for temporary tables

When executing an INSERT statement, the system automatically inserts the current session's session ID and session creation time into the SYS_SESSION_ID and SYS_SESS_CREATE_TIME columns, respectively.

When executing an UPDATE, DELETE, or SELECT statement, the system automatically adds a filter condition to the statement containing the temporary table: "SYS_SESSION_ID = current session session_id". This condition allows the SQL optimizer to perform partition pruning and query range extraction.

Data cleanup for temporary tables

• For temporary tables with the ON COMMIT DELETE ROWS option (transactional temporary tables, which is the default option), a new transaction is started at the end of the transaction, and the DELETE statement is executed to remove the temporary table data.
• For temporary tables with the ON COMMIT PRESERVE ROWS option (session-level temporary tables), the DELETE statement is executed when the session disconnects to remove the temporary table data.
• Due to the potential reuse of session IDs, OceanBase Database V3.2.4 BP4 and earlier versions perform checks on the data of the current session ID during login. If additional cleanup is required, it will be performed.
• The login checks and cleanups due to non-unique session IDs may lead to failures (cluster login failure).

Routing for temporary tables

• Transactional temporary tables (ON COMMIT DELETE ROWS) Access to temporary tables within a transaction can only be routed to the node where the transaction was started.

• Session-level temporary tables (ON COMMIT PRESERVE ROWS) After a session accesses a temporary table, the OBServer node notifies the proxy, which then routes subsequent requests only to the current session.

Dropping temporary tables

Like regular tables, you can execute the DROP statement while executing DML operations. All data in the temporary table will be deleted. This behavior differs from Oracle, where you can only DROP a temporary table after all sessions no longer hold resources from it.

Support for cross-functional features

Workarounds for critical issues

Unable to log in

1. Stop the business related to temporary tables, delete the temporary tables, or perform a major compaction. This usually resolves the issue. If it does not, proceed to step 2.
2. Restart the machine that is unable to log in.

PL Cache expansion due to inability to reuse plans

For example, a PL statement that includes a temporary table:

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

Since plans cannot be shared across sessions for temporary tables, each session must compile the Procedure p1 to generate a corresponding cache. This can lead to stability issues. To avoid this, modify the SQL statement to:

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

Changing the temporary table SQL to dynamic SQL can bypass this issue.

Data not cleaned up during failures

Failures can lead to data residue, but there is no automatic cleanup method. This generally does not affect usage. If excessive residual data exists, you can drop the temporary table and recreate it.