Identify Indexed Columns in Oracle Database Tables

calendar Mar 19, 2024 / · 9 min read · indexing oracle indexes dba_ind_columns user_ind_columns all_ind_columns dba_indexes dba_ind_expressions  ·
Share on: linkedin copy

Identify Indexed Columns in Oracle Database Tables

Purpose

Which columns does this index cover, and in what order? A developer asks it before adding an index that might already exist. A DBA asks it when a query scans a whole table and the team swears an index is in place. The answer is in the data dictionary, one row per indexed column.

DBA_IND_COLUMNS describes the columns of all the indexes on all tables and clusters in the database. The query below takes an index name and lists its columns in the order they sit in the index. Two further queries start from a table name instead, and one folds each index into a single line with a column list.

The order matters. A composite index serves queries that use its leading columns, so (customer_id, order_date) and (order_date, customer_id) are two different indexes with different uses.

Code

 1set verify off
 2col index_owner format a20
 3col index_name format a30
 4col column_name format a30
 5select index_owner
 6, index_name
 7, column_position
 8, column_name
 9, descend
10from dba_ind_columns
11where index_name = upper('&index')
12order by index_owner
13, index_name
14, column_position
15/

To start from a table, list the index columns for every index on it:

 1col table_name format a30
 2select index_name
 3, column_position
 4, column_name
 5, descend
 6from dba_ind_columns
 7where table_owner = upper('&owner')
 8and table_name = upper('&table')
 9order by index_name
10, column_position
11/

To see each index as one line, with its type and state:

 1col columns format a60
 2select i.index_name
 3, i.uniqueness
 4, i.visibility
 5, i.status
 6, listagg(c.column_name, ', ') within group (order by c.column_position) columns
 7from dba_indexes i
 8, dba_ind_columns c
 9where c.index_owner = i.owner
10and c.index_name = i.index_name
11and i.table_owner = upper('&owner')
12and i.table_name = upper('&table')
13group by i.index_name
14, i.uniqueness
15, i.visibility
16, i.status
17order by i.index_name
18/

To read the expressions behind a function-based index:

1select index_name
2, column_position
3, column_expression
4from dba_ind_expressions
5where table_owner = upper('&owner')
6and table_name = upper('&table')
7order by index_name
8, column_position
9/

Code Breakdown

The first query

  • dba_ind_columns holds one row per column of each index. Its columns are the same as ALL_IND_COLUMNS.
  • index_owner is in the select list on purpose. Two schemas can own indexes with the same name, and a query on index_name alone returns the columns of both mixed together. The owner column and the ORDER BY keep them apart.
  • column_position is the position of the column within the index. Position 1 is the leading column.
  • column_name is the name of the column, or the attribute of an object type column.
  • descend shows ASC or DESC for the sort direction of that column in the index.
  • where index_name = upper('&index') filters on the name you type. SQL*Plus replaces &index with the value you enter when it runs the statement. The quotes are already in the query, so enter the name without quotes. If the index was created with a mixed-case name, enter it exactly as DBA_INDEXES.INDEX_NAME shows it and remove the upper call.
  • set verify off stops SQL*Plus from printing the statement before and after it replaces the substitution variable.

The second query

  • table_owner and table_name find all the indexes on one table. Always give the owner, because the same table name can exist in more than one schema.
  • The ORDER BY puts the columns of each index together and in position order, so each index reads as a block.

The third query

  • dba_indexes has one row per index, with UNIQUENESS (UNIQUE or NONUNIQUE), VISIBILITY (VISIBLE or INVISIBLE to the optimizer) and STATUS.
  • listagg(... ) within group (order by c.column_position) joins the column names of one index into one string, in index order. The GROUP BY lists every non-aggregated column of the select.
  • The join links dba_indexes.owner to dba_ind_columns.index_owner, together with the index name, so an index in one schema never picks up columns from another.

The fourth query

  • dba_ind_expressions lists the expressions of function-based indexes. column_expression holds the expression and column_position says where it sits in the index.

Key Points

  • Scope of the views. DBA_IND_COLUMNS covers the whole database. ALL_IND_COLUMNS covers indexes on tables the current user can access. USER_IND_COLUMNS covers indexes the current user owns and indexes on the tables the current user owns, and it has no INDEX_OWNER or TABLE_OWNER columns. Use USER_IND_COLUMNS when you lack access to the DBA_ view.
  • Position is the order. COLUMN_POSITION is the position of the column or attribute within the index. Sort by it, or the column list has no meaning.
  • Function-based indexes show an expression, not a plain column. The expression is in DBA_IND_EXPRESSIONS. A column list for such an index is incomplete without that view.
  • Index type tells you more. DBA_INDEXES.INDEX_TYPE shows NORMAL, BITMAP, FUNCTION-BASED NORMAL, IOT - TOP, DOMAIN and other values. A bitmap index and a B-tree index on the same column are different tools.
  • Status. For a nonpartitioned index, STATUS is VALID or UNUSABLE. An unusable index still shows its columns here, so read STATUS before you trust the column list. For a partitioned index, look at the partition views such as DBA_IND_PARTITIONS.
  • Visibility. An INVISIBLE index is hidden from the optimizer. It still shows in these views, so a column list alone does not prove that queries can use the index.
  • Reference columns. For an index on a user-defined REF column, the names shown are the attribute names with the REF column name as a prefix.

Insights and Best Practices

Check before you create

Before adding an index, run the second query for the table. If an index already starts with the columns you plan to index, a new one adds write cost with no read gain. Oracle says that when rows are inserted or deleted, all indexes on the table must be updated, and that when a column is updated, all indexes that contain the column must be updated. An index whose columns are a leading subset of another index is a candidate for removal, once you have checked that nothing depends on it.

Read the leading column

Take an index on (first_name, last_name) and a query with WHERE last_name = 'KING'. The leading column is not in the predicate, so the optimizer may not choose the index. It may choose an index skip scan instead. The order of the columns decides which queries the index serves, which is why you look at column_position and not at the list of names alone.

Compare queries to indexes

Take the WHERE clause of a slow statement and put the columns it filters on next to the index columns. If the filter columns are the second and third columns of an index, and the first column is not in the predicate, the statement has no good index. The fix may be a new index, a different column order, or a change to the statement.

Watch for expressions

If a WHERE clause wraps a column in a function, such as UPPER(last_name), Oracle lists "the query predicate applies a function to the indexed column" as a reason for the optimizer to consider a full table scan. Unless the index is a function-based index, the database indexes the values of the column, not the values with the function applied. A function-based index on the expression is the exception. The fourth query shows the expression behind a function-based index.

Document with the one-line form

The third query gives a compact index inventory per table. Spool it for the key tables each quarter and keep the files. When a column vanishes from an index list, you will know when the change came.

When to Run This

  • Before creating an index, to see if one with the same leading columns exists
  • When a query does a full table scan and an index is expected
  • During a schema review, to find indexes with the same column list
  • Before dropping an index, to see what it covers
  • After a migration, to compare the indexes on the old and new schema
  • When tuning a join, to confirm that the join columns are indexed in the right order

Troubleshooting Common Issues

If the first query returns no rows, check the spelling and the case of the index name, and check the owner. Query DBA_INDEXES with a LIKE on the name to find the exact spelling. If you query USER_IND_COLUMNS or ALL_IND_COLUMNS, the index may be outside what your account can see.

If the query fails with ORA-00942, the message is "table or view does not exist". Oracle lists two causes: the object does not exist, or the account does not have access to it. When the DBA_ view is the object, the account most likely lacks access. Use ALL_IND_COLUMNS or USER_IND_COLUMNS instead, or ask for access.

If SQL*Plus does not prompt for the value, SET DEFINE may be off. SET DEFINE ON turns the scan for substitution variables back on, and the default prefix character is &.

If a column_name does not match any column of the table, run the fourth query. The index may be function-based, and the expression is listed there.

If an index appears in the list but queries do not use it, check VISIBILITY and STATUS in the third query. An invisible index is hidden from the optimizer. Oracle also says that an unusable index is ignored by the optimizer, so rebuild it or drop and re-create it before you expect queries to use it.

References

Posts in this series