Fragmentation by ROUND ROBIN

For a table that uses a round-robin distribution scheme, the rows that the database server stores in an insert or load operation are distributed cyclically among a user-defined number of fragments, so that the number of rows inserted into each fragment is approximately the same (±100).

Round-robin distributions are also called even distributions, because the design goal of this strategy is for an evenly balanced distribution among the fragments. To that end, a newly added round-robin fragment will be favored exclusively by inserts and loads until it no longer has the fewest number of rows among the table’s fragments.

The syntax for defining round-robin interval fragmentation requires that you specify at least two round-robin fragments in one of two forms. This form defines round-robin fragments and declares a name for each fragment:

   FRAGMENT BY ROUND ROBIN  
      PARTITION partition IN dbspace,
      . . . 
      PARTITION partition IN dbspace

As in other fragmentation schemes, each PARTITION partition specification declares the name of a fragment, which must be unique among the names of fragments of the same table. The dbspace specification can be different for each fragment, or some fragments (or all of the fragments) can be stored in separate named partitions of the same dbspace. Each partition is the name of a round-robin fragment.

This alternative form defines round-robin fragments with no explicit name:

   FRAGMENT BY ROUND ROBIN IN dbspace_list              

Here the dbspace_list specification is a comma-separated list of at least 2 (but no more than 2048) dbspaces, each of which stores a single round-robin fragment. No dbspace can appear more than once in this list. (In the system catalog, the sysfragments.partition column stores the identifier of the fragment. For fragments defined without the PARTITION keyword, the partition value is the identifier of the dbspace where the fragment is stored. For this reason, a repeated dbspace in dbspace_list violates a uniqueness requirement for names of fragments of the same table.)

A round-robin distribution scheme must be defined by only one or the other of these two syntax forms.

A table that is fragmented by round-robin has no fragment key, no fragment expressions, and no REMAINDER fragment. (An alternative description is that every round-robin fragment resembles a remainder fragment, because no fragment expressions are defined to match a fragment key for the inserted rows. But the REMAINDER keyword is not valid in the SQL syntax to define a round-robin distribution strategy.)

Because no fragment expressions are evaluated when the database server loads new rows into round-robin fragments, this strategy provides the best performance for insert operations.

Only tables, not indexes, can be defined with round-robin fragmentation. For performance reasons, any indexes that you define on a table that is fragmented by round-robin should be nonfragmented indexes.

Because a round-robin distribution strategy has no fragment key and no fragment expressions, you cannot explicitly define a NULL round-robin fragment. When rows with missing data are loaded into a table by round-robin, the rows with NULL values are stored wherever the database server happens to insert them as it approximately equalizes the number of inserted rows for every fragment.

By design, the GRANT FRAGMENT and REVOKE FRAGMENT statements of SQL cannot reference round-robin fragments. Because each fragment stores a quasi-random subset of the rows, the DBA cannot predict which rows will be stored in a given round-robin fragment. If some rows contain unencrypted sensitive information, table-level (rather than fragment-level) is a more appropriate granularity for granting or withholding discretionary access privileges in databases that do not implement label-based (LBAC) security policies.

Because round-robin fragments are uncorrelated with data values, queries of tables that are fragmented by round-robin cannot benefit from fragment elimination. Round-robin distribution schemes are useful for balancing the rows in a set of table fragments across multiple devices, but other storage distribution schemes are typically used in data warehouse applications that query dimensional tables, because the performance advantages of round-robin in loading data are more than offset by slower data retrieval from round-robin fragments.