Oracle Concepts – Find indexes for a table

Oracle Performance Tuning

See the 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 Order by owner, table_name, column_name  Where owner=’SCOTT’  AND table_name=’EMP’;

NOTE: You normally rebuild an index when moving it to a different tablespace or when it becomes invalid as the table relocates to a different tablespace. Rebuilding indexes on a regular basis is not required and
should not be practiced.
— Rebuild an index
To drop an index, use the DROP INDEX command, as shown in the following screenshot:
— Permanently remove the index from the database
DROP INDEX salary_idx;