default_table_organization

Description

The default_table_organization parameter allows you to set the default table organization method for tables created in a tenant.

Privilege requirements

• Query the parameter

  You can use the SHOW PARAMETERS statement or the GV$OB_PARAMETERS view to query the parameter in the sys tenant or any user tenant.

• Modify the parameter

  You can modify the parameter in the sys tenant or any user tenant.

Attributes

Considerations

You can use the default_table_organization parameter to flexibly control the default table organization method for tables created in a tenant. This parameter can be set to either of the following values:

• HEAP (heap-organized table):

  • When this method is used to create a table with a primary key, the data is not stored in order by the primary key column.
  • This method is suitable for scenarios that involve handling large amounts of data and performing complex query analysis.

• INDEX (index-organized table):

  • When this method is used to create a table with a primary key, the data is stored in order by the primary key column.
  • This method is suitable for transaction processing scenarios that require quick queries and frequent updates.

You can set the default_table_organization parameter to a suitable table organization mode based on your business needs, to optimize system performance and improve data management efficiency.

Examples

1. Query the current information about the default_table_organization parameter.

   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 set
   

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

   ALTER SYSTEM SET default_table_organization = 'HEAP';
   

3. Query the current information about the default_table_organization parameter.

   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 set
   

4. Create a test table named test_tbl1.

   CREATE TABLE test_tbl1(col1 INT);
   

5. Query the definition of the test_tbl1 table.

   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
   

References

• Create a table
• Best practices