Find indexes for a table

It is not unusual to wonder what indexes a specific table might have, and what columns in the table are assigned to those indexes. The following query will provide this information to you. It draws on the information in the dba_ind_columns data dictionary view:

SQL> column table_owner format a15

SQL> column table_name format a20

SQL> column index_name format a20

SQL> column column_name format a20

 

SQL> Select owner, table_name, index_name, column_name

2  FROM dba_ind_columns

3  Order by owner, table_name, column_name

4  Where owner=’SCOTT’

5  AND table_name=’EMP’;

 

TABLE_OWNER     TABLE_NAME           INDEX_NAME           COLUMN_NAME

--------------- -------------------- -------------------- ----------

SCOTT           EMP                  PK_EMP               EMPNO

 

In this example we find that the EMP table in the SCOTT schema has one index called PK_EMP. This index is built on a single column, EMPNO.