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 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.

⇒ Managing Oracle Tablespaces and Data Files

⇐ Show Table Columns Used in an Index in Oracle

⇑ Managing Oracle Table Indexes

⇑⇑ Oracle Database Tutorials