Forgot your password?
Document Actions
How does clustered Index work exactly in DB2?
Up to DB2
Posted by
Clustered and non-clustered indexes
Index
architectures are classified as clustered or non-clustered. Clustered
indexes are indexes whose order of the rows in the data pages correspond
to the order of the rows in the index. This is why only one clustered
index can exist in a given table, whereas, many non-clustered indexes
can exist in the table. In some relational database management systems,
the leaf node of the clustered index corresponds to the actual data,
not a pointer to data that resides elsewhere.
Both clustered
and non-clustered indexes contain only keys and record IDs in the
index structure. The record IDs always point to rows in the data pages.
The only difference between clustered and non-clustered indexes is
that the database manager attempts to keep the data in the data pages
in the same order as the corresponding keys appear in the index pages.
Thus the database manager will attempt to insert rows with similar
keys onto the same pages. If the table is reorganized, it will be
inserted into the data pages in the order of the index keys.
Reorganizing
a table with respect to a chosen index re-clusters the data. A clustered
index is most useful for columns that have range predicates because
it allows better sequential access of data in the table. This results
in fewer page fetches, since like values are on the same data page.
In
general, only one of the indexes in a table can have a high degree
of clustering.
Improving performance with clustering indexes
Clustering
indexes can improve the performance of most query operations because
they provide a more linear access path to data, which has been stored
in pages. In addition, because rows with similar index key values
are stored together, prefetching is usually more efficient when clustering
indexes are used.
as part of the table definition used with the CREATE TABLE statement.
Instead, clustering indexes are only created by executing the CREATE
INDEX statement with the CLUSTER option specified. Then the ALTER
TABLE statement should be used to add a primary key that corresponds
to the clustering index created to the table. This clustering index
will then be used as the table's primary key index.
PCTFREE in the table to an appropriate value using the ALTER TABLE
statement can help the table remain clustered by leaving adequate
free space to insert rows in the pages with similar values. For more information, see the ALTER TABLE statement
and Reducing the need to reorganize tables and indexes.
Generally,
clustering is more effectively maintained if the clustering index
is unique.
Hacker Rangers - Gamification for CyberSecurity