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

Index Fragmentation Guidelines

This section describes the implications of various index choices, how the fragmentation of the table might determine the fragmentation of the index, and the advantages and disadvantages of certain kinds of index fragmentation. To fragment an index, you can use an method except round-robin.

For general information about how to create indexes and index costs and management, see Planning and Managing Indexes For information about tuning indexes, see Create Appropriate Indexes for Queries and Create Appropriate Indexes for Transactions.

Important:
Run DSS application tests without indexes before you decide what indexes to create. You might find that you do not need indexes for good DSS performance.

Attached Indexes

An attached index is fragmented in the same way as the underlying table, with the same fragmentation scheme, and is stored in the same dbspaces but in different tablespaces. If the index will be used to access a table fragment that is stored in the same dbspace, create an attached index.

Attached Index on an Unfragmented Table

An attached index on an unfragmented table does not provide fragment elimination or increase parallel processing, but it might provide quick lookups for OLTP applications.

Attached Index on a Fragmented Table

If you create an attached index on a fragmented table, the index pages are tightly coupled to the data fragments they point to.

Attached indexes on fragmented tables are usually used for large OLTP applications and DSS environments when the index fragmentation does not need to be different from the table fragmentation.

Tip:
Attached indexes are included with the table when you use the ALTER FRAGMENT ON TABLE... ATTACH statement to add a table fragment to an existing table. For this reason, attached indexes do not have to be rebuilt when you update a table by attaching a fragment.

For example, consider the following sales table, fragmented by week. The table contains 100 weeks with 1 million rows of data for each week.

Create table sales(
   week  int
   dept  int
   qty   int
   price decimal)
fragment by expression
week = 1 in dbs1,
week = 2 in dbs2 . . .;

Consider that you run the following query:

Select sum (price*qty)
from sales
where week = 2 and dept = 10;

Assuming fifty departments with evenly distributed data, if the database server scans this table, it eliminates ninety-nine fragments, leaving one fragment with one million rows to filter. Add the following index on the dept column, fragmented the same way as the table:

Create index dept_idx on sales(dept);

Use of the index can eliminate ninety-nine index fragments, leaving one index fragment for department 10. Because a single department contains two percent of the data, the database server uses the index to access that two percent of the one million rows of table data in the fragment for week 2, which is 20,000 index lookups. Accessing significantly fewer than 1 million rows is required.

Detached Indexes

A detached index is either fragmented differently from the underlying table or unfragmented if the table is fragmented. It can be stored in any specified dbspace or dbslice.

Locally detached index fragments are stored on the same coserver as the corresponding table fragments. Locally detached indexes are faster than globally detached indexes, even for SELECT statements, because they do not incur messaging costs. Maintenance costs for locally detached indexes are also lower than for globally detached indexes.

Globally detached index fragments are stored on different coservers from the table fragments. In a globally detached index, the index is fragmented in such a way that the index might point to table fragments that are not on the same coserver. If you specify a fragmentation scheme for an index, you might inadvertently create a globally detached index.

Globally detached indexes increase maintenance costs for deletes, inserts, and updates of the index key column data. If the underlying table rarely changes, these costs should be insignificant, but the intercoserver messaging cost of using a globally detached index is high.

A globally detached index might take advantage of fragment elimination or reduced disk I/O to improve performance of queries or transactions with the following requirements:

In all other cases, an unfragmented index or a locally detached index provides better performance.

Important:
You cannot create a FOR EACH ROW trigger on a table that has a globally detached index.

If you would like the server to warn you whenever a globally detached index is created, set the GLOBAL_DETACH_INFORM environment variable to any value before starting your server. This variable can also be turned on and off with the server on-line using the onutil SET command.

Detached Unfragmented Index on an Unfragmented Table

Detached unfragmented indexes on unfragmented tables may be placed in any dbspace.

Such an index improves performance if index and data pages are placed in dbspaces on separate disks, especially if the index and data pages are often used together and index scans result in data-page reads.

Detached Fragmented Index on an Unfragmented Table

A detached fragmented index provides advantages similar to those of an attached index on a fragmented table.

Detached Unfragmented Index on a Fragmented Table

You might choose an unfragmented index on a fragmented table for very selective queries in a DSS environment in which scans are usually preferred. The fragmented table can be scanned in parallel, but the index cannot. At the same time, a multi-column specialized index might dramatically improve performance of specific queries.

Detached Fragmented Index on a Fragmented Table

You might create a detached fragmented index on a fragmented table for a workload of mixed OLTP and DSS applications. You might fragment the data for DSS applications, and fragment the indexes for OLTP applications. A detached fragmented index on a fragmented table might also improve selective queries and non-selective queries on different columns in a DSS environment.

Such a scenario provides the following advantages:

To determine whether a detached fragmented index on a fragmented table is a good choice, consider the index maintenance issues, especially if the index is globally detached.

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]