CREATE TABLE

Purpose

This statement creates 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] [[MERGE_ENGINE = {delete_insert | partial_update}] 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] [GENERATED BY DEFAULT AS IDENTITY | GENERATED ALWAYS AS IDENTITY]
    {
    [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] [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'
    | enable_macro_block_bloom_filter [=] {True | False}
    | DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list)

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

dynamic_partition_policy_list:
    dynamic_partition_policy_option [, dynamic_partition_policy_option ...]

dynamic_partition_policy_option:
    ENABLE = {true | false}
    | TIME_UNIT = {'hour' | 'day' | 'week' | 'month' | 'year'}
    | PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
    | EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
    | TIME_ZONE = {'default' | 'time_zone'}
    | BIGINT_PRECISION = {'none' | 'us' | 'ms' | 's'}

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)
    | PARTITION BY RANGE([column_name_list]) [SIZE('size_value')] [range_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 applicable for non-template 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 applicable for non-template 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 applicable for non-template 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: The default value. In this mode, the probability of triggering a major compaction after data is dumped is very low.
• QUEUING: In this mode, the probability of triggering a major compaction after data is dumped is low.
• MODERATE: In this mode, the probability of triggering a major compaction after data is dumped is moderate.
• SUPER: In this mode, the probability of triggering a major compaction after data is dumped is high.
• EXTREME: In this mode, the probability of triggering a major compaction after data is dumped is very high.

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

dynamic_partition_policy_option

• ENABLE = {true | false}: Optional. Specifies whether to enable dynamic partitioning. Valid values:

  • true: The default value. Specifies to enable dynamic partitioning.
  • false: Specifies to disable dynamic partitioning.

• TIME_UNIT = {'hour' | 'day' | 'week' | 'month' | 'year'}: Required. Specifies the time unit for partitioning. This value cannot be modified. Valid values:

  • hour: Partitions are created by hour.
  • day: Partitions are created by day.
  • week: Partitions are created by week.
  • month: Partitions are created by month.
  • year: Partitions are created by year.

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: Optional. Specifies the precreation time. When dynamic partitioning is scheduled, partitions are precreated so that the upper bound of the maximum partition > now() + precreate_time. Valid values:

  • -1: The default value. Specifies not to precreate partitions.
  • 0: Specifies to precreate only the current partition.
  • n {hour | day | week | month | year}: Specifies to precreate partitions for the specified time span. For example, 3 hour specifies to precreate partitions for the last 3 hours.

  Note

  • When multiple partitions are to be precreated, the partitioning interval is specified by TIME_UNIT.
  • The upper bound of the first precreated partition is the ceiling of the upper bound of the existing maximum partition, based on TIME_UNIT.

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: Optional. Specifies the partition expiration time. When dynamic partitioning is scheduled, all partitions whose upper bound < now() - expire_time are deleted. Valid values:

  • -1: The default value. Specifies that partitions never expire.
  • 0: Specifies that all partitions except the current partition expire.
  • n {hour | day | week | month | year}: Specifies the partition expiration time. For example, 1 day specifies that partitions expire 1 day after they are created.

• TIME_ZONE = {'default' | 'time_zone'}: Optional. Specifies the time zone information used to determine the current time and the size of the partitioning key of the date or timestamp type. This value cannot be modified. Valid values:

  • default: The default value. Specifies not to configure a time zone. The tenant time zone is used. For other types, the time_zone field must be default.
  • time_zone: Specifies a custom time zone offset. For example, +8:00.

• BIGINT_PRECISION = {'none' | 'us' | 'ms' | 's'}: Optional. Specifies the timestamp precision of the number type partitioning key. This value cannot be modified. Valid values:

  • none: The default value. Specifies no precision (the partitioning key is not of the number type).
  • us: Microsecond precision.
  • ms: Millisecond precision.
  • s: Second precision.

For more information about how to create a dynamic partition table, see Create a dynamic partition table.

Here is an example:

CREATE TABLE tbl2 (col1 INT, col2 TIMESTAMP)
    DYNAMIC_PARTITION_POLICY(
        ENABLE = true,
        TIME_UNIT = 'hour',
        PRECREATE_TIME = '3 hour',
        EXPIRE_TIME = '1 day',
        TIME_ZONE = '+8:00',
        BIGINT_PRECISION = 'none')
    PARTITION BY RANGE (col2)(
        PARTITION P0 VALUES LESS THAN (TIMESTAMP '2024-11-11 13:30:00')
        );

Examples

• Create a table named TEST_TBL1.

  obclient> CREATE TABLE TEST_TBL1 (col1 INT PRIMARY KEY, col2 VARCHAR(50));
  

• Create a table named TEST_TBL2 with 8 hash partitions.

  obclient> CREATE TABLE TEST_TBL2 (col1 INT PRIMARY KEY, col2 INT) PARTITION BY HASH(col1) PARTITIONS 8;
  

• Create a table named TEST_TBL3 with range partitions as the top-level partitions and hash partitions as the subpartitions.

  obclient> CREATE TABLE TEST_TBL3 (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));
  

• Create a table named TEST_TBL4 with the zstd compression algorithm and a 5% macroblock reserve.

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

• Create a transaction-level temporary table named TEST_TBL5.

  obclient> CREATE GLOBAL TEMPORARY TABLE TEST_TBL5(col1 INT) ON COMMIT DELETE ROWS;
  

• Create a table named TEST_TBL6 with constraints.

  obclient> CREATE TABLE TEST_TBL6 (col1 INT, col2 INT, col3 INT,CONSTRAINT equal_check1 CHECK(col2 = col3 * 2) ENABLE VALIDATE);
  

• Specify a foreign key for the REF_T2 table. When a DELETE operation affects a key value in the parent table that matches a row in the child table, execute the SET NULL operation.

  obclient> CREATE TABLE REF_T1(c1 INT PRIMARY KEY,C2 INT);
  

  obclient> CREATE TABLE REF_T2(c1 INT PRIMARY KEY, C2 INT,FOREIGN KEY(c2) REFERENCES ref_t1(c1) ON DELETE SET NULL);
  

• Create a non-templated range + range subpartitioned table named TEST_TBL7.

  obclient> CREATE TABLE TEST_TBL7 (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)
    )
  );
  

• Create a table named TEST_TBL8 with a parallelism level of 3.

  obclient> CREATE TABLE TEST_TBL8(col1 INT PRIMARY KEY, col2 INT) PARALLEL 3;
  

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

  obclient> SELECT LPAD(SEQ_PERSONIPTVSEQ.NEXTVAL,18,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;
  

  The result is as follows:

  +----------------------------------------------------------------------------+
  | LPAD(SEQ_PERSONIPTVSEQ.NEXTVAL,18,TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')) |
  +----------------------------------------------------------------------------+
  | 2025-04-08 19:35:1                                                         |
  +----------------------------------------------------------------------------+
  

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

• Create a replicated table named DUP_T1 at the cluster level. You can insert and read data from a replicated table in the same way as you do from 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';
  

  obclient> INSERT INTO DUP_T1 VALUES(1);
  

  obclient> SELECT * FROM DUP_T1;
  

  The result is as follows:

  +------+
  | C1   |
  +------+
  |    1 |
  +------+
  

• Create a columnstore table named TBL_CG.

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

• Specify the Skip Index attribute for a column when creating a table.

  obclient> CREATE TABLE TEST_INDEX(
    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 TEST_TBL9 with an integer column named col1, and specify that the operation uses five parallel threads. Also, specify that the data content of the new table TEST_TBL8 will be sourced from the query results of the table TEST_TBL8.

  obclient> CREATE /*+ parallel(5) */ TABLE TEST_TBL9 (col1 NUMBER) AS SELECT col1 FROM TEST_TBL8;
  

• Create a table named TEST_TBL10 with an auto-increment column named col1, and specify that the column is the primary key.

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

• Create a table named tb with a persistent bloom filter at the macroblock level.

  CREATE TABLE tb(c1 INT PRIMARY KEY, c2 INT) enable_macro_block_bloom_filter = True;
  

Limitations of global temporary tables in Oracle compatible mode

• Global temporary tables in Oracle compatible mode are widely used in various business scenarios and provide basic correctness and functionality guarantees.
• The primary purpose of using global temporary tables is to ensure compatibility and 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 use regular tables, it is better to do so.

Performance and stability

• The SQL execution efficiency of global temporary tables is similar to that of regular tables, without any significant advantages.
• Global temporary tables require additional work to clean up data when transactions end or sessions are disconnected, which incurs additional overhead.
• The checks and clean-up actions performed on global temporary tables during login may put pressure on login threads, leading to longer login times and, in severe cases, preventing login.

Creating global temporary tables

When creating a global temporary table, the system automatically rewrites the DDL statement:

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

For example:

obclient> CREATE GLOBAL TEMPORARY TABLE TEST_TBL11(
  c1 INT,
  c2 INT,
  PRIMARY KEY(c1)
);

is rewritten as follows.

obclient> CREATE GLOBAL TEMPORARY TABLE TEST_TBL11(
  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 global 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.

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

Data cleanup for global temporary tables

• For global 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 global temporary tables with the ON COMMIT PRESERVE ROWS option (session-level temporary tables), the DELETE statement is executed when the session is disconnected 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 current session ID data during login. If additional cleanup is required, it will be performed.
• The login checks and clean-up actions based on non-unique session IDs may lead to failures (such as cluster login issues).

Routing for global temporary tables

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

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

Dropping global temporary tables

Similar to 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 the DROP statement can only be executed after all sessions have released the temporary table resources.

Support for cross-functional features

Workarounds for critical issues

Unable to log in

1. Stop the business related to global temporary tables, delete or merge the temporary tables. If the issue persists, proceed to step 2.
2. Restart the machine that cannot log in.

PL Cache expansion due to inability to reuse plans

For example, a procedure definition that includes a global temporary table:

obclient> CREATE OR REPLACE PROCEDURE PRO_1
AS
  var1 VARCHAR2(100);
BEGIN
  -- Use dynamic SQL to create a global temporary table
  EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE temp_table (
    col1 VARCHAR2(100)
  ) ON COMMIT DROP';

  -- Dynamically insert data
  EXECUTE IMMEDIATE 'INSERT INTO temp_table VALUES (''xxx'')';

  -- Dynamically query
  EXECUTE IMMEDIATE 'SELECT col1 FROM temp_table WHERE ROWNUM = 1' INTO var1;

  DBMS_OUTPUT.PUT_LINE(var1);
END PRO_1;
/

Since different sessions cannot share plans for global temporary tables, each session must compile the PRO_1 procedure to generate the corresponding cache, which may lead to stability issues. Changing the temporary table SQL to use dynamic SQL can resolve this issue.

Data not cleaned up during failures

Failures may result in data residue, and there is currently no automatic cleanup method. This generally does not affect usage. If the residue data is excessive, you can drop the temporary table and rebuild it.

Read-only and read/write tables in an Oracle tenant

In an Oracle tenant, you can use the CREATE TABLE statement to create READ ONLY and READ WRITE tables. You can also use the ALTER TABLE statement to change the read/write attribute of a table.

Procedure:

1. Create a normal user:

   CREATE USER test1 IDENTIFIED BY "12345";
   

2. Grant the user the CONNECT and CREATE TABLE privileges:

   GRANT CREATE SESSION TO test1;
   GRANT CREATE TABLE TO test1;
   

3. Connect to OceanBase Database as the normal user:

   obclient -hxxx.xx.xxx.xxx -P2881 -utest1@oracle001  -A
   

4. Create a read-only table:

   CREATE TABLE tb_readonly1(id INT) READ ONLY;
   

5. Attempt to insert data into the read-only table (expected to fail):

   INSERT INTO tb_readonly1 VALUES (1);
   -- Expected error: ORA-00600: internal error code, arguments: -5235, The table 'TEST1.TB_READONLY1' is read only so it cannot execute this statement
   

6. Create a read/write table:

   CREATE TABLE tb_readwrite1(id INT) READ WRITE;
   

7. Insert data into the read/write table (expected to succeed):

   INSERT INTO tb_readwrite1 VALUES (99),(98);
   -- Expected result: Query OK, 2 rows affected (0.002 sec)
   --             Records: 2  Duplicates: 0  Warnings: 0
   

8. Convert the read/write table to a read-only table:

   ALTER TABLE tb_readwrite1 READ ONLY;
   

9. Attempt to insert data into the converted read-only table (expected to fail):

   INSERT INTO tb_readwrite1 VALUES (96),(97);
   -- Expected error: ORA-00600: internal error code, arguments: -5235, The table 'TEST1.TB_READWRITE1' is read only so it cannot execute this statement
   

References

• Change a table
• Create a table
• Truncate a table
• Drop a table