DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Index » Db2 CREATE INDEX

Db2 CREATE INDEX

Summary: in this tutorial, you will learn how to use the Db2 CREATE INDEX statement to define a new index on a table.

Introduction to Db2 CREATE INDEX statement

To define an index for a table, you use the CREATE INDEX statement. The basic syntax of CREATE INDEX statement is the following:

CREATE INDEX index_name ON table_name(column1, column2, ...);

In this syntax:

  • First, specify the name of the index after the CREATE INDEX keywords.
  • Then, specify the table to which the index belongs and one or more columns included in the index.

Db2 CREATE INDEX examples

We’ll use the authors table from the sample database for the demonstration.

1) Using Db2 CREATE INDEX statement to create an index on one column example

This statement finds the authors whose last name is Anderson.

SELECT first_name, last_name FROM authors WHERE last_name = 'Anderson';

The query optimizer had to scan the whole authors able to locate the authors whose the last name is Anderson.

Here is the query plan of the query:

Db2 CREATE INDEX - scan for first name

To improve the speed of the query, you can create an index on the last_name column as follows:

CREATE INDEX ix_last_name ON authors(last_name);
Db2 CREATE INDEX - index on last name column

Once the ix_last_name index created, you can execute the query that finds the authors whose last name is Anderson again.

This time the query optimizer uses the ix_last_name to find the authors. As you can see, the cost of locating the authors is reduced significantly.

Db2 CREATE INDEX - use last name index

2) Using Db2 CREATE INDEX statement to create an index on multiple columns example

First, delete the idx_last_name by using the DROP INDEX statement:

DROP INDEX ix_last_name;

And then create a new index that includes both last_name and first_name columns:

CREATE INDEX ix_name ON authors(last_name, first_name);

The following query finds the author whose last name is Anderson and the first name is Thomas:

SELECT author_id, first_name, last_name FROM authors WHERE last_name = 'Anderson' AND first_name = 'Thomas';

The query optimizer can use the ix_name index for searching. Here is the query plan:

Db2 CREATE INDEX - query plan for an index on multiple columns

This query finds authors whose last name is Brown:

SELECT author_id, first_name, last_name FROM authors WHERE last_name = 'Amy'
Db2 CREATE INDEX - query plan for search value on leftmost column

The query optimizer also can use the index ix_name for searching because the last_name column is the leftmost column of the index.

However, if you find the authors by the first name, the query optimizer is not able to leverage the ix_name index:

SELECT author_id, first_name, last_name FROM authors WHERE first_name = 'Amy'
Db2 CREATE INDEX - query plan for search value on rightmost column

So, when you create an index on multiple columns, the order of the columns in the index definition is important. It is a good practice to place the columns that are often used in the WHERE clause or join condition at the beginning of the list.

In this tutorial, you have learned how to use the Db2 CREATE INDEX statement to create a new index on a table.

  • Was this tutorial helpful ?
  • YesNo
Next Db2 UNIQUE Index

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.