Fragmentation Strategy

The are two fragmentation strategies are, round-robin and expression based. With expression based fragmentation the query optimiser will, if possible, remove data fragments from a query plan. With round-robin this is not possible.

Fragmentation by expression should be considered if

• Non-overlapping fragments can be created on a single column, then fragments can be eliminated with range or equality expressions.
  
  
• There is a high degree of selectivity in the query then expression based fragmentation should be considered. If, in general, the majority of the rows of the table are examined, then fragmenting by round robin might produces benefits.
  
  
• The data access is not evenly distributed. By fragmenting by expression it is possible to create uneven distributions of data in your fragments allowing the regularly accessed data to be fragmented over many dbspaces. The rarely or infrequently accessed data can be held within a single fragment. For example, if the system has a large lookup table where only a small proportion (10% say) of the rows are normally accessed, then this 10% of the table can be fragmented over many dbspaces, while the remaining rows are held in a single dbspace.
  
  

The ideal fragmentation strategy is to create non-overlapping fragments on a single column that allows fragments to be eliminated. either a range or equality expression. However, there are two other strategies that will eliminate fragments to be scanned when using an equality search. For theses two types of strategies, fragments cannot be eliminated on a range search.

A non-contiguous scheme means that some data values cannot be accounted for. The fragmentation expression can use any range, hash or arbitrary rule to define overlapping fragments based on a single column.

The fragmentation expression uses a range or arbitrary rule to define non-overlapping fragments based on multiple columns.