Oracle Exact Duplicate Index Finder

Purpose

Identifies indexes on the same table that have identical key column lists in identical positional order. Duplicate indexes in Oracle impose extra overhead on every DML operation against the indexed table and consume additional segment space in the tablespace. Because Oracle enforces PRIMARY KEY and UNIQUE constraints using backing indexes, the output includes constraint type information to assist with the decision on which duplicate to retain.

Oracle B-tree indexes do not have an equivalent to SQL Server’s INCLUDE columns or PostgreSQL’s INCLUDE clause. All key columns are full B-tree key entries, so the comparison is purely on the ordered key column list.

The script queries USER_IND_COLUMNS and USER_INDEXES. To scan another schema’s indexes, replace with ALL_IND_COLUMNS / ALL_INDEXES and add an OWNER filter — see the execution guidance below.

Supported Platforms and Versions

Oracle Database 19c, 21c, and 23ai (on-premises and Oracle Cloud Infrastructure). Uses USER_IND_COLUMNS and USER_INDEXES — standard catalog views available in all Oracle Database editions since Oracle 10g.

Required Permissions

SELECT on USER_IND_COLUMNS and USER_INDEXES — available to the schema owner by default.

To scan another user’s indexes, SELECT on ALL_IND_COLUMNS and ALL_INDEXES (or DBA_IND_COLUMNS / DBA_INDEXES for a DBA role).

Preconditions

Run connected to the database schema that owns the indexes, or adjust the view names and add an OWNER predicate as described in the execution guidance.

Risk Level

Low. Read-only. No indexes are dropped. Identifies candidates only.

Script

-- ============================================================
-- Oracle Exact Duplicate Index Finder
-- Permissions : SELECT on USER_IND_COLUMNS, USER_INDEXES
-- Risk        : Read-only — identifies candidates, drops nothing
-- To scan another schema: replace USER_* with ALL_* and add
--   WHERE ic.owner = 'SCHEMA_NAME'  to each filter.
-- ============================================================

WITH index_key AS
(
    SELECT
        ic.index_name,
        ic.table_name,
        LISTAGG(ic.column_name, ',')
            WITHIN GROUP (ORDER BY ic.column_position)      AS key_cols
    FROM user_ind_columns AS ic
    WHERE ic.index_type = 'NORMAL'      -- B-tree key columns only; exclude LOB/DOMAIN columns
    GROUP BY ic.index_name, ic.table_name
),
index_info AS
(
    SELECT
        ik.index_name,
        ik.table_name,
        ik.key_cols,
        ui.index_type,
        ui.uniqueness,
        ui.status,
        ui.partitioned,
        ui.constraint_index,
        ui.visibility,
        ui.num_rows,
        ui.last_analyzed
    FROM index_key      AS ik
    JOIN user_indexes   AS ui
        ON ui.index_name  = ik.index_name
        AND ui.table_name = ik.table_name
)
SELECT
    ii1.table_name,
    ii1.index_name,
    ii1.index_type,
    ii1.uniqueness,
    ii1.constraint_index,
    ii1.visibility,
    ii1.key_cols,
    ii1.num_rows                                    AS index_num_rows,
    ii1.last_analyzed,
    ii2.index_name                                  AS duplicate_index,
    ii2.index_type                                  AS duplicate_index_type,
    ii2.uniqueness                                  AS duplicate_uniqueness,
    ii2.constraint_index                            AS duplicate_is_constraint,
    ii2.visibility                                  AS duplicate_visibility
FROM index_info AS ii1
JOIN index_info AS ii2
    ON  ii1.table_name    = ii2.table_name
    AND ii1.index_name    < ii2.index_name
    AND ii1.key_cols      = ii2.key_cols
ORDER BY ii1.table_name, ii1.key_cols;

Safe Execution Guidance

1. Run as the schema owner in SQL*Plus, SQL Developer, or any Oracle client.
2. To scan another schema’s indexes, replace USER_IND_COLUMNS with ALL_IND_COLUMNS and USER_INDEXES with ALL_INDEXES, then add AND ic.owner = 'SCHEMANAME' to the index_key CTE and AND ui.owner = 'SCHEMANAME' to the index_info join.
3. Each row represents one duplicate pair. Both indexes cover the same queries — one can be dropped.
4. Confirm index usage before dropping. Oracle does not accumulate real-time usage statistics automatically. Enable index monitoring to observe access:

   -- Enable monitoring for a target index (run once, produces low overhead)
   ALTER INDEX duplicate_index_name MONITORING USAGE;
   
   -- After a representative workload period, check V$OBJECT_USAGE
   SELECT index_name, table_name, monitoring, used, start_monitoring, end_monitoring
   FROM v$object_usage
   WHERE index_name = 'DUPLICATE_INDEX_NAME';
   
   -- Disable monitoring when done
   ALTER INDEX duplicate_index_name NOMONITORING USAGE;

5. Check whether either index backs a constraint:

   -- Constraint-backed indexes cannot be dropped with DROP INDEX.
   -- Drop the constraint instead: ALTER TABLE t DROP CONSTRAINT c;
   SELECT constraint_name, constraint_type
   FROM user_constraints
   WHERE index_name IN ('INDEX_NAME', 'DUPLICATE_INDEX_NAME');

6. Generate candidate DROP INDEX statements for review:

   SELECT
       'DROP INDEX ' || ii2.index_name || ';'  AS drop_statement,
       ii2.constraint_index
   FROM index_info AS ii1
   JOIN index_info AS ii2
       ON ii1.table_name = ii2.table_name AND ii1.index_name < ii2.index_name
       AND ii1.key_cols = ii2.key_cols
   WHERE ii2.constraint_index = 'NO';
   -- Constraint indexes cannot be dropped directly — drop the constraint first.

Expected Output

One row per duplicate pair. An empty result set means no exact B-tree duplicates exist in the current schema.

Interpreting Results

Both indexes NONUNIQUE, neither backs a constraint: Either can be dropped. Use ALTER INDEX ... MONITORING USAGE to confirm the lower-used one before dropping.

constraint_index = YES: The index enforces a PRIMARY KEY or UNIQUE constraint. It cannot be dropped with DROP INDEX — drop the constraint first using ALTER TABLE DROP CONSTRAINT. If the constraint is needed, the non-constraint duplicate is the candidate for removal.

visibility = INVISIBLE: An invisible index is not used by the optimizer but still maintained on every DML. If it was made invisible for testing purposes and is no longer needed, drop it. If both indexes are visible, dropping either has the same functional effect.

UNIQUE and NONUNIQUE pair: Retain the unique index — it enforces a stronger guarantee and eliminates the need for the non-unique duplicate.

Function-based indexes: This script uses index_type = 'NORMAL' to exclude non-standard index types from the LISTAGG. A function-based index stores the expression in USER_IND_EXPRESSIONS rather than a plain column name in USER_IND_COLUMNS. Function-based indexes are not compared by this script. If you need to detect duplicate function-based indexes, query USER_IND_EXPRESSIONS separately.

Supplement: Index Segment Size

SELECT
    segment_name AS index_name,
    ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb
FROM user_segments
WHERE segment_type = 'INDEX'
  AND segment_name IN ('INDEX_A', 'DUPLICATE_INDEX')
GROUP BY segment_name
ORDER BY size_mb DESC;

Rollback Steps

Not applicable — read-only script.

Official References

• USER_IND_COLUMNS
• USER_INDEXES
• V$OBJECT_USAGE
• DROP INDEX
• ALTER TABLE DROP CONSTRAINT