DBA_INDEXES - Analyze Oracle Index Types, Sizing & Status

DBA_INDEXES

Overview

DBA_INDEXES provides one row for every index in the database, covering B-tree, bitmap, function-based, reverse-key, domain, partitioned, and index-organized table (IOT) indexes. It is the primary view for index maintenance, health monitoring, and performance analysis. DBAs use it to detect unusable indexes after data loads, evaluate index efficiency via clustering factor, identify redundant or overlapping indexes, and audit invisible indexes awaiting validation before drop.

View Type: Data Dictionary View (static) Available Since: Oracle 7 Required Privileges: SELECT on DBA_INDEXES or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

The user-scoped variants are USER_INDEXES (current schema only, no OWNER column) and ALL_INDEXES (indexes on tables accessible to the current user).

Key Columns

Column	Datatype	Description
OWNER	VARCHAR2(128)	Schema that owns the index
INDEX_NAME	VARCHAR2(128)	Name of the index
TABLE_OWNER	VARCHAR2(128)	Schema that owns the indexed table (usually the same as OWNER)
TABLE_NAME	VARCHAR2(128)	Name of the table the index is defined on
INDEX_TYPE	VARCHAR2(27)	NORMAL (B-tree), BITMAP, FUNCTION-BASED NORMAL, FUNCTION-BASED BITMAP, IOT - TOP, DOMAIN, CLUSTER
UNIQUENESS	VARCHAR2(9)	UNIQUE or NONUNIQUE
TABLESPACE_NAME	VARCHAR2(30)	Tablespace for the index segment; NULL for partitioned indexes
STATUS	VARCHAR2(8)	VALID, UNUSABLE, or N/A (N/A for partitioned indexes; check DBA_IND_PARTITIONS for partition-level status)
NUM_ROWS	NUMBER	Estimated number of index entries from the most recent statistics gather
LEAF_BLOCKS	NUMBER	Number of leaf-level blocks in the B-tree structure; directly determines full index scan cost
CLUSTERING_FACTOR	NUMBER	Measure of how ordered the table rows are relative to the index; low = efficient range scans, high = scattered rows
BLEVEL	NUMBER	B-tree depth, excluding the leaf level; 0 means the root is also a leaf; most OLTP indexes are 2–3
LAST_ANALYZED	DATE	Timestamp of the most recent statistics gather for this index
VISIBILITY	VARCHAR2(9)	VISIBLE (default) or INVISIBLE — invisible indexes are maintained but ignored by the optimizer
COMPRESSION	VARCHAR2(8)	ENABLED or DISABLED — whether index key compression is active
PREFIX_LENGTH	NUMBER	Number of leading key columns included in key compression
PARTITIONED	VARCHAR2(3)	YES if the index is partitioned, NO otherwise
FUNCIDX_STATUS	VARCHAR2(8)	ENABLED or DISABLED for function-based indexes; NULL for standard indexes
LOGGING	VARCHAR2(3)	YES or NO — whether redo logging is enabled for index maintenance operations

Essential Queries

Unusable and Invalid Indexes

Find all indexes with STATUS = ‘UNUSABLE’ and all partitioned indexes with at least one unusable partition. Unusable indexes are silently ignored by the optimizer, which forces full table scans:

SELECT
    i.owner,
    i.index_name,
    i.table_name,
    i.index_type,
    i.status,
    i.partitioned,
    i.last_analyzed,
    NVL2(
        (SELECT 1
         FROM   dba_ind_partitions ip
         WHERE  ip.index_owner = i.owner
         AND    ip.index_name  = i.index_name
         AND    ip.status      = 'UNUSABLE'
         AND    ROWNUM         = 1),
        'YES', 'NO'
    )                                        AS has_unusable_partitions
FROM
    dba_indexes i
WHERE
    i.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                    'MDSYS', 'ORDSYS', 'EXFSYS', 'CTXSYS', 'WMSYS',
                    'XDB', 'ORDPLUGINS')
    AND (
        i.status = 'UNUSABLE'
        OR EXISTS (
            SELECT 1
            FROM   dba_ind_partitions ip
            WHERE  ip.index_owner = i.owner
            AND    ip.index_name  = i.index_name
            AND    ip.status      = 'UNUSABLE'
        )
    )
ORDER BY
    i.owner,
    i.table_name,
    i.index_name;

Index Size vs Table Size

Compare the allocated size of each index against its parent table. Indexes that are disproportionately large relative to the table can indicate index bloat from high DML volume:

SELECT
    i.owner,
    i.index_name,
    i.table_name,
    i.index_type,
    i.uniqueness,
    i.leaf_blocks,
    ROUND(i.leaf_blocks * 8192 / 1024 / 1024, 2)        AS index_mb,
    t.blocks                                             AS table_blocks,
    ROUND(t.blocks * 8192 / 1024 / 1024, 2)             AS table_mb,
    ROUND(i.leaf_blocks / NULLIF(t.blocks, 0) * 100, 1) AS index_pct_of_table,
    i.num_rows,
    i.blevel,
    i.last_analyzed
FROM
    dba_indexes  i
    JOIN dba_tables t
        ON  t.owner      = i.table_owner
        AND t.table_name = i.table_name
WHERE
    i.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                    'MDSYS', 'ORDSYS', 'EXFSYS', 'CTXSYS', 'WMSYS', 'XDB')
    AND i.leaf_blocks IS NOT NULL
    AND t.blocks      IS NOT NULL
    AND t.blocks      > 0
ORDER BY
    index_mb DESC NULLS LAST
FETCH FIRST 40 ROWS ONLY;

High Clustering Factor Indexes

A clustering factor close to NUM_ROWS (rather than the table’s block count) means rows are physically scattered relative to the index key. These indexes are expensive for range scans and are prime candidates for table reorganization or monitoring:

SELECT
    i.owner,
    i.index_name,
    i.table_name,
    i.index_type,
    i.clustering_factor,
    t.num_rows,
    t.blocks                                                    AS table_blocks,
    ROUND(i.clustering_factor / NULLIF(t.num_rows, 0), 4)       AS cf_to_row_ratio,
    ROUND(i.clustering_factor / NULLIF(t.blocks, 0), 2)         AS cf_to_block_ratio,
    CASE
        WHEN i.clustering_factor <= t.blocks * 2     THEN 'GOOD'
        WHEN i.clustering_factor <= t.num_rows * 0.5 THEN 'MODERATE'
        ELSE 'POOR'
    END                                                         AS cf_assessment,
    i.last_analyzed
FROM
    dba_indexes  i
    JOIN dba_tables t
        ON  t.owner      = i.table_owner
        AND t.table_name = i.table_name
WHERE
    i.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                    'MDSYS', 'ORDSYS', 'EXFSYS', 'CTXSYS', 'WMSYS', 'XDB')
    AND i.index_type        = 'NORMAL'
    AND i.clustering_factor IS NOT NULL
    AND t.num_rows          > 10000
ORDER BY
    cf_to_row_ratio DESC NULLS LAST
FETCH FIRST 30 ROWS ONLY;

Duplicate and Redundant Indexes

Detect indexes on the same table that share the same leading column. An index with a leading column already covered by a longer composite index is redundant and can be dropped:

WITH index_cols AS (
    SELECT
        i.owner,
        i.table_name,
        i.index_name,
        i.uniqueness,
        i.status,
        i.visibility,
        LISTAGG(ic.column_name, ', ')
            WITHIN GROUP (ORDER BY ic.column_position) AS column_list,
        MAX(ic.column_position)                        AS col_count,
        MIN(ic.column_name) KEEP
            (DENSE_RANK FIRST ORDER BY ic.column_position) AS first_col
    FROM
        dba_indexes     i
        JOIN dba_ind_columns ic
            ON  ic.index_owner = i.owner
            AND ic.index_name  = i.index_name
    WHERE
        i.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                        'MDSYS', 'ORDSYS', 'EXFSYS', 'CTXSYS', 'WMSYS', 'XDB')
        AND i.index_type = 'NORMAL'
    GROUP BY
        i.owner, i.table_name, i.index_name, i.uniqueness, i.status, i.visibility
)
SELECT
    a.owner,
    a.table_name,
    a.index_name        AS possible_redundant_index,
    a.column_list       AS its_columns,
    b.index_name        AS covering_index,
    b.column_list       AS covering_columns,
    b.uniqueness        AS covering_uniqueness
FROM
    index_cols a
    JOIN index_cols b
        ON  b.owner      = a.owner
        AND b.table_name = a.table_name
        AND b.index_name != a.index_name
        AND b.first_col   = a.first_col
        AND b.col_count   > a.col_count
ORDER BY
    a.owner,
    a.table_name,
    a.index_name;

Invisible Indexes

List all invisible indexes. These are commonly created as a drop-safety net — the index is maintained but not used by the optimizer until made visible:

SELECT
    owner,
    index_name,
    table_name,
    index_type,
    uniqueness,
    status,
    leaf_blocks,
    ROUND(leaf_blocks * 8192 / 1024 / 1024, 2) AS index_mb,
    last_analyzed,
    compression
FROM
    dba_indexes
WHERE
    owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                  'MDSYS', 'ORDSYS', 'EXFSYS', 'CTXSYS', 'WMSYS', 'XDB')
    AND visibility = 'INVISIBLE'
ORDER BY
    owner,
    table_name,
    index_name;

Common Use Cases

Post data-load rebuild — After direct-path inserts with APPEND hint, partition operations, or Data Pump imports with INDEXES=N, query for UNUSABLE indexes and generate ALTER INDEX … REBUILD statements to restore them before going live
Index bloat detection — After heavy DELETE or UPDATE workloads, LEAF_BLOCKS can grow significantly relative to NUM_ROWS because deleted index entries are not immediately reclaimed; a ratio significantly above expected indicates a rebuild or coalesce is needed
Clustering factor investigation — When a query with a WHERE clause on an indexed column shows full table scans in explain plan despite a suitable index, check the clustering factor; a value close to NUM_ROWS makes the optimizer prefer a full scan over an index range scan
Redundant index cleanup — Before adding a new composite index, query the leading-column overlap to avoid creating an index that merely duplicates an existing one’s prefix; also use this before removing an index to ensure no queries depend solely on it
Invisible index validation — Mark an index INVISIBLE before dropping it, run the workload for one full business cycle, verify performance is unchanged in AWR or SQL Monitor, then drop with confidence
Auto index review (19c+) — Filter INDEX_NAME LIKE ‘SYS_AI_%’ ESCAPE ” to identify indexes created by Oracle’s automatic indexing feature and compare their usage against manually created indexes

DBA_TABLES — Parent table metadata; join on TABLE_OWNER and TABLE_NAME to compare index size against table size
DBA_OBJECTS — Object catalog; join on OWNER and INDEX_NAME to get OBJECT_ID and confirm STATUS at the object level
DBA_IND_COLUMNS — Indexed columns in key order; essential for reconstructing composite index column lists
DBA_IND_PARTITIONS — Partition-level index status for partitioned indexes; check here when DBA_INDEXES shows STATUS = ‘N/A’
DBA_IND_STATISTICS — Detailed statistics metadata including the stale indicator, method, and degree from the last gather
V$OBJECT_USAGE — Tracks whether an index has been accessed since monitoring was enabled with ALTER INDEX … MONITORING USAGE; join on INDEX_NAME to determine if an index is safe to drop

Version Notes

Oracle 10g: INDEX_TYPE values expanded with bitmap join indexes and domain index support fully reflected
Oracle 11g: VISIBILITY column (VISIBLE / INVISIBLE) added; invisible indexes allow risk-free testing before dropping an index without incurring the downtime of a rebuild if needed again
Oracle 12c: Advanced index compression available; COMPRESSION values expanded beyond ENABLED/DISABLED to include ADVANCED compression tiers; virtual column function-based index metadata improved
Oracle 12c (Multitenant): CDB_INDEXES available from the CDB root with CON_ID; within a PDB, DBA_INDEXES is identical to pre-12c behavior
Oracle 19c: Automatic indexing (DBMS_AUTO_INDEXING) introduces system-managed indexes visible as SYS_AI_% entries; the AUTO column in DBA_INDEXES flags these; they can be made invisible or dropped via DBMS_AUTO_INDEXING procedures
Oracle 23ai: Vector indexes (VECTOR INDEX type) appear in DBA_INDEXES for AI-driven similarity search capabilities introduced with Oracle AI Vector Search