Indexing

In Oracle, you create an index using the CREATE INDEX statement.

Syntax

CREATE INDEX index_name
ON table_name(column_name);

Example 1: Create a Simple Index

Suppose you have an employees table:

CREATE INDEX emp_name_idx
ON employees(last_name);

This creates an index on the last_name column to speed up searches such as:

SELECT *
FROM employees
WHERE last_name = 'Smith';

Example 2: Create a Unique Index

CREATE UNIQUE INDEX emp_email_idx
ON employees(email);

A unique index prevents duplicate values in the email column.

Example 4: Create a Function-Based Index

CREATE INDEX emp_upper_name_idx
ON employees(UPPER(last_name));

Supports queries such as:

SELECT *
FROM employees
WHERE UPPER(last_name) = 'SMITH';