Enterprise Edition Home | Express Edition Home | Previous Page | Next Page   Fragmentation Guidelines > Using Distribution Schemes to Eliminate Fragments >

Effectiveness of Fragment Elimination

The database server cannot eliminate fragments when you fragment a table with a round-robin distribution scheme. Furthermore, not all expression-based distribution schemes give you the same fragment-elimination behavior.

Table 9 summarizes the fragment-elimination behavior for different combinations of expression-based distribution schemes and query expressions. Partitions in fragmented tables do not effect the fragment-elimination behavior shown below.

Table 9. Fragment Elimination for Different Categories of Expression-Based
Distribution Schemes and Query Expressions
Type of Expression-Based Distribution Scheme
Type of Query (WHERE clause) Expression Nonoverlapping Fragments on a Single Column Overlapping or Non-contiguous Fragments on a Single Column Nonoverlapping Fragments on Multiple Columns
Range
expression
Fragments can be eliminated. Fragments cannot be eliminated. Fragments cannot be eliminated.
Equality expression Fragments can be eliminated. Fragments can be eliminated. Fragments can be eliminated.

Table 9 indicates that the distribution schemes enable fragment elimination, but the effectiveness of fragment elimination is determined by the WHERE clause of the query in question.

For example, consider a table fragmented with the following expression:

FRAGMENT BY EXPRESSION
100 < column_a AND column_b < 0 IN dbsp1,
100 >= column_a AND column_b < 0 IN dbsp2,
column_b >= 0 IN dbsp3

The database server cannot eliminate any fragments from the search if the WHERE clause has the following expression:

column_a = 5 OR column_b = -50

On the other hand, the database server can eliminate the fragment in dbspace dbsp3 if the WHERE clause has the following expression:

column_b = -50

Furthermore, the database server can eliminate the two fragments in dbspaces dbsp2 and dbsp3 if the WHERE clause has the following expression:

column_a = 5 AND column_b = -50

Partitions in fragmented tables do not effect fragment-elimination behavior.

The following sections discuss distribution schemes to fragment data to improve fragment elimination behavior.

Nonoverlapping Fragments on a Single Column

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. It is recommended that you meet these conditions when you design your fragmentation rule. Figure 43 gives an example of this type of fragmentation rule.

Figure 43. 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.

Overlapping Fragments on a Single Column

The only restriction for this category of fragmentation rule is that you base the fragmentation rule on a single column. The fragments can be overlapping and noncontiguous. You can use any range, MOD function, or arbitrary rule that is based on a single column. Figure 44 shows an example of this type of fragmentation rule.

Figure 44. Example of Overlapping Fragments on a Single Column
...
FRAGMENT BY EXPRESSION
a<=8 OR a IN (9,10,21,22,23) IN dbsp1,
a>10 IN dbsp2;

If you use this type of distribution scheme, the database server can eliminate fragments on an equality search but not a range search. This distribution scheme can still be useful because all INSERT and many UPDATE operations perform equality searches.

This alternative is acceptable if you cannot use an expression that creates nonoverlapping fragments with contiguous values. For example, in cases where a table is growing over time, you might want to use a MOD function rule to keep the fragments of similar size. Expression-based distribution schemes that use MOD function rules fall into this category because the values in each fragment are not contiguous.

Nonoverlapping Fragments, Multiple Columns

This category of expression-based distribution scheme uses an arbitrary rule to define nonoverlapping fragments based on multiple columns. Figure 45 and Figure 46 show an example of this type of fragmentation rule.

Figure 45. 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 46. Schematic Example of Nonoverlapping Fragments on Two Columns
begin figure description - This figure is described in the surrounding text. - end figure description

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.

Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]