Recommendations for Creating Indexes

• To avoid mismatch problems, create an index from the environment that is to use it to query the data. This will ensure that the definitions used to create the index, match those used to query the data. However, if you can be confident that the SQL catalog definitions exactly match the English data definition items, then there should be no problem using indexes created in one environment to optimise queries in the other.
• Create indexes that are helpful and relevant to the type of queries that you regularly carry out. For example, if most of your query selection criteria are based on two or three columns (for example, ENAME and DEPTNO), create indexes based on these columns.
• Do not create indexes based on a column that only has a small number of different values. An extreme example of this is a column that only holds two values, for example 'Yes' and 'No', or 'Male' and 'Female'.
• Ensure that the current SQL table/column definitions used in the SQL query have not changed since current indexes were created.

• Where possible you should avoid referencing column definition items that return data such as the current date or a file translation, where the data in the translate file might change, as these can produce an inconsistent index.

  However, in the case of translate files, these arrangements are acceptable so long as either static data is used or proper management is exercised. For instance when relevant changes to the translate files are expected the index should be deleted, prior to the changes, and the index re-built. It is perhaps worth mentioning that it is perfectly ok to add new translation items to the file. It is the act of changing current translation items that causes a problem.

  The consequence of ignoring these points could be an invalid index. This would seriously affect the integrity of your query or selection thereby producing inaccurate query or selection results.

  Evidence of an invalidated index could be detected by using VERIFY-INDEX.