Tuning

A remainder fragment is always scanned. It is always possible to avoid a remainder clause

Ensure the chosen fragmentation strategy doesn't generate an I/O bottleneck.

Complex fragmentation expressions take a longer time to be be evaluated than simple expressions.

When fragmentation expression is parsed it is examined in order and one clause at a time. The first match cause the parser to skip the rest of the expression. Therefore, place the most restrictive part of the expression first. For example, to insert the value 25000, six evaluations are performed:

col1 >=  1000 and col1 <= 10000 in dbspacel,
col1 >  10000 and col1 <= 20000 in dbspace2,
col1 >  20000 and col1 <= 30000 in dbspace3

In the re-arranged expression, four evaluations are performed:

col1 <= 10000 and col1 >=  1000 in dbspacel,
col1 <= 20000 and col1 >  10000 in dbspace2,
col1 <= 30000 and col1 >  20000 in dbsPace3

Avoid datatype conversions

Avoid DATE and DATETIME expressions as these will be converted to an integer for the expression evaluation. If the expression must be DATE or DATETIME based consider using a dummy INTEGER column on the table to avoid the conversion.

If data is regularly loaded into the table consider placing the most commonly accessed fragment first in the fragmentation statement.