How to resolve ORA-01408 Error Message in Oracle database

Learn the reason 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 got already listed this set of columns.

Resolution

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

Option #1

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

For example, if you had a desk called suppliers described as follows:

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

You’ve then created an index known as supplier_idx with the following command:

CREATE INDEX supplier_idx
  ON suppliers (supplier_id);

Then you’ve got tried to create a second index known as supplier_idx2 that uses the identical column as the first index.

CREATE INDEX supplier_idx2
  ON suppliers (supplier_id);

You would receive the following error message:

You will now not gain in performance by means of creating an index on the equal set of columns extra than once. Instead, you may additionally want to create an index that uses a exceptional set of columns (or a distinctive ordering of the columns).

For example, the following index would be efficaciously created:

CREATE INDEX supplier_idx3
  ON suppliers (supplier_id, supplier_name);