Home | Previous Page | Next Page   Planning Table and Index Fragmentation >

Choosing Fragmentation Schemes

When you choose a fragmentation method, consider the following two main performance goals:

The way in which your applications use the database server determine the priority that you assign to these goals, as described in Determining Performance Goals.

The following table compares round-robin, expression-based, hash, hybrid, and range fragmentation schemes for three important features: ease of data balancing, fragment elimination, and data skip.

Table 2. Comparison of Fragmentation Schemes.
Fragmentation
Scheme
Ease of Data
Balancing
Fragment Elimination Data Skip
Round-robin The database server automatically balances data over time. The database server cannot eliminate fragments. You cannot determine if the integrity of the transaction is compromised when you use the dataskip feature. You can insert rows into a table that is fragmented by round-robin if any table fragment is accessible, even though some fragments are not available.
Expression-based You must know the data distribution to balance the number of rows in fragments. If expressions on one or two columns are used, the database server can eliminate fragments for queries that have either range or equality expressions. You can determine whether the integrity of a transaction has been compromised when you use the dataskip feature. You cannot insert rows if the fragment for those rows is not available.
Hash The database server does not necessarily balance data over time. If the fragmentation column contains unique, well distributed values, data is usually approximately balanced. The database server can eliminate fragments for queries that have equality expressions. You cannot determine if the integrity of the transaction is compromised when you use the dataskip feature. You cannot insert rows if the fragment for those rows is not available.
Hybrid You must know the data distribution and queries run against the table to balance the table I/O across the dbslice. Unless the hash fragmentation column contains evenly distributed unique values, hash-fragmented tables do not necessarily completely balance data. The database server can eliminate fragments for range or equality statements on the base-level expression strategy or equality expressions on the secondary-level hash strategy. You can determine whether the integrity of a transaction has been compromised when you use the dataskip feature. You cannot insert rows if the fragment for those rows is not available.
Range Data balancing requires dense, uniform, non-duplicate distribution of fragmentation column data. The database server can eliminate fragments for queries with equality and range expressions. You can determine whether the integrity of a transaction has been compromised when you use the dataskip feature. You cannot insert rows if the fragment for those rows is not available.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]