13.1 Items to be examined during index design

An index is created to improve table processing performance. However, a poorly designed index can have an adverse effect on performance. You should examine the methodology for creating effective indexes. Also, table processing performance and operability vary depending on the method used to store indexes in user RDAREAs. You should take these points into account when designing an index.

The following table lists items to consider when you are designing an index.

Table 13-1 Items to consider when you are designing an index

Design task and items to be examinedAdvantagesDisadvantagesSection
Index creationTable search performance is improved.As the number of indexes created increases, overhead for index update processing also increases.13.2
Index row partitioningTable storage RDAREAs and index storage RDAREAs can be managed on a one-by-one basis, thereby improving utilities' operability.If a non-partitioning key index is partitioned, the performance of a search using an index is reduced.13.3
Creation of plug-in indexIf a plug-in index is created in a column defined as an abstract data type using the index type provided by a plug-in, table search performance is improved.As the number of indexes created increases, overhead for index update processing also increases.13.4
Plug-in index row partitioningUser LOB RDAREAs can be handled independently during batch index creation.Row partitioning results in an increase in RDAREAs. When backing up a database with RDAREAs specified or when reorganizing the database, note that the table and index have a one-to-one correspondence.13.5