DB2 Indexes: Here are the Top Rules to Create

An index is a database object. That uses to speed up the retrieval of tuples from base relations. 

The index is independent of the table it indexes. It stores address pointers to tuples of the base relation. 

Once created, indexes are maintained by the DBMS automatically and used to service certain user access requests.

The software engineer’s (or DBA’s) responsibility is to create them. Check out here how to create an index.

IN THIS PAGE

1. When you need index on a Table
2. When index is not needed

When you need index on a Table

• A column or combination of columns that use very often in system query conditions (join conditions).
• A column contains a wide range of values.
• A column contains a large number of null values.
• The table is large and most SQL queries expect to retrieve less than 2 % – 4% of the tuples.
• A column or combination of columns is frequently used in the Order-By clause of queries.

When index is not needed

• The table is small.
• Most queries are expected to retrieve more than 2% – 4% of the tuples.
• The table is frequently updated (there are exceptions to this rule).
• Columns are not often used in database queries.

Related