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
ALTER INDEX salary_idx REBUILD;
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;
