You can use the CREATE TABLE statement to create a table.
This section describes how to create a non-partitioned table. For information about how to create and use a partitioned table, see Create a partitioned table.
Create a non-partitioned table
A non-partitioned table is a table that has only one partition.
The sample statement for creating a non-partitioned table is as follows:
obclient>CREATE TABLE table_name1(w_id int
, w_ytd decimal(12,2)
, w_tax decimal(4,4)
, w_name varchar(10)
, w_street_1 varchar(20)
, w_street_2 varchar(20)
, w_city varchar(20)
, w_state char(2)
, w_zip char(9)
, unique(w_name, w_city)
, primary key(w_id)
);
Query OK, 0 rows affected (0.09 sec)
obclient>CREATE TABLE table_name2 (c_w_id int NOT NULL
, c_d_id int NOT null
, c_id int NOT null
, c_discount decimal(4, 4)
, c_credit char(2)
, c_last varchar(16)
, c_first varchar(16)
, c_middle char(2)
, c_balance decimal(12, 2)
, c_ytd_payment decimal(12, 2)
, c_payment_cnt int
, c_credit_lim decimal(12, 2)
, c_street_1 varchar(20)
, c_street_2 varchar(20)
, c_city varchar(20)
, c_state char(2)
, c_zip char(9)
, c_phone char(16)
, c_since date
, c_delivery_cnt int
, c_data varchar(500)
, index icust(c_last, c_d_id, c_w_id, c_first, c_id)
, FOREIGN KEY (c_w_id) REFERENCES table_name1(w_id)
, primary key (c_w_id, c_d_id, c_id)
);
Query OK, 0 rows affected
The example creates two tables and defines constraints on columns within them, including primary keys and foreign keys on different columns. For more information about primary keys and foreign keys, see Define column constraints.
When creating table columns, select the appropriate data types. For detailed descriptions of SQL data types, see Overview of data types.
Note
To meet performance and maintenance requirements, it is recommended to design a primary key or unique key for tables during creation. If no suitable field serves as a primary key, you can create the table without specifying a primary key. After the table is created, the system automatically designates an auto-increment column as a hidden primary key. For more information about auto-increment columns, see Define an auto-increment column.
Copy data from an existing table to create a new table
Copy table data
You can use the CREATE TABLE AS SELECT statement to copy table data, but the structure is not identical, and information such as constraints, indexes, default values, and partitions is lost.
A sample statement is as follows:
obclient>CREATE TABLE t1_copy AS SELECT * FROM t1;
Query OK, 3 rows affected
Copy table structure
You can use the CREATE TABLE LIKE statement to copy a table's structure, but not its data.
A sample statement is as follows:
obclient>CREATE TABLE t1_like like t1;
Query OK, 0 rows affected
Create a rowstore table
OceanBase AI Database supports creating rowstore tables and converting between rowstore and columnstore formats.
When the default_table_store_format parameter is set (which is the default), tables are created as rowstore tables by default. When default_table_store_format is set to a value other than row, you can create a rowstore table by specifying the WITH COLUMN GROUP(all columns) option.
For information about converting between rowstore and columnstore formats, see Modify a table. For information about creating a columnstore index, see Create an index.
Specify WITH COLUMN GROUP(all columns) to create a rowstore table.
Example:
CREATE TABLE tbl1_cg (col1 INT PRIMARY KEY, col2 VARCHAR(50)) WITH COLUMN GROUP(all columns);
Note
If you create a rowstore table by specifying the WITH COLUMN GROUP(all columns) option, the table remains in the rowstore format even if you later execute the DROP COLUMN GROUP(all columns) statement to drop this column group.
Create a columnstore table
OceanBase AI Database supports creating columnstore tables, converting between rowstore and columnstore formats, and creating columnstore indexes. By default, when you create a table in OceanBase AI Database, it is a rowstore table. You can explicitly create it as a columnstore table or a hybrid rowstore-columnstore table by specifying the WITH COLUMN GROUP option.
For information about converting between rowstore and columnstore formats, see Modify a table. For information about creating columnstore indexes, see Create an index.
Specify WITH COLUMN GROUP(all columns, each column) to create a hybrid rowstore-columnstore table.
Example:
CREATE TABLE tbl1_cg (col1 INT PRIMARY KEY, col2 VARCHAR(50)) WITH COLUMN GROUP(all columns, each column);
Specify WITH COLUMN GROUP(each column) to create a columnstore table.
Example:
CREATE TABLE tbl2_cg (col1 INT PRIMARY KEY, col2 VARCHAR(50)) WITH COLUMN GROUP(each column);
When you create and use a columnstore table, if you import a large amount of data, you need to perform a major compaction to improve read performance and collect statistics to adjust execution strategies.
Major compaction: After bulk data import, it is recommended to perform a major compaction. This helps improve read performance because the major compaction organizes fragmented data, making it more contiguous in physical storage and reducing disk I/O during reads. After data import, trigger a major compaction within the tenant to ensure all data is compacted to the baseline layer. For more information, see
MAJOR AND MINOR.Statistics collection: After the major compaction is complete, it is recommended to collect statistics. This is crucial for the optimizer to generate effective query plans and execution strategies. Execute GATHER_SCHEMA_STATS to collect statistics for all tables, and monitor the collection progress through the view GV$OB_OPT_STAT_GATHER_MONITOR.
Note that as the data volume of a columnstore table increases, the speed of the major compaction may decrease.
Create a heap-organized table (heap table)
OceanBase AI Database supports two types of table organization: Index Organized Table and Heap Organized Table.
Limitations
The Unique Key Local or the Primary Key of a heap-organized table must include all partition keys.
When creating an index on a heap-organized table, the index name cannot be the same as the name of the first column of the primary key.
The following DDL operations are not currently supported for heap-organized tables in OceanBase AI Database:
- Primary key constraint operations
- Modifying a column into the primary key
- Changing the type length of a primary key column from larger to smaller
- Adding a primary key column
- Modifying the primary key to an auto-increment column
- Dropping a primary key column
- Partition splitting
Syntax for specifying table organization
To specify the table organization when creating a table, add the ORGANIZATION option after the CREATE TABLE statement. The SQL statement is as follows:
CREATE TABLE table_name column_definition ORGANIZATION [=] {INDEX | HEAP};
Here, the ORGANIZATION parameter specifies the storage order of data rows in the table. Valid values are as follows:
INDEX: Indicates that the table model is a clustered index table model, meaning the table is organized by index.HEAP: Indicates that the table model is a non-clustered index table model, meaning the table is organized as a heap.
If the ORGANIZATION option is not specified, its value is the same as the value of the default_table_organization parameter.
Examples:
Specify the value of the
ORGANIZATIONattribute asHEAPwhen creating a table.CREATE TABLE ora_tbl1 (col1 INT, col2 INT) ORGANIZATION = HEAP;When creating a table, do not specify
ORGANIZATION.After modifying the tenant-level parameter
default_table_organization, create a table. For more information about thedefault_table_organizationparameter, see default_table_organization.Note
The
default_table_organizationparameter applies only to user tenants and is not supported by the sys (system) tenant.Set the value of the
default_table_organizationparameter toHEAP.ALTER SYSTEM SET default_table_organization = 'HEAP';Create a table named
ora_tbl2.CREATE TABLE ora_tbl2(col1 INT, col2 INT);View the definition of the
ora_tbl2table.SHOW CREATE TABLE ora_tbl2;The query result is as follows:
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ora_tbl2 | CREATE TABLE `ora_tbl2` ( `col1` int(11) DEFAULT NULL, `col2` int(11) DEFAULT NULL ) ORGANIZATION HEAP DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 | +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set
Specify clustered columns when creating a table
To specify clustered columns when creating a table, add the CLUSTER BY table option after the CREATE TABLE statement. The SQL statement is as follows:
CREATE TABLE table_name (table_definition_list) CLUSTER BY (column_name_list);
column_name_list:
column_name [, column_name ...]
The parameters are described as follows:
column_name: Indicates the clustered column used for sorting.
Notice
In OceanBase AI Database, if you do not explicitly specify the table mode (ORGNIZATION = HEAP) but define the CLUSTER BY option, OceanBase AI Database creates the table as a heap by default.
Example:
Create a table named cb_tbl1 sorted by col2 and col4.
obclient> CREATE TABLE cb_tbl1 (
col1 INT PRIMARY KEY,
col2 INT,
col3 DECIMAL(10, 2),
col4 TIMESTAMP
)
CLUSTER BY (col2, col4);
Create a temporary table
A temporary table is a special table at the session level, primarily used for temporarily storing intermediate data.
Limitations
Temporary table routing: Since temporary tables need to support load balancing like regular tables, they cannot be bound to a specific server by default. Consider disabling load balancing when using them.
In OceanBase AI Database, write operations on temporary tables created within the same transaction are not supported when the database is in the Serializable isolation level.
If the current session has a temporary table with the same name as a regular table, it is not recommended to use the database-level statistics collection feature in that session, as the results may be unexpected.
Temporary tables are cleaned up through background logic. To avoid significant impact on business operations from their DDL, the cleanup speed is limited. Therefore, scenarios with extensive use of temporary tables are advised to manually delete them promptly.
DBLink cannot access temporary tables.
When connecting to OBServer via ODP, you need to manually adjust the server_protocal, client_session_id_version, and proxy_id parameters.
The specific steps are as follows:
Run the following command to modify the communication protocol configuration of OBProxy.
ALTER proxyconfig SET server_protocol = 'OceanBase 2.0';Run the following command to set the algorithm for generating the Client Session ID to version 2.
ALTER proxyconfig SET client_session_id_version = 2;Run the following command to change the ODP ID to 1. Different ODPs require different numbers to ensure the generated Client Session IDs do not conflict.
ALTER proxyconfig SET proxy_id = 1;
Usage example
Create a temporary table named tbl1:
CREATE TEMPORARY TABLE tbl1(col1 INT);
Specify the table's update model
To specify the table update model when creating a table, add the MERGE_ENGINE table option after the CREATE TABLE statement. The SQL statement is as follows:
CREATE TABLE table_name column_definition
MERGE_ENGINE = {delete_insert | partial_update | append_only};
Note
The configuration value of the MERGE_ENGINE parameter cannot be modified after it is specified during table creation.
The MERGE_ENGINE parameter specifies the table's update model. Valid values are as follows:
partial_update: Indicates that the partial update model is used. Each update only records the modified columns (delta), which saves storage space. However, queries require merging multiple data copies to obtain the latest values. This mode is suitable for OLTP scenarios with frequent updates and low query requirements.delete_insert: Indicates that the full-column update model is used. Each update writes a complete row (delete old row + insert new row), prioritizing query performance. This mode supports Skip Index for incremental data (Memtable/Delta SSTable). During queries, incremental data can be filtered and pushed down. If the filtering result does not involve updating baseline data, baseline and incremental data can be processed in batches separately, reducing read amplification. This mode is suitable for OLAP scenarios with a high proportion of incremental data, frequent execution of complex queries, or batch processing and analysis.append_only: Indicates that the table's update mode allows onlyINSERToperations and prohibits all other DML and DDL operations that modify stored data. To clean up expired data in this mode, you can use the TTL feature. For detailed usage restrictions, see Update model usage limitations.
Note
The core difference between delete_insert and partial_update lies in the write granularity: the former writes a complete column for each update, while the latter writes only the changed columns. Full-column writing enables delete_insert to support Skip Index for Delta SSTables, thereby achieving better filter pushdown in analytical queries.
If the MERGE_ENGINE option is not specified, its value is the same as the value of the default_table_merge_engine parameter.
Application scenarios
Scenario |
Recommended Value |
Description |
|---|---|---|
| OLAP, analytical queries, batch processing | delete_insert |
When incremental data can be filtered, query performance is improved. Note that incremental data occupies more storage space. |
| OLTP, high-frequency updates, storage-sensitive | partial_update |
It saves storage and is suitable for scenarios with frequent updates but low query requirements. |
| Immutability, efficient append, and simplified queries | append_only |
Suitable for high-throughput scenarios (such as log and IoT data monitoring), featuring efficient range queries and aggregate computation. |
For more information about configuration scenarios, see Recommended configurations.
Update model usage limitations
When the table update model MERGE_ENGINE is set to append_only, the following restrictions apply:
The following DML syntax is prohibited:
UPDATEDELETEMERGE INTOREPLACE
The
INSERT [IGNORE] xxx ON DUPLICATE KEY UPDATEsyntax is not prohibited, but an error is reported when actual data modification occurs.The following DDL is prohibited:
TRUNCATEtables and partitions- Partition exchange
- Drop partitions
- Drop columns
- Reduce column range
- Dynamic partitioning
- Clear obsolete columns
- Add a column (with
DEFAULT VALUE) - Add an auto-increment column
Examples
Create table
mer_tbl1with the update model set to full column update (delete_insert).obclient> CREATE TABLE mer_tbl1 (col1 INT, col2 INT) MERGE_ENGINE = delete_insert;Create a columnstore table
mer_tbl2with the update model set to full column update (delete_insert).obclient> CREATE TABLE mer_tbl2 (col1 INT, col2 INT) MERGE_ENGINE = delete_insert WITH COLUMN GROUP(each column);Create a table in
append_onlymode and performINSERT,SELECT,UPDATE, andDELETEoperations.Create table
mer_tbl3with the update model set toappend_only.obclient> CREATE TABLE mer_tbl3 (col1 INT PRIMARY KEY, col2 VARCHAR(100)) MERGE_ENGINE = append_only;Insert data into table
mer_tbl3.obclient> INSERT INTO mer_tbl3 VALUES(1, 'oceanbase');Query data from table
mer_tbl3.obclient> SELECT * FROM mer_tbl3;The query result is as follows:
+------+-----------+ | col1 | col2 | +------+-----------+ | 1 | oceanbase | +------+-----------+ 1 row in setAn error occurs when updating data in table
mer_tbl3.obclient> UPDATE mer_tbl3 SET col2 = 'database' WHERE col1 = 1;The query result is as follows:
ERROR 1235 (0A000): update append_only table is not supportedAn error occurs when deleting data from table
mer_tbl3.obclient> DELETE FROM mer_tbl3 WHERE col1 = 1;The query result is as follows:
ERROR 1235 (0A000): delete from append_only table is not supported