Skip to content
Oracle DBA Scripts & Database Utilities

Index Column Details (dindcol.sql)

What This Script Does

Section titled “What This Script Does”

Shows detailed index column information including sequence, data types, and cardinality statistics for each column in an index.

The Script

Section titled “The Script”
rem  dindcol.sql
rem
rem  linesize = 80
set lines 132
rem
rem
col table_owner noprint new_value towner
col table_name format a24 heading 'TABLE NAME'
col index_type format a3 heading 'TYP'
col u format a1 heading 'U'
col index_name format a24 heading 'INDEX NAME'
col column_name format a30 heading 'COLUMN|NAME'
col NUM_DISTINCT format 9,999,999,999 heading 'COL|CARD'
col column_position format 99 heading 'SEQ'
col column_length format 999 heading 'LEN'
rem
clear break
break on table_owner skip page on table_name skip 1 on index_name on index_type on u
rem
ttitle center 'Index Column Definitions for ' towner skip 2
rem
select i.table_owner,
       i.table_name,
       decode(i.index_type, 'NORMAL', NULL, substr(i.index_type,1,3)) index_type,
       decode( i.uniqueness, 'NONUNIQUE', null, 'UNIQUE', 'U', 'BITMAP', 'B', '?' ) u,
       i.index_name, c.column_position, c.column_name, c.column_length, DC.NUM_DISTINCT
  from sys.dba_ind_columns c, sys.dba_indexes i, DBA_TAB_COLUMNS DC
 where i.table_owner like nvl( upper('&owner'), '%')
   and i.table_name like nvl(upper('&table'),'%')
   and i.index_name like nvl(upper('&index'),'%')
AND C.TABLE_OWNER   = DC.OWNER
AND C.TABLE_NAME    = DC.TABLE_NAME
AND C.COLUMN_NAME   = DC.COLUMN_NAME
AND I.TABLE_OWNER = DC.OWNER
AND I.TABLE_NAME             = DC.TABLE_NAME
   and c.column_name like nvl(upper('&column'),'%')
   and i.owner = c.index_owner
   and i.index_name = c.index_name
 order by i.table_owner, i.table_name, 2, i.index_name, c.column_position;


rem
tti off

Usage

Section titled “Usage”
-- Basic usage
@dindcol.sql


-- When prompted, enter:
-- owner: ** - Schema name (use % for all)
-- table: ** - Table name (use % for all)
-- index: ** - Index name (use % for all)
-- column: ** - Column name (use % for all)

Parameters

Section titled “Parameters”

The script prompts for:

  • &owner - ** - Schema name (use % for all)
  • &table - ** - Table name (use % for all)
  • &index - ** - Index name (use % for all)
  • &column - ** - Column name (use % for all)

Required Privileges

Section titled “Required Privileges”
SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_IND_COLUMNS
SELECT ON DBA_INDEXES
SELECT ON DBA_TAB_COLUMNS

Sample Output

Section titled “Sample Output”
                         Index Column Definitions for HR


TABLE NAME               TYP U INDEX NAME              SEQ COLUMN                         LEN    COL
                                                           NAME                               CARD
------------------------ --- - ------------------------ --- ------------------------------ --- -----------
EMPLOYEES                    U EMPLOYEES_EMAIL_UK         1 EMAIL                           25       1,025
                               EMPLOYEES_NAME_IDX          1 LAST_NAME                       25         287
                                                           2 FIRST_NAME                      20         945
                               EMPLOYEES_DEPT_IDX          1 DEPARTMENT_ID                    4          11

Key Output Columns

Section titled “Key Output Columns”
  • TABLE NAME: Table that the index belongs to
  • TYP: Index type (blank=NORMAL, BIT=BITMAP, FUN=FUNCTION-BASED)
  • U: Uniqueness (U=UNIQUE, B=BITMAP, blank=NON-UNIQUE)
  • INDEX NAME: Name of the index
  • SEQ: Column position within the index (1, 2, 3…)
  • COLUMN NAME: Name of the indexed column
  • LEN: Column length/precision
  • COL CARD: Column cardinality (number of distinct values)

Common Use Cases

Section titled “Common Use Cases”

Index Analysis

-- Review index composition and cardinality
@dindcol.sql
-- Enter: HR for owner, EMPLOYEES for table, % for index and column

Query Tuning

-- Check specific index selectivity
@dindcol.sql
-- Enter: % for owner/table, EMPLOYEES_NAME_IDX for index, % for column
Section titled “Related Scripts”