totn Oracle Error Messages

Oracle / PLSQL: 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:

Oracle PLSQL

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);