Indexes

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

What Is an Index?

An index is a separate data structure Oracle maintains alongside a table to make lookups faster. Without an index, finding a specific row requires Oracle to read every row in the table from top to bottom — a full table scan. With an index on the searched column, Oracle jumps directly to the matching rows, the same way a book's index lets you find a topic without reading every page.

Indexes come with a cost. Every time a row is inserted, updated, or deleted Oracle must also update every index on that table. On read-heavy tables with occasional writes, indexes are almost always worthwhile. On write-heavy tables with few lookups, too many indexes slow down inserts and updates more than they help queries. Choosing what to index — and what not to — is one of the most important decisions in database design.

• An index speeds up SELECT queries that filter or sort on the indexed column
• Oracle automatically creates an index on every PRIMARY KEY and UNIQUE constraint
• Additional indexes must be created manually with CREATE INDEX
• Indexes consume disk space and slow down INSERT, UPDATE, and DELETE operations
• Oracle's query optimiser decides whether to use an index — creating one does not guarantee it will be used
• Query USER_INDEXES and USER_IND_COLUMNS to see existing indexes

Creating and Dropping Indexes

Use CREATE INDEX to add an index to any column or combination of columns. The index name should be descriptive — convention is idx_tablename_columnname. A unique index enforces that no two rows share the same value in the indexed column, in addition to speeding up lookups. Use DROP INDEX to remove an index when it is no longer needed.

-- Create a standard B-tree index on order_date.
-- Queries that filter or sort on order_date will benefit from this index.
CREATE INDEX idx_orders_order_date
    ON dataplexa_store.orders (order_date);
-- Create a composite index on two columns.
-- Useful when queries filter on both customer_id AND status together.
CREATE INDEX idx_orders_customer_status
    ON dataplexa_store.orders (customer_id, status);
-- Create a unique index -- enforces uniqueness AND speeds up lookups.
CREATE UNIQUE INDEX idx_customers_email
    ON dataplexa_store.customers (email);
-- Remove an index that is no longer useful.
DROP INDEX idx_orders_order_date;
-- List all indexes on the orders table.
SELECT index_name, index_type, uniqueness
FROM   user_indexes
WHERE  table_name = 'ORDERS'
ORDER  BY index_name;

• PK_ORDERS was created automatically when the primary key constraint was defined — Oracle always indexes primary keys
• The composite index idx_orders_customer_status is most useful when a query filters on both columns — queries filtering only on status alone cannot use it efficiently
• Table names in USER_INDEXES are stored in uppercase — WHERE table_name = 'ORDERS' must match exactly
• DROP INDEX does not require specifying the table — index names are unique within a schema

B-tree vs Bitmap Indexes

Oracle supports two main index types. A B-tree index is the default. It organises values in a balanced tree structure, making it fast for equality lookups, range queries, and sorting. It works well on columns with many distinct values — customer IDs, order dates, email addresses. A bitmap index stores a compact bit array for each distinct value. It is highly efficient for columns with very few distinct values — status flags, gender, country codes — especially in data warehouse queries that combine multiple such columns. Bitmap indexes are not suitable for tables with frequent inserts and updates because maintaining them under concurrent writes is expensive.

-- B-tree index: best for high-cardinality columns (many distinct values).
-- order_date has thousands of distinct values -- B-tree is correct here.
CREATE INDEX idx_orders_order_date
    ON dataplexa_store.orders (order_date);
-- Bitmap index: best for low-cardinality columns (few distinct values).
-- status has only 4 possible values: pending, shipped, delivered, cancelled.
-- Suitable for a reporting/data warehouse environment, not an OLTP system.
CREATE BITMAP INDEX idx_orders_status_bmp
    ON dataplexa_store.orders (status);
-- Function-based index: index the result of an expression.
-- Useful when queries filter on UPPER(email) for case-insensitive lookups.
CREATE INDEX idx_customers_email_upper
    ON dataplexa_store.customers (UPPER(email));
-- Now this query can use the index instead of scanning the full table:
SELECT customer_id, first_name, last_name
FROM   dataplexa_store.customers
WHERE  UPPER(email) = UPPER('Sarah.Chen@Email.Com');

• B-tree indexes are the safe default for most columns — use bitmap only in read-heavy reporting environments
• A function-based index stores the pre-computed result of an expression — without it, WHERE UPPER(email) = ... would force a full table scan because the index on the raw email column cannot be used
• Bitmap indexes should not be used on OLTP tables with concurrent writes — row-level locking conflicts make them impractical

When Oracle Does Not Use an Index

Creating an index does not guarantee Oracle will use it. The query optimiser weighs the cost of using the index against the cost of a full table scan and picks whichever is cheaper. Several common patterns prevent an index from being used even when one exists on the queried column.

-- These patterns suppress index usage on the indexed column.
-- PATTERN 1: wrapping the column in a function
-- The index is on order_date, not on TRUNC(order_date) -- full scan used
SELECT * FROM dataplexa_store.orders WHERE TRUNC(order_date) = DATE '2024-06-01';
-- FIX: use a range instead of wrapping the column
SELECT * FROM dataplexa_store.orders
WHERE  order_date >= DATE '2024-06-01'
AND    order_date <  DATE '2024-06-02';
-- PATTERN 2: implicit type conversion
-- customer_id is NUMBER but the value is passed as a string -- index suppressed
SELECT * FROM dataplexa_store.orders WHERE customer_id = '14';
-- FIX: match the data type exactly
SELECT * FROM dataplexa_store.orders WHERE customer_id = 14;
-- PATTERN 3: leading wildcard in LIKE
-- Oracle cannot use a B-tree index to search for values ending in a pattern
SELECT * FROM dataplexa_store.customers WHERE email LIKE '%@gmail.com';
-- FIX: anchor the wildcard at the end if possible
SELECT * FROM dataplexa_store.customers WHERE email LIKE 'sarah%';

• Wrapping a column in a function — TRUNC(), TO_CHAR(), UPPER() — prevents a standard index from being used unless a function-based index exists for that exact expression
• Passing a string where a NUMBER column is expected causes Oracle to implicitly convert every row, suppressing the index
• A leading wildcard LIKE '%value' cannot use a B-tree index because the index is sorted by the start of the value, not the end
• EXPLAIN PLAN followed by DBMS_XPLAN.DISPLAY is the standard Oracle tool for checking whether a query uses an index

Lesson Summary

Practice Questions

Quiz

Next up — Introduction to PL/SQL — What PL/SQL is, how a PL/SQL block is structured, and how to declare variables and write your first procedural logic in Oracle.