Overview
The default_table_organization parameter allows you to set the default table organization mode for creating new tables.
Note
The current version of the default_table_organization parameter applies only to user tenants of OceanBase AI Database. The sys (system) tenant does not support it.
Privilege requirements
Query parameters
Both the
systenant and all user tenants can query this parameter using theSHOW PARAMETERSstatement or theGV$OB_PARAMETERSview.Modify parameters
Both the
systenant and user tenants can modify this parameter.
Attributes
Attribute |
Description |
|---|---|
| Parameter type | String |
| Default Value | INDEX |
| Value range |
|
| Modifiable | Yes, you can use the following methods:ALTER SYSTEM SETStatement modification. |
| Whether to restart the OBServer node for the change to take effect | No, the setting takes effect immediately. |
Usage instructions
You can use the default_table_organization parameter to flexibly control the default table organization mode for newly created tables. This parameter can be set to one of the following two values:
HEAP:- When you create a table with a primary key in this mode, the data is not sorted according to the order of the primary key columns.
- This mode is suitable for scenarios that require processing large volumes of data and performing complex query analysis.
INDEX:- When a table with a primary key is created in this mode, data is stored and sorted in the order of the primary key columns.
- This mode is suitable for transaction processing scenarios that require fast queries and frequent updates.
By setting default_table_organization, you can select an appropriate table organization mode based on your specific requirements, thereby optimizing system performance and improving data management efficiency.
Configuration example
View the current information of the
default_table_organizationparameter.SHOW PARAMETERS LIKE 'default_table_organization';The return result is as follows:
+-------+----------+----------------+----------+----------------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+ | zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | default_value | isdefault | +-------+----------+----------------+----------+----------------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+ | zone1 | observer | 100.10.100.100 | 2882 | default_table_organization | STRING | INDEX | The default_organization configuration option allows you to set the default table organization mode to either HEAP (unordered data storage) or INDEX (the data rows are held in an index defined on the primary key for the table) when creating new tables. | TENANT | TENANT | DEFAULT | DYNAMIC_EFFECTIVE | INDEX | 1 | +-------+----------+----------------+----------+----------------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+ 1 row in setSet the value of the
default_table_organizationparameter toHEAP.ALTER SYSTEM SET default_table_organization = 'HEAP';Check the current value of the
default_table_organizationparameter.SHOW PARAMETERS LIKE 'default_table_organization';The return result is as follows:
+-------+----------+----------------+----------+----------------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+ | zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | default_value | isdefault | +-------+----------+----------------+----------+----------------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+ | zone1 | observer | 100.10.100.100 | 2882 | default_table_organization | STRING | HEAP | The default_organization configuration option allows you to set the default table organization mode to either HEAP (unordered data storage) or INDEX (the data rows are held in an index defined on the primary key for the table) when creating new tables. | TENANT | TENANT | DEFAULT | DYNAMIC_EFFECTIVE | INDEX | 0 | +-------+----------+----------------+----------+----------------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+ 1 row in setCreate a test table named
test_tbl1.CREATE TABLE test_tbl1(col1 INT);View the definition of table
test_tbl1.SHOW CREATE TABLE test_tbl1;The return result is as follows:
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test_tbl1 | CREATE TABLE `test_tbl1` ( `col1` 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