Saturday, July 10, 2010

How to find indexes for a table?

How can I find the indexes created on a table?

It is very easy to find out the indexes existing on a table. Before I did not know about this SQL statement I was thinking that it is going to be a gigantic task. But just the following statement did the trick.

In Oracle, there exists a table with the name "user_ind_columns" which contains 4 columns.
1 table owner
2 table name
3.index name
4.column name

What you need to do is --- just running an SQL statement against the database using this information.
Sample query to find table indexes and related information:
select table_name, index_name, column_name 
from USER_IND_COLUMNS
where TABLE_NAME='EMPLOYEE'


Note: Please leave your comments and make the valuable suggestions available to the world.

È