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] [[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)
    | MICRO_BLOCK_FORMAT_VERSION [=] {1|2}

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]
    | PARTITION BY RANGE (column_name) INTERVAL (expr)
        [subpartition_option]
        (range_partition)

/*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 a data dump is extremely low.
• QUEUING: In this mode, the probability of triggering a major compaction after a data dump is low.
• MODERATE: In this mode, the probability of triggering a major compaction after a data dump is moderate.
• SUPER: In this mode, the probability of triggering a major compaction after a data dump is high.
• EXTREME: In this mode, the probability of triggering a major compaction after a data dump 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 partition management. Valid values:

  • true: The default value. Indicates that dynamic partition management is enabled.
  • false: Indicates that dynamic partition management is disabled.

• TIME_UNIT = {'hour' | 'day' | 'week' | 'month' | 'year'}: Required. Specifies the time unit for partitioning, that is, the interval for automatically creating partition boundaries. 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 time for pre-creating partitions. When dynamic partition management is scheduled, partitions are pre-created so that the upper bound of the maximum partition > now() + precreate_time. Valid values:

  • -1: The default value. Indicates that no partitions are pre-created.
  • 0: Indicates that only the current partition is pre-created.
  • n {hour | day | week | month | year}: Indicates the time span for pre-creating partitions. For example, 3 hour indicates that partitions are pre-created within 3 hours.

  Note

  • When multiple partitions are pre-created, the interval between partition boundaries is specified by TIME_UNIT.
  • The boundary of the first pre-created partition is the ceiling of the maximum existing partition boundary based on TIME_UNIT.

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

  • -1: The default value. Indicates that partitions do not expire.
  • 0: Indicates that all partitions except the current one are expired.
  • n {hour | day | week | month | year}: Indicates the partition expiration time. For example, 1 day indicates that the partition expiration time is 1 day.

• TIME_ZONE = {'default' | 'time_zone'}: Optional. Specifies the time zone for determining the current time and the size of the partitioning key of the date or timestamp type. Valid values:

  • default: The default value. Indicates that no time zone is specified and the tenant time zone is used. For other data types, the time_zone field must be default.
  • time_zone: Indicates 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. Valid values:

  • none: The default value. Indicates that no precision is specified (the partitioning key is not of the number type).
  • us: Indicates microsecond precision.
  • ms: Indicates millisecond precision.
  • s: Indicates second precision.

For more information about creating 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 primary 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 encoding enabled and using the zstd compression algorithm, with 5% of space reserved for macroblocks.

  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 rows in the parent table that match the key values 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 partitioned 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;
  

• Use a function to define the default value for a column.

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

  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 5 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 a column named col1 set as an auto-increment column and specified as 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.

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

• Create a table named tb and enable the new flat row storage format (version 2).

  obclient> CREATE TABLE tb(c1 INT PRIMARY KEY, c2 INT) micro_block_format_version = 2;
  

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 use of global temporary tables is to ensure compatibility and reduce the need for business modifications. They are suitable for limited scenarios where performance requirements are not high. If the business scenario can be changed to use regular tables, it is better to do so.

Performance and stability

• The SQL execution efficiency of global temporary tables is generally similar to that of regular tables, without significant advantages.
• Global temporary tables require additional work, such as data cleanup, when transactions end or sessions disconnect, which incurs additional overhead.
• The checks and cleanup actions performed during login for global temporary tables 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 modifies the create table 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, respectively.

When executing an UPDATE, DELETE, or SELECT statement, the system automatically adds a filter condition " SYS_SESSION_ID = current session session_id" to the statements that include global temporary tables. 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 when the transaction ends, 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), a new transaction is started when the session disconnects, and the DELETE statement is executed to remove the temporary table data.
• Due to the possibility of session ID reuse, OceanBase Database V3.2.4 BP4 and earlier versions perform data checks for the current session ID upon login. If additional cleanup is required, it is performed.
• The login checks and cleanup actions for non-unique session IDs may lead to login failures (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 successfully execute the DROP statement while executing DML operations. All data in the global 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 the temporary tables, or perform a major compaction. If the issue persists, proceed to step 2.
2. Restart the machine that is unable to log in.

PL Cache expansion due to plan reuse

For example, consider a PL/SQL block that contains 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 execution plans for global temporary tables, each session must compile the PRO_1 procedure to generate its own cache. This can lead to stability issues. Changing the temporary table SQL to use dynamic SQL can resolve this issue.

Data not cleaned up during failures

Failures can result in data residue, which currently cannot be automatically cleaned up. This generally does not affect usage. If excessive residual data remains, you can drop the temporary table and recreate 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. Try to insert data into the read-only table (the operation is 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 (the operation is 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. Try to insert data into the converted read-only table (the operation is 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

• Modify a table
• Create a table
• Truncate a table
• Drop a table