DB2 has a provision to create indexes dynamically. During the query execution, indexes will create dynamically. And they will delete after the query execution completes.
Precisely, there is no syntax to create dynamic indexes. See here how DB2 will take care of it.
Indexes in DB2
When querying data from the database, you must utilize indexes wherever possible. Which then increases performance quite dramatically.
You do NOT need to fetch columns that have an index on them. Instead, try to utilize them wherever possible in the SQL WHERE clause.
For instance, if the EMPNOcolumn has an index on it, you should attempt to utilize the index in your queries:
SELECT firstnme, midinit, lastnme, dept
FROM emp
WHERE empno > 10000
The preceding statement will now utilize the index to locate the employee row(s) without a full table scan. The column EMPNO has an index on it; thus, Db2 can use it to locate the rows to be returned.
Note that it is not necessary to retrieve the indexed column to utilize its index. A reference in the WHERE clause is sufficient to prevent a full table scan.
Dynamic Indexes
Also, you should be aware that DB2 can create an index on the fly to help query a table or tables.
For instance, the following WHERE clause will cause dynamic to create dynamic indexes on the salary, bonus, and comm columns to help reduce the access time for the data:
SELECT empno, firstnme, lastnme
FROM emp
WHERE salary + bonus + comm > 100000.00;
These indexes are only temporary, and they will delete when the query is complete.
Related posts
Srinimf 