Oracle Concepts – Find indexes for a table

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;

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: