---
title: "DB2: Unlock the Rules for Efficient Creating Indexes"
description: "Indexes in the database speed up the pulling of rows. Here are the top rules for when to create and when not to create."
url: https://srinimf.com/2023/10/29/indexes-in-database-top-rules/
date: 2023-10-29
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2022/12/indexes-in-database.jpg?fit=2240%2C1260&ssl=1
categories: ["DB2", "SQL"]
tags: ["Table indexes"]
type: post
lang: en
---

# DB2: Unlock the Rules for Efficient Creating Indexes

The index is a database object that improves performance when you optimize it on the table. Here are some rules that can help you create efficient indexes. An index is used to speed up the retrieval of rows from the database table.

Related[DB2 Indexes Type 1 Vs. Type 2](https://srinimf.com/2010/07/24/db2-index/)

IN THIS PAGE

Related[DB2 Creating Index: 6 Thumb Rules](https://srinimf.com/2015/02/09/rules-for-creating-index-db2/)

1. [What is an index](https://srinimf.com/2023/10/29/indexes-in-database-top-rules/#what-is-an-index)
2. [Rules to create an index: When is needed](https://srinimf.com/2023/10/29/indexes-in-database-top-rules/#rules-to-create-an-index-when-is-needed)
3. [When an index is not needed](https://srinimf.com/2023/10/29/indexes-in-database-top-rules/#when-an-index-is-not-needed)

![DB2 indexes](https://i0.wp.com/srinimf.com/wp-content/uploads/2022/12/pexels-photo-221004.jpeg?resize=1880%2C1253&ssl=1)*Photo by Pixabay on Pexels.com*

## What is an index

The index is separate from the table it indexes. It contains pointers to the data in the table. The database management system (DBMS) automatically maintains the indexes and uses them to fulfill certain user queries. It is the responsibility of the software engineer or database administrator (DBA) to create indexes. Visit [this link](https://srinimf.com/2020/09/13/db2-unique-index-vs-clustered-index/) to learn how to create an index.

## Rules to create an index: When is needed

- In SQL queries, a particular column or combination of columns is used frequently.
- The column has a wide range of values.
- The column also contains a large number of null values.
- The table is large, and most queries only expect a small percentage (2% – 4%) of the rows.
- In queries, the mentioned column or combination of columns is often used in the Order-by-clause.

## When an index is not needed

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

Related

- [SQL Index Vs Database Index: Top Differences](https://srinimf.com/2016/10/08/the-best-differences-sql-index-and-database-index/)DateOctober 8, 2016
- [DB2 Creating Index: 6 Thumb Rules](https://srinimf.com/2015/02/09/rules-for-creating-index-db2/)DateFebruary 9, 2015
- [DB2 Indexes Type 1 Vs. Type 2](https://srinimf.com/2010/07/24/db2-index/)DateJuly 24, 2010

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2023%2F10%2F29%2Findexes-in-database-top-rules%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2022%2F12%2Findexes-in-database.jpg%3Ffit%3D1200%252C675%26ssl%3D1&description=DB2%3A%20Unlock%20the%20Rules%20for%20Efficient%20Creating%20Indexes)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2023/10/29/indexes-in-database-top-rules/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2023/10/29/indexes-in-database-top-rules/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2023/10/29/indexes-in-database-top-rules/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2023/10/29/indexes-in-database-top-rules/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%3A%20Unlock%20the%20Rules%20for%20Efficient%20Creating%20Indexes&body=https%3A%2F%2Fsrinimf.com%2F2023%2F10%2F29%2Findexes-in-database-top-rules%2F&share=email)
-

### Like this:

Like Loading…