Show Table Columns Used in an Index in Oracle

Q

How To See the Table Columns Used in an Index in Oracle?

✍: FYIcenter.com

A

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

 

Create a Single Index for Multiple Columns in Oracle

Rebuild an Index in Oracle

Managing Oracle Table Indexes

⇑⇑ Oracle Database Tutorials

2019-04-17, 2196🔥, 0💬