📚 Oracle SQL Indexes: CREATE, DROP, Unique, Function-based & Bitmap Indexes

Indexes in Oracle SQL improve query performance by allowing faster data retrieval. This tutorial covers how to create and drop different types of indexes including unique, function-based, and bitmap indexes.

🛠️ 1. CREATE INDEX

Syntax:

CREATE INDEX index_name ON table_name(column_name);

Example:

CREATE INDEX IDX_EMP_NAME ON EMPLOYEE(EMP_NAME);

This creates a normal index on the EMP_NAME column to speed up searches.

🗑️ 2. DROP INDEX

Syntax:

DROP INDEX index_name;

Example:

DROP INDEX IDX_EMP_NAME;

Drops the specified index from the database.

🔒 3. UNIQUE INDEX

Syntax:

CREATE UNIQUE INDEX index_name ON table_name(column_name);

Example:

CREATE UNIQUE INDEX IDX_EMP_EMAIL ON EMPLOYEE(EMAIL);

Ensures values in the EMAIL column are unique and speeds up lookups.

⚙️ 4. FUNCTION-BASED INDEX

Purpose:

Indexes based on expressions or functions to optimize queries using functions on columns.

Syntax:

CREATE INDEX index_name ON table_name(UPPER(column_name));

Example:

CREATE INDEX IDX_EMP_NAME_UPPER ON EMPLOYEE(UPPER(EMP_NAME));

This speeds up case-insensitive searches on EMP_NAME.

🖼️ 5. BITMAP INDEX

Purpose:

Efficient for columns with low cardinality (few distinct values), like gender, status, or categories.

Syntax:

CREATE BITMAP INDEX index_name ON table_name(column_name);

Example:

CREATE BITMAP INDEX IDX_EMP_GENDER ON EMPLOYEE(GENDER);

Optimizes queries filtering by gender.

---

🚀 Proper use of indexes improves query speed dramatically. Choose index types wisely based on your data!

Trust Coding : Programming