Oracle - Show Table Columns Used in an Index in Oracle

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

⇒Managing Oracle Table Indexes

⇒⇒Oracle Database Tutorials

2019-04-17, 769👍, 0💬

Recovered Tables with Indexes in Oracle
What Happens to the Indexes If a Table Is Recovered in Oracle? If you dropped a table, and recovered it back from the recycle bin, what happens to its indexes? Are all indexes recovered back automatically? The answer is that all indexes will be recovered, if you recover a dropped table from the recy... 2019-04-22, 826👍, 0💬

Show Table Columns Used in an Index in Oracle
How To See the Table Columns Used in an Index in Oracle? 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... 2019-04-17, 770👍, 0💬

Rebuild an Index in Oracle
How To Rebuild an Index in Oracle? If you want to rebuild an index, you can use the "ALTER INDEX ... REBUILD statement as shown in the following SQL script: SELECT index_name, table_name, uniqueness FROM USER_INDEXES WHERE table_name = 'EMPLOYEES'; INDEX_NAME TABLE_NAME UNIQUENES -------------------... 2019-04-17, 757👍, 0💬

Create a Single Index for Multiple Columns in Oracle
How To Create a Single Index for Multiple Columns in Oracle? If you know a group of multiple columns will be always used together as search criteria, you should create a single index for that group of columns with the "ON table_name(col1, col2, ...)" clause. Here is an example of one index for two c... 2019-04-17, 674👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.