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:
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.