A fragmentation rule that creates nonoverlapping fragments
on a single column is the preferred fragmentation rule from a fragment-elimination
standpoint.
The advantage of this type of distribution scheme is that the database
server can eliminate fragments for queries with range expressions
as well as queries with equality expressions. You should meet these
conditions when you design your fragmentation rule. Example of nonoverlapping fragments on a
single column gives an example of this
type of fragmentation rule. Figure 1: Example of nonoverlapping fragments on a
single column
...
FRAGMENT BY EXPRESSION
a<=8 OR a IN (9,10) IN dbsp1,
10<a AND a<=20 IN dbsp2,
a IN (21,22,23) IN dbsp3,
a>23 IN dbsp4;
You can create nonoverlapping fragments using a range rule or an
arbitrary rule based on a single column. You can use relational operators,
as well as AND, IN, OR, or BETWEEN. Be careful when you use the BETWEEN
operator. When the database server parses the BETWEEN keyword, it
includes the end points that you specify in the range of values. Avoid
using a REMAINDER clause in your expression. If you use a REMAINDER
clause, the database server cannot always eliminate the remainder
fragment.