CREATE TABLE

描述

该语句用来在数据库中创建一张新表。

语法

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) //在创建所有列后添加约束
        | [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 ]       //在创建列时添加约束
    [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}
    | COLUMN_NAME_CASE_SENSITIVE [=] {True | False}

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)

/*模板化二级分区*/
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)

/*非模板化二级分区*/
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/*仅非模板化二级分区可定义*/]

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/*仅非模板化二级分区可定义*/]
    [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 /*仅非模板化二级分区可定义*/]
    [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)

参数说明

table_mode_value

• NORMAL：默认值，表示正常。在该模式下，数据转储后触发合并的概率极低。
• QUEUING：在该模式下，数据转储后触发合并的概率低。
• MODERATE：表示适度。在该模式下，数据转储后触发合并的概率为中等。
• SUPER：表示超级。在该模式下，数据转储后触发合并的概率高。
• EXTREME：表示极端。在该模式下，转储后触发合并的概率较高。

更多有关合并的信息，请参见 自适应合并。

dynamic_partition_policy_option

• ENABLE = {true | false}：可选项，表示是否启用动态分区管理。可修改，取值如下：

  • true：默认值，表示启用动态分区管理。
  • false：表示禁用动态分区管理。

• TIME_UNIT = {'hour' | 'day' | 'week' | 'month' | 'year'}：必选项，表示分区的时间单位，即自动创建分区边界的间隔。不可修改，取值如下：

  • hour：按小时划分分区。
  • day：按天划分分区。
  • week：按周（星期）划分分区。
  • month：按月划分分区。
  • year：按年划分分区。

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}：可选项，表示预创建时间。调度一次动态分区管理，会预创建分区使得 最大分区上界 > now() + precreate_time。可修改，取值如下：

  • -1：默认值，表示不预创建分区。
  • 0：表示仅预创建当前分区。
  • n {hour | day | week | month | year}：表示预创建对应时间跨度的分区。例如，3 hour 表示预创建 3 小时内的分区。

  说明

  • 需要预创建多个分区时，分区边界的间隔为 TIME_UNIT。
  • 首个预创建的分区边界为现存最大分区边界按 TIME_UNIT 向上取整。

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}：可选项，表示分区过期时间。调度一次动态分区管理，会删除所有 分区上界 < now() - expire_time 的过期分区。可修改，取值如下：

  • -1：默认值，表示分区永不过期。
  • 0：表示除当前分区外，之前的所有分区均过期。
  • n {hour | day | week | month | year}：表示分区过期时间。例如，1 day 表示分区过期时间为 1 天。

• TIME_ZONE = {'default' | 'time_zone'}：可选项，表示判断当前时间与时间类型（date、timestamp 类型）分区键大小时，依赖的时区信息。不可修改，取值如下：

  • default：默认值，表示不额外配置时区，使用租户时区。除上述类型外，其他类型 time_zone 字段必须为 default。
  • time_zone：表示自定义时区偏移值。例如，+8:00 等时区偏移值。

• BIGINT_PRECISION = {'none' | 'us' | 'ms' | 's'}：可选项，表示 number 类型分区键的时间戳精度。不可修改，取值如下：

  • none：默认值，表示无精度（分区键不为 number 类型）。
  • us：微秒精度。
  • ms：毫秒精度。
  • s：秒精度。

更多有关创建动态分区表的信息，参见 创建动态分区表。

示例如下：

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

示例

• 创建表 TEST_TBL1。

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

• 创建表 TEST_TBL2，创建 Hash 分区，分区数为 8 的表。

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

• 创建表 TEST_TBL3，创建一级分区为 Range 分区，二级分区为 Hash 分区的表。

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

• 创建表 TEST_TBL4，开启 Encoding 并使用 zstd 压缩，宏块保留空间为 5%。

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

• 创建一个事务级临时表 TEST_TBL5。

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

• 创建一个带约束的表 TEST_TBL6。

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

• 为表 REF_T2 指定外键，当 DELETE 操作影响与子表相匹配行的父表中键值时，执行 SET NULL 操作。

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

• 创建非模板化的 Range + Range 二级分区表 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)
    )
  );
  

• 创建表 TEST_TBL8 的并行度为 3。

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

• 使用函数定义列的默认值。

  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;
  

  返回结果如下：

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

• 创建 cluster 级别的复制表 DUP_T1。像普通表一样插入和读写复制表的数据。对于一个读请求，如果使用了 Proxy，读请求可能会路由到任意一个 OBServer 节点；如果直连 OBServer 节点，只要本地副本可读，就会在直连的 OBServer 节点上执行读请求。

  obclient> CREATE TABLE DUP_T1(c1 int) DUPLICATE_SCOPE = 'cluster';
  

  obclient> INSERT INTO DUP_T1 VALUES(1);
  

  obclient> SELECT * FROM DUP_T1;
  

  返回结果如下：

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

• 创建列存表 TBL_CG。

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

• 创建表时标识列 Skip Index 属性。

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

• 创建表 TEST_TBL9，它有一个整数类型的列 col1，并且指定该操作应用 5 个并行度来完成，指定了新表 TEST_TBL8 的数据内容将来源于表 TEST_TBL8 的查询结果。

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

• 创建一个名为 TEST_TBL10 的表，列 col1 设置为自增列，并且指定为主键。

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

• 创建开启持久化宏块级别 bloom filter 的表 tb。

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

• 创建表 tb 启用新的 Flat 行存储格式（版本 2）。

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

Oracle 兼容模式 Global 临时表的使用限制

• Oracle 兼容模式下的临时表在多个业务场景下都有实际应用，具备基本的正确性/功能性保证。
• 一般使用临时表的目的大多是做兼容，减少业务改造。当业务在有限场景，对临时表性能要求不高时可以使用。如果业务场景能够改成普通表，则改普通表更好。

性能和稳定性

• 临时表的 SQL 执行效率与普通表基本一样，无特别优势。
• 临时表在事务结束/Session 断开时需要做额外的工作，即清理数据，会产生额外开销。
• 临时表在登录时进行的检查和清理动作可能会对登录线程造成压力，导致登录时间变长，严重时导致无法登录。

创建临时表

当创建临时表时，默认会对建表语句进行改写：

• 新增 SYS_SESSION_ID 列作为主键。
• 新增 SYS_SESS_CREATE_TIME 作为普通列。
• 创建以 SYS_SESSION_ID 作为分区键的 Hash 分区表，分区个数为固定的 16 分区。

例如：

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

会被改写为如下形式。

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/查询语句

执行 INSERT 时，会默认将当前会话的 Session ID 和 Session 创建时间插入 SYS_SESSION_ID 列和 SYS_SESS_CREATE_TIME。

执行 UPDATE/DELETE/SELECT 时，通过 SQL 改写，默认会为包含临时表的语句添加过滤条件 “SYS_SESSION_ID = 当前会话 session_id”。通过这个条件，SQL 优化器可以进行分区裁剪并抽取查询范围。

临时表数据清理

• 对 ON COMMIT DELETE ROWS 选项的临时表（事务临时表，也是默认选项），在事务结束时，开启一个新事务，通过执行 DELETE 语句删除临时表数据。
• 对 ON COMMIT PRESERVE ROWS 选项的临时表（会话临时表），在会话断开时，通过执行 DELETE 语句删除临时表数据。
• 由于 Session ID 可能存在复用的情况，在 OceanBase 数据库 V3.2.4 BP4 和之前版本会在登录时对当前 Session ID 的数据进行检查，如果存在需要额外进行一次清理。
• 由于 Session ID 不唯一进行的登录检查和清理，可能会导致故障（集群无法登录）。

临时表的路由

• 事务临时表（ON COMMIT DELETE ROWS） 事务内对临时表的访问只能路由给事务开启节点。

• 会话临时表（ON COMMIT PRESERVE ROWS） 当 Session 访问过临时表后，OBServer 节点会通知 Proxy，让 Proxy 的后续请求只能发给当前 Session。

删除临时表

与普通表相同可以在 DML 执行的同时成功执行 DROP，临时表全部数据会被删除。这与 Oracle 的行为不同，Oracle 是等待所有 Session 不持有临时表资源之后，才能 DROP。

交叉功能的支持情况

严重问题的规避手段

无法登录

1. 停止临时表相关业务，将临时表删除或做一次合并，一般可以自行恢复，如果仍然无法恢复，则进行第 2 步。
2. 重启无法登录的机器。

无法重用计划导致 PL Cache 膨胀

例如 PL 定义中包含临时表的语句：

obclient> CREATE OR REPLACE PROCEDURE PRO_1
AS
  var1 VARCHAR2(100);
BEGIN
  -- 使用动态 SQL 创建临时表
  EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE temp_table (
    col1 VARCHAR2(100)
  ) ON COMMIT DROP';

  -- 动态插入数据
  EXECUTE IMMEDIATE 'INSERT INTO temp_table VALUES (''xxx'')';

  -- 动态查询
  EXECUTE IMMEDIATE 'SELECT col1 FROM temp_table WHERE ROWNUM = 1' INTO var1;

  DBMS_OUTPUT.PUT_LINE(var1);
END PRO_1;
/

由于不同 Session 对临时表访问无法共享计划，每个 Session 都需要编译一次这个 Procedure PRO_1 生成对应的 Cache，可能导致稳定性问题。将临时表 SQL 改为动态 SQL 可以绕过这个问题。

故障情况数据未清理

故障情况可能导致数据残留，目前无自动清理办法，一般不影响使用。如果残留数据过多，可以通过 DROP 临时表，再重建解决。

Oracle 租户下的只读和读写表

在 Oracle 租户下，您可以使用 CREATE TABLE 创建 READ ONLY 和 READ WRITE 类型的表。您也可以使用 ALTER TABLE 语句更改表的读写属性。

操作步骤：

1. 创建普通用户：

   CREATE USER test1 IDENTIFIED BY "12345";
   

2. 授予用户连接和建表权限：

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

3. 使用普通用户连接 OceanBase 数据库：

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

4. 创建只读表：

   CREATE TABLE tb_readonly1(id INT) READ ONLY;
   

5. 尝试向只读表插入数据（预期失败）：

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

6. 创建读写表：

   CREATE TABLE tb_readwrite1(id INT) READ WRITE;
   

7. 向读写表插入数据（预期成功）：

   INSERT INTO tb_readwrite1 VALUES (99),(98);
   -- 预期结果：Query OK, 2 rows affected (0.002 sec)
   --             Records: 2  Duplicates: 0  Warnings: 0
   

8. 将读写表转换为只读表：

   ALTER TABLE tb_readwrite1 READ ONLY;
   

9. 尝试向转换后的只读表插入数据（预期失败）：

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

相关文档

• 更改表
• 创建表
• 清空表
• 删除表