Indexing In Oracle Database

In Oracle, there are multiple ways to define (declare) indexes, depending on the type of index and its intended purpose. Below are the different syntaxes for various Oracle index types, commonly referred to as index declarations or index creation statements.

🔧 1. B-tree Index (Default)

CREATE INDEX index_name
ON table_name(column1 [, column2, ...]);

✅ Example:

CREATE INDEX idx_emp_name ON employees(employee_name);

🔒 2. Unique Index

CREATE UNIQUE INDEX index_name
ON table_name(column1 [, column2, ...]);

✅ Example:

CREATE UNIQUE INDEX idx_emp_email ON employees(email);

🔗 3. Composite Index (Multi-column)

CREATE INDEX index_name
ON table_name(column1, column2, column3);

✅ Example:

CREATE INDEX idx_dept_loc ON departments(department_name, location_id);

🧠 4. Function-Based Index

CREATE INDEX index_name
ON table_name(function(column));

✅ Example:

CREATE INDEX idx_upper_name ON employees(UPPER(employee_name));

This is useful when queries frequently use expressions like WHERE UPPER(employee_name) = 'JOHN'.

🧮 5. Bitmap Index

CREATE BITMAP INDEX index_name
ON table_name(column_name);

✅ Example:

CREATE BITMAP INDEX idx_gender ON employees(gender);

Note: Best for columns with low cardinality (few distinct values), e.g., gender, status.