In OceanBase AI Database, a table is the most fundamental data storage unit, containing all data accessible to users. Each table consists of multiple rows, with each row comprising several columns. Proper table design is the foundation for system efficiency and scalability.
Table types
OceanBase AI Database supports various table types, which can be understood from dimensions such as primary key, storage format, and special purpose.
Classification by primary key
- Primary key table: A table that contains a primary key. Data is stored in an ordered manner based on the primary key, ensuring high efficiency for point queries.
- Table without primary key: A table where no primary key is specified. Data is stored in the order it is written, making it suitable for append-only write scenarios such as logging.
Classification by storage format
- Row-based table: Data is organized and stored by row. It is suitable for OLTP-type point queries and transaction processing scenarios.
- Columnar table: Data is organized and stored by column. It significantly improves the performance of analytical queries, especially for aggregate analysis scenarios involving large volumes of data.
- Hybrid row-column table: Data is stored both row-wise and column-wise. The system automatically chooses between a row-based or columnar access path based on the query statement. It is suitable for HTAP scenarios that require balancing transactional and analytical workloads.
Other table types
Partitioned table: Divides the data in a table into different blocks based on specific rules. Data within the same block is physically stored together, facilitating partition pruning and parallel processing. Basic partitioning strategies include RANGE, LIST, HASH, and KEY partitioning.
Heap table (heap-organized table): Data is not stored in an order based on the primary key. The primary key serves as a secondary unique index, decoupling data from the index. When data is written in chronological order, this can effectively utilize skip indexing to improve the efficiency of analytical queries. In AP scenarios, a newly created table uses a heap organization by default—the tenant-level parameter default_table_organization has a value of
HEAPunder the OLAP parameter template. IfORGANIZATIONis not explicitly specified, a new table is a heap table, which facilitates large-scale import and utilization of skip indexing.Temporary table: Used to store intermediate results in ETL processes, temporary data during direct load, and cached intermediate results for complex queries. Data is isolated by session or transaction and is automatically cleaned up after a session ends or a transaction is committed/rolled back.
External table: Data is not stored inside the database but in an external storage service. Access is provided through the database's table definition. It is suitable for data import/export and cross-system data querying.
Table update modes
OceanBase AI Database supports specifying a data update mode when creating a table using the merge_engine parameter in the CREATE TABLE statement, to adapt to different business scenarios.
delete_insert (full-column update mode)
Prioritizes query performance. Using the "Merge-On-Write" mechanism, it converts
UPDATEoperations into full-columnDELETEandINSERTrecords, ensuring each row contains a complete set of column values. This mode significantly improves the efficiency of complex queries and batch processing (such as analytical tasks), but incremental data requires additional storage space. It is suitable for scenarios where incremental data is frequent and requires rapid analysis.partial_update (partial update mode)
Records only the values of the modified columns, avoiding redundant storage. During queries, multiple versions of data must be merged to obtain the latest values, resulting in relatively lower performance. However, it is more suitable for scenarios with high-frequency updates but low query demand (such as OLTP-type business) or environments sensitive to storage costs.
Feature category |
delete_insert update mode |
partial_update update mode |
|---|---|---|
| Storage method | Each update writes two rows to the SSTable (DELETE and INSERT), which contains all column data. |
Each update only records the values of the modified columns, saving storage space. |
| Query efficiency |
|
When querying, records from multiple MemTables and SSTables need to be merged to obtain the latest value of the primary key, which may impact performance. This is suitable for scenarios that are sensitive to storage costs and involve frequent update operations. |
| Application scenarios | Scenarios with a high proportion of incremental data that require frequent execution of complex queries or batch processing analysis. | Scenarios with high data update frequency but low query requirements. |
For more information, see Create a table.