Index Column Definitions (dindcoli.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides Oracle database administration functionality via the dindcoli.sql script.

The Script

rem  dindcol.sql
rem
rem  linesize = 80
rem
ttitle 'Index Column Definitions'
rem
col table_name format a22 heading 'TABLE NAME'
col u format a1 heading 'U'
col index_name format a20 heading 'INDEX NAME'
col column_name format a25 heading 'COLUMN NAME'
col column_position format 99 heading 'SEQ'
col column_length format 999 heading 'LEN'
rem
break on table_name skip 1 on index_name on u
rem
select i.table_name, decode( i.uniqueness, 'UNIQUE', 'U', null ) u,
       i.index_name, c.column_position, c.column_name, c.column_length
  from sys.dba_ind_columns c, sys.dba_indexes i
 where i.table_owner like upper('&owner')
   and i.table_name like upper('&table')
   and i.index_name like upper('&index')
   and i.owner = c.index_owner
   and i.index_name = c.index_name
 order by i.table_name, i.index_name, c.column_position;

What This Script Does

The dindcoli.sql script analyzes index column structures in Oracle databases. It displays detailed information about the columns that make up indexes, including their position within composite indexes, column lengths, and uniqueness constraints. This is essential for understanding index design and optimization opportunities.

Usage

Run the script and provide input when prompted:

SQL> @dindcoli.sql
Enter value for owner: HR
Enter value for table: EMPLOYEES
Enter value for index: %

Parameters:

• owner: Schema owner name (supports wildcard % matching)
• table: Table name pattern (supports wildcard % matching)
• index: Index name pattern (supports wildcard % matching, use % for all indexes)

Required Privileges

• SELECT privilege on DBA_IND_COLUMNS and DBA_INDEXES (usually requires DBA role)
• For non-DBA users, modify script to use USER_IND_COLUMNS and USER_INDEXES

Sample Output

Index Column Definitions

TABLE NAME             U INDEX NAME          SEQ COLUMN NAME               LEN
---------------------- - -------------------- --- ------------------------- ---
CUSTOMERS                CUST_EMAIL_UK          1 EMAIL                     100
                       U

EMPLOYEES              U EMP_EMP_ID_PK         1 EMPLOYEE_ID                22
                         EMP_DEPARTMENT_IX      1 DEPARTMENT_ID              22
                         EMP_JOB_IX             1 JOB_ID                     10
                         EMP_MANAGER_IX         1 MANAGER_ID                 22
                         EMP_NAME_IX            1 LAST_NAME                  25
                                                2 FIRST_NAME                 20

ORDERS                   ORD_CUSTOMER_IX        1 CUSTOMER_ID                22
                         ORD_DATE_IX            1 ORDER_DATE                  7
                       U ORD_ID_PK              1 ORDER_ID                   22
                         ORD_STATUS_IX          1 ORDER_STATUS               20

Key Output Columns

• TABLE NAME: Name of the table containing the index
• U: Uniqueness indicator (‘U’ for unique indexes, blank for non-unique)
• INDEX NAME: Name of the index
• SEQ: Column position within the index (sequence order)
• COLUMN NAME: Name of the indexed column
• LEN: Maximum length of the column data

Understanding the Results

Index Structure Analysis

• Single Column Indexes: Only one row per index
• Composite Indexes: Multiple rows with sequential SEQ numbers
• Column Order: SEQ indicates the order of columns in composite indexes

Uniqueness Indicators

• ‘U’ Flag: Indicates a unique index (enforces uniqueness constraint)
• Blank: Non-unique index (allows duplicate values)

Column Length Considerations

• Large Lengths: May indicate inefficient indexing of large columns
• Variable Lengths: VARCHAR2 columns show maximum possible length

Common Use Cases

1. Index Design Review

   • Analyze existing index structures for optimization opportunities
   • Identify redundant or overlapping indexes

2. Composite Index Analysis

   • Review column order in multi-column indexes
   • Ensure proper leading column selection for query patterns

3. Performance Tuning

   • Identify missing indexes for common query patterns
   • Analyze index column selectivity

4. Schema Documentation

   • Document index structures for development teams
   • Create index inventory for database migrations

Performance Optimization Insights

Column Position Importance

• Leading Columns: Most selective columns should typically be first
• Query Patterns: Column order should match common WHERE clause patterns
• Range Scans: Consider query predicates when ordering columns

Index Efficiency Indicators

• Long Columns: Indexes on very long columns may be inefficient
• Too Many Columns: Indexes with many columns may be over-indexed
• Duplicate Patterns: Similar column combinations may indicate redundancy

Troubleshooting Guide

Common Issues

1. No Output: Check spelling of owner, table, and index names
2. Permission Denied: Ensure access to DBA_IND_COLUMNS and DBA_INDEXES views
3. Too Much Output: Use more specific patterns to narrow results

Alternative Queries for Non-DBA Users

-- For current user's objects only
SELECT table_name, decode(uniqueness, 'UNIQUE', 'U', null) u,
       index_name, column_position, column_name, column_length
FROM user_ind_columns c, user_indexes i
WHERE i.table_name like upper('&table')
  AND i.index_name = c.index_name
ORDER BY table_name, index_name, column_position;

Advanced Analysis

Finding Redundant Indexes

Use output to identify indexes with similar leading columns:

• Look for indexes on the same table with identical leading columns
• Consider dropping less selective or unused redundant indexes

Composite Index Effectiveness

• Verify that column order matches actual query patterns
• Ensure range predicates are not on leading columns unless necessary

Related Scripts

• dindex.sql - Comprehensive index analysis
• dtable.sql - Table structure analysis
• dindstats.sql - Index statistics analysis

Script Features

• Hierarchical Display: Groups output by table and index for easy reading
• Uniqueness Indicator: Quick identification of unique vs. non-unique indexes
• Column Sequence: Clear display of column order in composite indexes
• Flexible Filtering: Supports wildcard patterns for targeted analysis

Best Practices

1. Regular Review: Periodically analyze index structures for optimization
2. Document Changes: Track index modifications and their performance impact
3. Consider Selectivity: Use column length as one factor in index design decisions
4. Query Pattern Alignment: Ensure index column order matches common query patterns