Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Show Table Columns Used in an Index

By: FYIcenter.com

(Continued from previous topic...)

How To See the Table Columns Used in an Index?

You can a list of indexes in your schema from the USER_INDEXES view, but it will not give you the columns used in each index in the USER_INDEXES view. If you want to see the columns used in an index, you can use the USER_IND_COLUMNS view. Here is an example script for you:

SELECT index_name, table_name, column_name 
  FROM USER_IND_COLUMNS WHERE table_name = 'EMPLOYEES';
INDEX_NAME           TABLE_NAME       COLUMN_NAME
-------------------- ---------------- ----------------
EMP_EMAIL_UK         EMPLOYEES        EMAIL
EMP_EMP_ID_PK        EMPLOYEES        EMPLOYEE_ID
EMP_DEPARTMENT_IX    EMPLOYEES        DEPARTMENT_ID
EMP_JOB_IX           EMPLOYEES        JOB_ID
EMP_MANAGER_IX       EMPLOYEES        MANAGER_ID
EMP_NAME_IX          EMPLOYEES        LAST_NAME
EMP_NAME_IX          EMPLOYEES        FIRST_NAME

(Continued on next topic...)

1. What Is an Index?
2. How To Run SQL Statements through the Web Interface?
3. How To Create a Table Index?
4. How To List All Indexes in Your Schema?
5. What Is an Index Associated with a Constraint?
6. How To Rename an Index?
7. How To Drop an Index?
8. Can You Drop an Index Associated with a Unique or Primary Key Constraint?
9. What Happens to Indexes If You Drop a Table?
10. How To Recover a Dropped Index?
11. What Happens to the Indexes If a Table Is Recovered?
12. How To Rebuild an Index?
13. How To See the Table Columns Used in an Index?
14. How To Create a Single Index for Multiple Columns?