Nonoverlapping fragments, multiple columns

The database server uses an arbitrary rule to define nonoverlapping fragments based on multiple columns.

The following figures show an example of nonoverlapping fragments on two columns.
Figure 1. Example of nonoverlapping fragments on two columns
...
FRAGMENT BY EXPRESSION
0<a AND a<=10 AND b IN (‘E', ‘F', ‘G') IN dbsp1,
0<a AND a<=10 AND b IN (‘H', ‘I', ‘J') IN dbsp2,
10<a AND a<=20 AND b IN (‘E', ‘F', ‘G') IN dbsp3,
10<a AND a<=20 AND b IN (‘H', ‘I', ‘J') IN dbsp4,
20<a AND a<=30 AND b IN (‘E', ‘F', ‘G') IN dbsp5,
20<a AND a<=30 AND b IN (‘H', ‘I', ‘J') IN dbsp6;
Figure 2. Schematic example of nonoverlapping fragments on two columns
This figure is described in the surrounding text.

If you use this type of distribution scheme, the database server can eliminate fragments on an equality search but not a range search. This capability can still be useful because all INSERT operations and many UPDATE operations perform equality searches. Avoid using a REMAINDER clause in the expression. If you use a REMAINDER clause, the database server cannot always eliminate the remainder fragment.

This alternative is acceptable if you cannot obtain sufficient granularity using an expression based on a single column.


Copyright© 2018 HCL Technologies Limited