Important considerations for random chunk distributions

If you enable random chunk distributions, review these important considerations.

Good practices for loads and inserts

To take advantage of chunk distributions, users should load or insert data in one statement or nzload command. For example, if you are creating and loading a user table of 1000 rows, load the table in one statement. If your users perform inserts and updates to a table, in different statements or sessions, the system could allocate a new extent for the records from these different transactions and commands, rather than fill an already allocated extent for the table.

If you have multiple concurrent sessions that use the INSERT INTO VALUES syntax, the system will insert the rows to the same extent that was allocated for the table. The CTAS, nzload, and INSERT INTO … SELECT FROM syntax does not support parallel inserts into the same allocated extent, and so these commands will insert to different extents for the table.

If a small table is stored across multiple data slices/extents and allocates more space than it uses, consider using the CREATE TABLE AS/INSERT INTO command as a single operation to create a new random distribution table that takes advantage of the random chunk distribution. When you CTAS a table on a system configured for random chunk distribution, note that the CTAS operation takes longer because it requires additional time to process the records into chunks for writing to the allocated extent.

Restrictions for dropping databases

If you use this feature and create a database with more than 260,000 user tables and sequences, you cannot drop the database:
SYSTEM.ADMIN(ADMIN)=> drop database bigdb;
    ERROR:  DROP DATABASE: Database "BIGDB" has 310002 tables and/or sequences. 
        Objects must be manually dropped until the number is less than 260000

You must manually drop some of the tables or sequences until the count falls below 260,000, and then you can drop the database.

Additional restrictions and considerations

In a replication environment, the main and subordinate must be on the same release to use the random chunk distribution feature.

If you enable the feature and then disable it, the table data layout does not automatically change. Also, if you perform the same LOAD, INSERT, and CTAS operations with the feature enabled and with the feature disabled, the data layouts are different.

If you enable random chunk distribution and load a large amount of data, a data skew might occur, which might affect performance.