When planning for the use of indexes in your application database, there are some important considerations to make.
- Always be sure that the database architect fully understands performance issues of the DBMS which will be deployed when creating indexes. For example, on the IBM i, the use of immediate access path maintenance has performance implications when an IPL is performed or when saving objects.
- Also on the IBM i, LANSA supports the use of OPNQRYF. OPNQRYF provides extremely powerful and flexible methods of creating dynamic views over tables.
- In general, it is a good practice to limit the number of indexes you create. Indexes should reflect frequently used access techniques for the tables.
- RDML has many powerful table access techniques which can reduce the need for indexes. Be sure to review the capabilities of the RDML commands as part of your database design and requirements assessment.
- For RDMLX tables, the use of Select/Omit Criteria is not recommended. Instead, use the WHERE parameter to limit the data retrieved.