Indexes and Performance

Indexes are the primary mechanism for speeding up query performance in Oracle Database. Like an index in a book, a database index allows Oracle to find rows quickly without scanning every row in a table. This lesson covers index types, the Oracle optimiser, and essential performance tuning techniques.

---

What is an Index?

An index is a database object that stores a sorted copy of selected column values along with pointers (ROWIDs) to the corresponding table rows.

Without an index: Oracle performs a full table scan — reads every row. With an index: Oracle performs an index lookup — jumps directly to matching rows.

---

Index Types

B-Tree Index (Default)

The most common index type — a balanced tree structure:

CREATE INDEX emp_lastname_idx ON employees(last_name);

• Best for high-cardinality columns (many distinct values)
• Supports equality (=) and range (<, >, BETWEEN) queries
• Automatically created for PRIMARY KEY and UNIQUE constraints

Composite Index

An index on multiple columns:

CREATE INDEX emp_dept_sal_idx ON employees(department_id, salary);