Temporary Indexes and Permanent Indexes (iSeries)

The query optimizer can dynamically create temporary indexes to execute queries. Indexes can be created to execute queries that specify joins, ordering or grouping.

There are two ways of creating indexes:

  • An index can be created over a file.
  • An existing index can be used to speed up selection during the creation of the new index. This type of index create is referred to as an index-from-index create.

When the query optimizer creates a temporary index, this index can only be used for the query and work process (job) that initiated it. When the associated cursor is fully closed, the system deletes the index.

Indexes created by the query optimizer are sparse indexes. This means that all selection that can be applied at the time the index is created will be built into the index. These indexes, once created, usually perform very well. However, there is some up-front cost involved in creating the index.

The query optimizer indicates which key fields were used to create the temporary index and may recommend to create a permanent index. A permanent index could be created to avoid temporary index to be created the next time the query is run.

Before you create a permanent index, consider the cost of creating and maintaining a permanent index compared to the savings made by running the query with the permanent index before creating any additional indexes.