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.

The following table summarizes the fragment-elimination behavior for different combinations of expression-based distribution schemes and query expressions. Partitions in fragmented tables do not affect the fragment-elimination behavior shown in the following table.

Table 1. Fragment elimination for different types of expression-based distribution schemes and query expressions
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.

This table shows that the distribution schemes enable fragment elimination, but the effectiveness of fragment elimination is determined by the WHERE clause of the specified query.

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

However, 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 affect fragment-elimination behavior.