CREATE TABLE

Description

This statement is used to create a table in a 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] [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 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] [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)

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

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

Parameters

table_mode_value

• NORMAL: Normal. The probability of triggering a major compaction is extremely low after data is dumped in this mode.
• QUEUING: The probability of triggering a major compaction is low after data is dumped in this mode.
• MODERATE: Moderate. The probability of triggering a major compaction is moderate after data is dumped in this mode.
• SUPER: Super. The probability of triggering a major compaction is high after data is dumped in this mode.
• EXTREME: Extreme. The probability of triggering a major compaction is relatively high after data is dumped in this mode.

For more information about major compactions, see Adaptive major compactions.

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 partitioned by HASH with 8 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 partitioned by RANGE and subpartitioned 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, using zstd as the compression algorithm, and set the macroblock reservation space 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
  

• Add a foreign key to the ref_t2 table. When a DELETE operation affects a matching row in a child table, the foreign key constraint specifies to set the value of the primary key in the parent table to null.

  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 table partitioned by RANGE and subpartitioned by RANGE.

  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 the tbl10 table with a DOP of 3.

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

• Define default values for columns by using functions.

  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 replica-level copy table named dup_t1. You can insert data and read and write data from and to the copy table as you would with a normal table. For a read request, if you use a Proxy, the request can be routed to any OBServer node; if you directly connect to an OBServer node, the read request will be processed on the OBServer node where the local replica is located.

  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);
  

• Identify a column with the Skip Index attribute when creating a table.

  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)
  );
  

• Create a table named tbl1 that has an integer column named col1, and specify to perform the operation with 5 DOP. The data in the new table tbl1 will be sourced from the query result of the tbl2 table.

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

• 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 compatibility mode

• Temporary tables are used in various business scenarios in Oracle compatibility mode and ensure basic accuracy and functionality.
• Generally, temporary tables are used for compatibility and to reduce the amount 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 ends or a session disconnects, additional work needs to be done to clear the data of temporary tables, which causes extra overheads.
• The checks and cleans performed on login threads may increase the load on the threads, thus prolonging the login time. In severe cases, the login may fail.

Create a temporary table

By default, the database modifies the table creation statement as follows:

• It adds the SYS_SESSION_ID column as the primary key.
• It adds the SYS_SESS_CREATE_TIME column as a normal column.
• It creates a hash-partitioned table with 16 partitions, using the SYS_SESSION_ID column as the partitioning key.

For example:

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

The preceding statement is modified into the following one.

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 in a temporary table

When you execute the INSERT statement, the database will default insert the session ID and session creation time of the current session into the SYS_SESSION_ID and SYS_SESS_CREATE_TIME columns.

When you execute the UPDATE, DELETE, or SELECT statement, the database will add the filter condition "SYS_SESSION_ID = session ID of the current session" to the SQL statement based on SQL rewriting. This condition allows the SQL optimizer to perform partition pruning and extract the query range.

Data clearance in a temporary table

• For temporary tables with the ON COMMIT DELETE ROWS option (transaction temporary tables, which is also the default option), when a transaction ends, a new transaction is started and the data of the temporary table is cleared by executing the DELETE statement.
• For temporary tables with the ON COMMIT PRESERVE ROWS option (session temporary tables), when a session disconnects, the data of the temporary table is cleared by executing the DELETE statement.
• If session IDs are reused, in OceanBase Database V3.2.4 BP4 and earlier, the data of the current session ID is checked upon login. If extra data is found, it is cleared.
• Login checks and data clearance based on non-unique session IDs may cause login failures (the cluster cannot be logged in).

Routing of data in a temporary table

• 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 to allow subsequent requests from the Proxy to be routed only to the current session.

Drop a temporary table

You can successfully execute the DROP statement while performing DML operations, and all data in the temporary table will be deleted. This behavior differs from that in Oracle, where the database waits until all sessions stop using the temporary table resources before you can execute the DROP statement.

Cross-version feature support

Workarounds for severe issues

Unable to log in

1. Stop the business related to temporary tables and delete temporary tables or perform a major compaction. Generally, the issue can be resolved on its own. If the issue persists, perform step 2.
2. Restart the server where login fails.

PL cache bloat due to plan reuse failure

For example, a procedure definition statement that contains a temporary table:

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

Accesses to the temporary table in the procedure definition statement cannot share plans because session IDs for accessing the temporary table are different. As a result, each session needs to compile the procedure p1 to generate a cache, which may lead to stability issues. You can modify the SQL statement as follows to avoid this problem:

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

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

Data not cleared after a failure

Fault conditions may lead to data residue, and currently there is no automatic cleanup method, but it generally does not affect usage. If there is too much residual data, it can be resolved by dropping the temporary table and then rebuilding it.