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);

• Column order matters — the index is most effective when queries filter on the leading columns
• A query on department_id alone can use this index
• A query on salary alone typically cannot (unless index skip scan is used)

Unique Index

Enforces uniqueness:

CREATE UNIQUE INDEX emp_email_uk ON employees(email);

Bitmap Index

Stores bitmaps instead of row pointers — ideal for low-cardinality columns:

CREATE BITMAP INDEX emp_gender_bm ON employees(gender);

• Best for data warehouse / read-heavy workloads
• Excellent for queries with AND/OR conditions on multiple low-cardinality columns
• Not suitable for OLTP — bitmap indexes cause heavy locking on DML operations

Function-Based Index

Indexes the result of an expression:

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

Allows this query to use the index:

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

Reverse Key Index

Reverses the bytes of the key — distributes sequential inserts across the index:

CREATE INDEX order_id_rev_idx ON orders(order_id) REVERSE;

Useful for avoiding hot blocks when many sessions insert sequential values.

Index-Organized Table (IOT)

The table data is stored within the index structure itself:

CREATE TABLE lookup_codes (
  code_id   NUMBER,
  code_desc VARCHAR2(100),
  CONSTRAINT lookup_pk PRIMARY KEY (code_id)
) ORGANIZATION INDEX;

Best for small lookup tables accessed primarily by primary key.

---

Index Summary

Index Type	Best For
B-Tree	High-cardinality, OLTP queries
Composite	Multi-column filters, covering indexes
Bitmap	Low-cardinality, data warehouse, complex AND/OR
Function-Based	Queries using functions on columns
Reverse Key	Sequential inserts (avoids hot blocks)
IOT	Small lookup tables, primary key access

---

The Oracle Optimiser

Oracle uses a Cost-Based Optimiser (CBO) that evaluates multiple execution plans and chooses the one with the lowest estimated cost.

How the CBO Works

1. Parse — analyse the SQL syntax
2. Transform — rewrite the query for efficiency (view merging, subquery unnesting)
3. Estimate — calculate the cost of different access paths using statistics
4. Choose — select the plan with the lowest cost

Optimizer Statistics

The CBO depends on accurate statistics:

-- Gather statistics for a table
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

-- Gather statistics for an entire schema
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');

-- Check when statistics were last gathered
SELECT table_name, last_analyzed, num_rows
FROM user_tables;

Tip: Stale statistics are a leading cause of poor performance. Oracle auto-gathers statistics by default, but verify after large data loads.

---

EXPLAIN PLAN