Oracle: ORA-01408 Error Message

ORA-01408 Error Message

Learn the cause and how to resolve the ORA-01408 error message in Oracle.

Description

When you encounter an ORA-01408 error, the following error message will appear:

• ORA-01408: such column list already indexed

Cause

You tried to create an index on a set of columns in a table, but you've already indexed this set of columns.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Correct your CREATE INDEX statement to reference a set of columns that have not already been indexed.

For example, if you had a table called suppliers defined as follows:

CREATE TABLE suppliers
( supplier_id number not null,
  supplier_name varchar2(50) not null
);

You've then created an index called supplier_idx with the following command:

CREATE INDEX supplier_idx
  ON suppliers (supplier_id);

Then you've tried to create a second index called supplier_idx2 that uses the same column as the first index.

CREATE INDEX supplier_idx2
  ON suppliers (supplier_id);

You would receive the following error message:

You will not benefit in performance by creating an index on the same set of columns more than once. Instead, you may wish to create an index that uses a different set of columns (or a different ordering of the columns).

For example, the following index would be successfully created:

CREATE INDEX supplier_idx3
  ON suppliers (supplier_id, supplier_name);