default_table_organization

Description

The default_table_organization parameter specifies the default table organization mode for newly created tables.

Privilege requirements

• Query the parameter

  The sys tenant and all user tenants can query the parameter by using the SHOW PARAMETERS statement or the GV$OB_PARAMETERS view.

• Modify the parameter

  The sys tenant and user tenants can modify the parameter.

Attributes

Considerations

The default_table_organization parameter allows you to flexibly control the default table organization mode for newly created tables. You can set the parameter to one of the following values:

• HEAP (heap-organized tables):

  • When you create a table with a primary key in this mode, the data is not sorted based on the order of the primary key column.
  • This mode is suitable for scenarios that require processing large amounts of data and performing complex query analysis.

• INDEX (index-organized tables):

  • When you create a table with a primary key in this mode, the data is sorted based on the order of the primary key column.
  • This mode is suitable for transaction processing scenarios that require fast queries and frequent updates.

By setting the default_table_organization parameter, you can choose the appropriate table organization mode based on your specific requirements to optimize system performance and improve data management efficiency.

Examples

1. View the current value of the default_table_organization parameter.

   SHOW PARAMETERS LIKE 'default_table_organization';
   

   The output 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. View the current value of the default_table_organization parameter.

   SHOW PARAMETERS LIKE 'default_table_organization';
   

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