default_table_organization

功能描述

default_table_organization 配置项用户设置创建新表时默认的表组织模式。

权限要求

• 查询配置项

  sys 租户和所有用户租户均可以使用 SHOW PARAMETERS 语句或视图 GV$OB_PARAMETERS 查询该配置项。

• 修改配置项

  sys 租户和用户租户均可修改该配置项。

属性说明

使用说明

使用配置项 default_table_organization 可以灵活地控制默认创建表的表组织模式。该配置项可以设置为以下两个值：

• HEAP（堆组织表）：

  • 该模式下创建有主键表时，数据不会根据主键列的顺序对数据存储进行排序。
  • 该模式适用于需要处理大量数据并进行复杂查询分析的场景。

• INDEX（索引组织表）：

  • 该模式下创建有主键表时，数据会根据主键列的顺序对数据存储进行排序。
  • 该模式适用于需要快速查询和频繁更新的事务处理场景。

通过设置 default_table_organization，您可以根据具体需求选择合适的表组织模式，从而优化系统性能并提高数据管理效率。

配置示例

1. 查看配置项 default_table_organization 当前信息。

   SHOW PARAMETERS LIKE 'default_table_organization';
   

   返回结果如下：

   +-------+----------+----------------+----------+----------------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+
   | 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 set
   

2. 将配置项 default_table_organization 的值设置为 HEAP。

   ALTER SYSTEM SET default_table_organization = 'HEAP';
   

3. 再次查看配置项 default_table_organization 当前信息。

   SHOW PARAMETERS LIKE 'default_table_organization';
   

   返回结果如下：

   +-------+----------+----------------+----------+----------------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+
   | 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 set
   

4. 创建测试表 test_tbl1。

   CREATE TABLE test_tbl1(col1 INT);
   

5. 查看表 test_tbl1 定义。

   SHOW CREATE TABLE test_tbl1;
   

   返回结果如下：

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

相关文档

• 创建表
• 配置最佳实践