Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Create a Single Index for Multiple Columns

By: FYIcenter.com

(Continued from previous topic...)

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.

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?