Oracle ORA-01408 Error: Causes and Solutions Complete Guide

ORA-01408: Such Column List Already Indexed

ORA-01408 is thrown by Oracle when you attempt to create an index on a column or combination of columns that is already covered by an existing index on the same table. In simple terms, Oracle does not allow two indexes with identical leading column lists on the same table. This error is common in team environments, repeated script executions, and migration workflows where existing schema objects are not verified before running DDL statements.

---

Top 3 Causes

1. Attempting to Create a Duplicate Index Explicitly

The most straightforward cause: an index already exists on the exact same column list, and a developer tries to create another one without checking first.

-- First index already exists
CREATE INDEX IDX_EMP_DEPT ON EMP (DEPT_ID);

-- Running this again causes ORA-01408
CREATE INDEX IDX_EMP_DEPT_DUP ON EMP (DEPT_ID);
-- ERROR: ORA-01408: such column list already indexed

-- Check existing indexes before creating
SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = 'EMP'
ORDER BY INDEX_NAME, COLUMN_POSITION;

2. Conflict with Indexes Auto-Created by Constraints

Oracle automatically creates an underlying index when you define a PRIMARY KEY or UNIQUE constraint. If you then try to manually create an index on the same column(s), ORA-01408 is raised.

-- PK constraint already created an index on EMP_ID
ALTER TABLE EMP ADD CONSTRAINT PK_EMP PRIMARY KEY (EMP_ID);

-- This will fail with ORA-01408
CREATE INDEX IDX_EMP_ID ON EMP (EMP_ID);

-- Query to find constraint-backed indexes
SELECT c.CONSTRAINT_NAME, c.CONSTRAINT_TYPE, c.INDEX_NAME, cc.COLUMN_NAME
FROM USER_CONSTRAINTS c
JOIN USER_CONS_COLUMNS cc ON c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE c.TABLE_NAME = 'EMP'
  AND c.CONSTRAINT_TYPE IN ('P', 'U');

3. Non-Idempotent Deployment Scripts Running Multiple Times

In CI/CD pipelines, running the same migration script twice without existence checks will succeed the first time and fail on subsequent runs with ORA-01408.

-- Idempotent index creation using PL/SQL
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM USER_INDEXES
    WHERE INDEX_NAME = 'IDX_EMP_DEPT_JOB'
      AND TABLE_NAME  = 'EMP';

    IF v_count = 0 THEN
        EXECUTE IMMEDIATE
            'CREATE INDEX IDX_EMP_DEPT_JOB ON EMP (DEPT_ID, JOB_ID)';
        DBMS_OUTPUT.PUT_LINE('Index created successfully.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Index already exists. Skipping.');
    END IF;
END;
/

---

Quick Fix Solutions

Drop the duplicate index and recreate if needed:

-- Identify which index to keep, then drop the redundant one
DROP INDEX IDX_EMP_DEPT_DUP;

-- If a new index with different characteristics is needed,
-- differentiate by column order or additional columns
CREATE INDEX IDX_EMP_JOB_DEPT ON EMP (JOB_ID, DEPT_ID);

Use exception handling to gracefully skip ORA-01408:

DECLARE
    already_indexed EXCEPTION;
    PRAGMA EXCEPTION_INIT(already_indexed, -1408);
BEGIN
    EXECUTE IMMEDIATE
        'CREATE INDEX IDX_EMP_DEPT_JOB ON EMP (DEPT_ID, JOB_ID)';
EXCEPTION
    WHEN already_indexed THEN
        DBMS_OUTPUT.PUT_LINE('Index already exists on this column list. Skipping.');
END;
/

---

Prevention Tips

1. Always audit indexes before running DDL.
Make it a standard practice to query USER_INDEXES and USER_IND_COLUMNS before any index creation, especially during migrations or deployments. The query below helps identify duplicate column lists across your schema:

-- Detect indexes with identical column lists on the same table
SELECT a.TABLE_NAME, a.INDEX_NAME AS IDX_A, b.INDEX_NAME AS IDX_B, a.COL_LIST
FROM (
    SELECT TABLE_NAME, INDEX_NAME,
           LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_POSITION) AS COL_LIST
    FROM USER_IND_COLUMNS
    GROUP BY TABLE_NAME, INDEX_NAME
) a
JOIN (
    SELECT TABLE_NAME, INDEX_NAME,
           LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_POSITION) AS COL_LIST
    FROM USER_IND_COLUMNS
    GROUP BY TABLE_NAME, INDEX_NAME
) b ON a.TABLE_NAME = b.TABLE_NAME
    AND a.COL_LIST   = b.COL_LIST
    AND a.INDEX_NAME < b.INDEX_NAME;

2. Write idempotent scripts and use migration tools.
Always wrap index creation DDL in existence-check logic, or adopt tools like Flyway or Liquibase that track which scripts have already been applied. This structurally eliminates the risk of running the same DDL twice and prevents ORA-01408 from appearing in automated pipelines altogether.

---

Related Oracle Errors

• ORA-00955 – Object name already used by an existing object (duplicate object names).
• ORA-02261 – A UNIQUE or PRIMARY KEY constraint already exists on the same column(s).
• ORA-01430 – Column being added already exists in the table.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.