Using indexes to speed access to large tables

DB2 UDB for AS/400 provides two basic means for accessing tables: a table scan (sequential) and an index-based (direct) retrieval. Index-based retrieval is usually more efficient than table scan. However, when a very large percentage of pages are retrieved, table scan is more efficient than index-based retrieval.

If DB2 UDB for AS/400 cannot use an index to access the data in a table, it will have to read all the data in the table. Very large tables present a special performance problem: the high cost of retrieving all the data in the table. The following topics provide suggestions that will help you to design code which allows DB2 UDB for AS/400 to take advantage of available indexes:

• Coding for effective indexes: Avoid numeric conversions
• Coding for effective indexes: Avoid arithmetic expressions
• Coding for effective indexes: Avoid character string padding
• Coding for effective indexes: Avoid the use of like patterns beginning with % or _
• Coding for effective indexes: Be aware of the instances where DB2 UDB for AS/400 does not use an index

Additional information about using indexes:

See Coding for effective indexes: Using indexes with sort sequence for information about how indexes work with sort sequence tables.

See Examples of indexes for coding examples of effective indexes.