Data Dictionary Views About Indexes

In relational theory, the database catalog (metadata) is just as important as the data itself. The catalog describes the logical schema (tables, columns, constraints), and it also records physical and operational structures (indexes, partitions, storage, and status). Oracle exposes its catalog through the data dictionary—a set of tables and views that report what exists and how it is defined.

This lesson focuses on the dictionary views that help you answer the most common index questions:

• What indexes exist, and what type are they (B-tree, bitmap, function-based, domain, etc.)?
• Which table does an index belong to, and is it UNIQUE?
• Which columns (and what order) make up the index?
• Where is the index stored (tablespace), and what is its operational state (valid/unusable, visible/invisible, partitioned)?
• What size and shape does the index have (B-tree depth, leaf blocks, distinct keys, row counts)?

Scope: USER, ALL, and DBA Views

Oracle publishes many dictionary views in three “scope tiers.” You choose the tier based on privileges and your intent:

• USER_* — objects you own (no special privilege required).
• ALL_* — objects you can access (includes your objects plus objects granted to you).
• DBA_* — all objects in the database (typically requires DBA privileges).

For index research inside your own schema, USER_INDEXES and USER_IND_COLUMNS are the primary starting point. If you need cross-schema visibility, move up to the ALL_ or DBA_ equivalents.

The USER_INDEXES view describes index objects at a high level: name, type, table association, uniqueness, and many structural details. It includes many columns; the list below highlights common ones you will use when auditing or troubleshooting.

INDEX_NAME	Name of the index.
INDEX_TYPE	Type of index (for example NORMAL, BITMAP, FUNCTION-BASED, DOMAIN).
TABLE_OWNER	Schema that owns the base table associated with the index.
TABLE_NAME	Name of the base table associated with the index.
UNIQUENESS	Either UNIQUE or NONUNIQUE.
TABLESPACE_NAME	Tablespace where the index segment is stored.
BLEVEL	Depth of the B-tree structure (lower is generally better for read efficiency).
LEAF_BLOCKS	Number of leaf blocks in the index segment.
DISTINCT_KEYS	Count of distinct key values (helps you reason about selectivity).
NUM_ROWS	Row count associated with the index statistics (depends on stats freshness).

-- 1) List indexes you own (names + type + table + uniqueness + tablespace)
SELECT index_name,
       index_type,
       table_name,
       uniqueness,
       tablespace_name
FROM   user_indexes
ORDER  BY table_name, index_name;

-- 2) Filter indexes for a single table (use uppercase table name unless quoted)
SELECT index_name,
       index_type,
       uniqueness,
       tablespace_name
FROM   user_indexes
WHERE  table_name = 'EMPLOYEES'
ORDER  BY index_name;

-- Show each index with its columns in order
SELECT ui.table_name,
       ui.index_name,
       ui.uniqueness,
       ui.index_type,
       uic.column_position,
       uic.column_name
FROM   user_indexes ui
JOIN   user_ind_columns uic
       ON ui.index_name = uic.index_name
ORDER  BY ui.table_name, ui.index_name, uic.column_position;

If you use partitioning, index metadata expands. At a minimum, know that partitioned indexes have dedicated dictionary views. A commonly referenced one is USER_PART_INDEXES, which contains information about partitioned index definitions. When you troubleshoot a partitioned workload, you often want to confirm whether an index is local/global and how partitions are aligned.

-- Identify partitioned indexes you own
SELECT index_name,
       table_name
FROM   user_part_indexes
ORDER  BY table_name, index_name;

Partition-aware metadata becomes essential when you are rebuilding indexes by partition, exchanging partitions, or diagnosing unexpected plan changes after partition maintenance.

Beyond the Basics: ALL_ and DBA_ Equivalents

The same concepts exist across schemas:

• ALL_INDEXES, DBA_INDEXES
• ALL_IND_COLUMNS, DBA_IND_COLUMNS
• ALL_PART_INDEXES, DBA_PART_INDEXES

Use ALL_* when you are analyzing objects granted to you (for example in an application schema). Use DBA_* when you must audit database-wide indexing practices.

Not all indexes are “normal” B-trees. Oracle supports extensible indexing through indextypes, which underpin domain indexes (indexes implemented by Oracle or a user-defined framework). The ALL_INDEXTYPES view displays information about the indextypes accessible to the current user.

Related Views

1. DBA_INDEXTYPES displays information about all indextypes in the database.
2. USER_INDEXTYPES displays information about the indextypes owned by the current user.

Conceptually, indextypes matter because they tell you what “specialized” indexing capabilities exist in the database, and whether those index implementations are system-managed or require user-managed maintenance.

When you are debugging a query plan or auditing indexing practices, you typically want four classes of facts:

1. Definition — what the index is and what it is on (name, table, uniqueness, type).
2. Key structure — which columns and what order.
3. Placement — which tablespace (and in broader architecture, which storage lane it maps to).
4. Health / shape — whether statistics look reasonable (row counts, distinct keys, depth) and whether operational state is acceptable.

The basic views in this lesson cover (1)–(3) directly. For deeper tuning, you will also use statistics-focused views (and you will correlate index metadata with query plans).

Example Queries

• View all indexes owned by the current user:

  
  SELECT *
  FROM   user_indexes;
  

• View all indexes accessible to the current user:

  
  SELECT *
  FROM   all_indexes;
  

• Get index columns for a specific index:

  
  SELECT index_name,
         table_name,
         column_position,
         column_name
  FROM   user_ind_columns
  WHERE  index_name = 'BIDDER_CLIENT_IDX'
  ORDER  BY column_position;
  

• List indexes for one table and show their columns:

  
  SELECT ui.index_name,
         ui.uniqueness,
         ui.index_type,
         uic.column_position,
         uic.column_name
  FROM   user_indexes ui
  JOIN   user_ind_columns uic
         ON ui.index_name = uic.index_name
  WHERE  ui.table_name = 'EMPLOYEES'
  ORDER  BY ui.index_name, uic.column_position;
  

Filtering Results

You rarely query these views without filtering. Start with table name and/or index name, then expand outward:

-- Find indexes in a specific tablespace (useful for “separate index tablespace” audits)
SELECT index_name, table_name, tablespace_name
FROM   user_indexes
WHERE  tablespace_name = 'INDEX_TBS'
ORDER  BY table_name, index_name;

With these dictionary patterns, you can quickly answer “what indexes exist, how are they defined, and where are they stored?” The next lesson builds on that by showing how to drop indexes safely as part of index lifecycle management.