Custom reports often summarize data extracted from large volume tables.  If the SQL statement used to extract the data from the large volume table is designed to use the table's primary index or a secondary index performance will dramatically.  The developer should check all SQL statements and attempt to conform to the available indexes.

BASIC TABLE REQUIREMENTS

• A database table has a primary index.  It consists of the key fields of the table.
• A database table can further have secondary indices. To support a WHERE clause on a non-primary key, a secondary index is created.

 CODING REQUIREMENTS

• An index can be used by the database if

• Index fields are specified in the WHERE clause
• All previous fields of a composite (concatenated) index are specified.

• An index will not be used if the following operators are present on the indexed fields in your where clause

• NOT or NE or <>
• LIKE ‘%pattern’
• IS NULL
• IS NOT NULL
• NOT IN

NEW INDEX

If there is no key field available for a frequently used table and the size of the table is significant, consider requesting a secondary index or view or matchcode according to the available fields and needs.  This will be a DDIC request and will be carefully studied by DDIC team.  Resist adding indexes and views to large non-custom tables as far as possible. 

Related Items:

• Internal Tables and Types
• Performance guidelines on Indi
• Using Index tables to improve