DBA_IND_COLUMNS - Query Oracle Index Column Details

DBA_IND_COLUMNS

Overview

DBA_IND_COLUMNS describes every column (or expression) that participates in every index in the database, one row per index column. It is the essential companion to DBA_INDEXES when you need to know not just that an index exists but exactly which columns it covers, in what order, and in what sort direction. This view is used routinely when checking index coverage for a query predicate, identifying duplicate or redundant indexes, auditing composite index column ordering, and diagnosing cases where the optimizer is unable to use an available index.

For function-based and expression indexes, the COLUMN_EXPRESSION column contains the expression text in place of a simple column name. USER_IND_COLUMNS and ALL_IND_COLUMNS provide the same information scoped to the current user’s objects or accessible objects respectively.

View Type: Static Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_IND_COLUMNS, SELECT_CATALOG_ROLE, or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
INDEX_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
TABLE_NAME	VARCHAR2(128)	Name of the indexed table
COLUMN_NAME	VARCHAR2(4000)	Column name; for expression indexes, this shows SYS_NC prefixed hidden column name
COLUMN_POSITION	NUMBER	Position of the column within the index (1 = leading column)
COLUMN_LENGTH	NUMBER	Indexed length for the column
CHAR_LENGTH	NUMBER	Indexed length in characters (for character type columns)
DESCEND	VARCHAR2(4)	ASC or DESC — the sort direction for the column in the index
COLUMN_EXPRESSION	LONG	For function-based indexes, the actual expression text; NULL for plain column indexes

Essential Queries

Basic Usage

Show all indexed columns for a specific table, grouped by index, in column position order:

SELECT
    ic.index_name,
    i.index_type,
    i.uniqueness,
    i.status,
    ic.column_position,
    ic.column_name,
    ic.descend,
    ic.column_expression
FROM
    dba_ind_columns ic
    JOIN dba_indexes i ON i.owner      = ic.index_owner
                       AND i.index_name = ic.index_name
WHERE
    ic.table_owner = 'HR'
    AND ic.table_name  = 'EMPLOYEES'
ORDER BY
    ic.index_name,
    ic.column_position;

Monitoring Query

Find all tables in a schema that have no index on any column — tables with only a full-scan access path, often a sign of missing index coverage:

SELECT
    t.owner,
    t.table_name,
    t.num_rows,
    t.last_analyzed
FROM
    dba_tables t
WHERE
    t.owner = 'HR'
    AND NOT EXISTS (
        SELECT 1
        FROM   dba_ind_columns ic
        WHERE  ic.table_owner = t.owner
        AND    ic.table_name  = t.table_name
    )
    AND t.num_rows > 1000
ORDER BY
    t.num_rows DESC NULLS LAST;

Combined with Other Views

Join DBA_IND_COLUMNS with DBA_INDEXES and DBA_TAB_COLUMNS to show, for each table column, whether it is covered by an index and what index covers it:

SELECT
    tc.column_id,
    tc.column_name,
    tc.data_type,
    tc.nullable,
    LISTAGG(ic.index_name || '[pos=' || ic.column_position || ']', ', ')
        WITHIN GROUP (ORDER BY ic.index_name)  AS covering_indexes
FROM
    dba_tab_columns  tc
    LEFT JOIN dba_ind_columns ic ON ic.table_owner = tc.owner
                                 AND ic.table_name  = tc.table_name
                                 AND ic.column_name = tc.column_name
WHERE
    tc.owner      = 'HR'
    AND tc.table_name = 'EMPLOYEES'
GROUP BY
    tc.column_id,
    tc.column_name,
    tc.data_type,
    tc.nullable
ORDER BY
    tc.column_id;

Duplicate and Redundant Index Detection

Identify pairs of indexes on the same table where one index’s leading columns are a prefix of another, indicating potential redundancy:

SELECT
    a.table_owner,
    a.table_name,
    a.index_name         AS potential_redundant,
    b.index_name         AS superset_index,
    a.column_name        AS col1,
    b.column_name        AS col1_superset
FROM
    dba_ind_columns a
    JOIN dba_ind_columns b ON b.table_owner     = a.table_owner
                           AND b.table_name      = a.table_name
                           AND b.column_name     = a.column_name
                           AND b.column_position = a.column_position
                           AND b.index_name     != a.index_name
WHERE
    a.column_position = 1
    AND a.table_owner NOT IN ('SYS', 'SYSTEM')
ORDER BY
    a.table_owner,
    a.table_name,
    a.index_name;

Function-Based Index Inventory

List all function-based (expression) indexes across the database, showing the expression they index:

SELECT
    ic.table_owner,
    ic.table_name,
    ic.index_name,
    ic.column_position,
    ic.column_expression,
    i.status,
    i.uniqueness
FROM
    dba_ind_columns ic
    JOIN dba_indexes i ON i.owner      = ic.index_owner
                       AND i.index_name = ic.index_name
WHERE
    ic.column_expression IS NOT NULL
    AND ic.table_owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP')
ORDER BY
    ic.table_owner,
    ic.table_name,
    ic.index_name,
    ic.column_position;

Common Use Cases

• Index coverage analysis — For a query with WHERE clause predicates, check that the indexed leading columns match the filter columns to confirm the optimizer can use the index efficiently
• Composite index leading column check — Verify that the most selective or most commonly filtered column is placed at position 1 in composite indexes, as queries that skip the leading column cannot use the index
• Redundant index cleanup — Find pairs of indexes where one is a proper prefix of another; the shorter index is usually redundant and can be dropped to reduce maintenance overhead
• Function-based index discovery — Locate expression indexes to confirm that queries using the same expression in a WHERE clause will match the index definition exactly
• Descending index audit — Filter DESCEND = ‘DESC’ to find columns indexed in descending order, which affect ORDER BY and range scan plans
• Pre-migration schema validation — Export the full index-column mapping before a migration to verify that all indexes are recreated correctly on the target system

Related Views

• DBA_INDEXES — Index-level metadata including type, uniqueness, status, and partition information; join on INDEX_OWNER and INDEX_NAME
• DBA_TAB_COLUMNS — Column definitions for each table; cross-reference to confirm data types of indexed columns
• DBA_TABLES — Table statistics including NUM_ROWS; useful for estimating the impact of adding or dropping indexes
• DBA_CONSTRAINTS — Join to find which indexes enforce primary key or unique constraints
• V$OBJECT_USAGE — Tracks whether an index has been used since monitoring was enabled with ALTER INDEX … MONITORING USAGE

Version Notes

• Oracle 8i: Function-based index support added; COLUMN_EXPRESSION populated for expression indexes, COLUMN_NAME shows a SYS_NC hidden column name
• Oracle 10g: DESCEND column reliable for all index types; index compression metadata available via DBA_INDEXES
• Oracle 11g: Virtual column indexes reflected correctly; COLUMN_NAME for virtual columns matches the virtual column name in DBA_TAB_COLUMNS
• Oracle 12c: Partial indexes (on partitioned tables with a WHERE clause) introduced; visible via DBA_INDEXES.VISIBILITY and DBA_IND_COLUMNS behaves identically
• Oracle 23ai: Vector index support (VECTOR index type) — DBA_IND_COLUMNS rows exist for the indexed vector column; index type details in DBA_INDEXES.INDEX_TYPE