Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Indexes

The problem of listing indexes for a table is much the same as that of listing constraints on a table. You have a master-detail relationship between the index and its columns, and you may have multiple indexes on one table.

To list only the indexes on a table, query the all_indexes view:

SELECT index_name, index_type, uniqueness
FROM all_indexes
WHERE owner = UPPER('&owner')
  AND table_name = UPPER('&table_name');

Listing the indexes alone is seldom enough. You need to know at least the columns involved in each index. To that end, join with all_ind_columns. Example 10-8 shows the query.

Example 10-8. A script to list all indexes on a table

COLUMN index_name FORMAT A20
COLUMN index_type FORMAT A10
COLUMN UNIQUENESS FORMAT A10
COLUMN column_name FORMAT A15

SELECT ai.index_name, ai.index_type, ai.uniqueness, aic.column_name
FROM all_indexes ai INNER JOIN all_ind_columns aic
     ON ai.owner = aic.index_owner
     AND ai.index_name = aic.index_name
WHERE ai.owner = UPPER('&owner')
  AND ai.table_name = UPPER('&table_name')
ORDER BY aic.column_position;

Here's a run of Example 10-8 showing the two indexes on the employee table:

SQL> @ex10-8
Enter value for owner: gennick
old   5: WHERE ai.owner = UPPER('&owner')
new   5: WHERE ai.owner = UPPER('gennick')
Enter value for table_name: employee old 6: AND ai.table_name = UPPER('&table_name') new 6: AND ai.table_name = UPPER('employee') INDEX_NAME INDEX_TYPE UNIQUENESS COLUMN_NAME -------------------- ---------- ---------- --------------- EMPLOYEE_PK NORMAL ...