default_table_organization

Description

You can set the value of the default_table_organization parameter to specify the default table organization mode for new tables.

Privilege requirements

• Query the parameter

  The sys tenant and all user tenants can use the SHOW PARAMETERS statement or the GV$OB_PARAMETERS view to query the value of this parameter.

• Modify the parameter

  The sys tenant and user tenants can modify the value of this parameter.

Attributes

Considerations

You can use the default_table_organization parameter to specify the default table organization mode for new tables. The value of this parameter can be HEAP or INDEX.

• HEAP (heap-organized tables):

  • When a table with a primary key is created in this mode, the data in the table is not sorted by the values of the primary key column.
  • This mode is recommended if you need to process and analyze large amounts of data.

• INDEX (index-organized tables):

  • When a table with a primary key is created in this mode, the data in the table is sorted by the values of the primary key column.
  • This mode is recommended for transaction processing scenarios that involve frequent queries and updates.

You can set the default_table_organization parameter to HEAP or INDEX based on your business requirements. In this way, you can optimize the system performance and improve the data management efficiency.

Examples

1. Query the value of the default_table_organization parameter.

   SHOW PARAMETERS LIKE 'default_table_organization';
   

   The returned 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 set
   

2. Set the value of the default_table_organization parameter to HEAP.

   ALTER SYSTEM SET default_table_organization = 'HEAP';
   

3. Query the value of the default_table_organization parameter again.

   SHOW PARAMETERS LIKE 'default_table_organization';
   

   The returned 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 set
   

4. Create a test table named test_tbl1.

   CREATE TABLE test_tbl1(col1 INT);
   

5. View the definition of the test_tbl1 table.

   SHOW CREATE TABLE test_tbl1;
   

   The returned 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
   

References

• Create a table
• Best practices