Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Managing Oracle Table Indexes

By: FYIcenter.com

Part:   1  2  3  4  

(Continued from previous part...)

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

How To Create a Single Index for Multiple Columns?

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 columns:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  birth_date DATE NOT NULL,
  social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.
  
CREATE INDEX student_names ON student(first_name,last_name);
Index created.

SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME              TABLE_NAME            UNIQUENES
----------------------- --------------------- ---------
SYS_C004123             STUDENT               UNIQUE
SYS_C004124             STUDENT               UNIQUE
STUDENT_NAMES           STUDENT               NONUNIQUE 

SELECT index_name, table_name, column_name 
  FROM USER_IND_COLUMNS WHERE table_name = 'STUDENT';
INDEX_NAME           TABLE_NAME       COLUMN_NAME
-------------------- ---------------- ----------------
SYS_C004135          STUDENT          ID 
SYS_C004136          STUDENT          SOCIAL_NUMBER 
STUDENT_NAMES        STUDENT          FIRST_NAME 
STUDENT_NAMES        STUDENT          LAST_NAME 

The USER_IND_COLUMNS view confirms that index STUDEND_NAMES has two columns FIRST_NAME and LAST_NAME.

Part:   1  2  3  4