How to Create Dynamic Indexes in DB2

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.

DB2 dynamic index

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

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.