Oracle Index Design and Best Practices

TL;DR

Oracle indexes speed up data retrieval by creating pointers to table rows, much like a book's index. Proper index design significantly improves query performance, while poorly designed ones can slow down data modifications. You need to balance read and write performance when deciding where and how to use indexes.

1. The Mental Model

Think of an index like the index at the back of a textbook. Instead of reading the entire book to find a specific topic, you look up the topic in the index, find the page numbers, and go directly to those pages. In Oracle, an index helps the database find specific rows in a table without scanning every single row.

2. The Core Material

Oracle indexes are schema objects that contain an entry for each indexed row or each distinct value. They store the indexed column(s) value(s) and a Rowid, which is the physical address of the row. When you query data using an indexed column in your WHERE clause, Oracle can use the index to quickly locate the data.

Types of Indexes

Photo by Markus Winkler on Pexels

• B-tree Index (Default): The most common type, efficient for a wide range of queries, especially those involving equality (=), range (<, >, BETWEEN), and ORDER BY operations.
• Bitmap Index: Suitable for columns with low cardinality (few distinct values), often used in data warehousing. They use bitmaps to map distinct values to Rowids.
• Function-Based Index: Indexes the result of a function or expression on one or more columns, useful when your queries frequently use functions in the WHERE clause.
• Composite/Concatenated Index: An index on multiple columns, useful when queries often filter by a combination of these columns. The order of columns matters!

When to Use Indexes

Photo by Element5 Digital on Pexels

Indexes are most beneficial for:
* Columns frequently used in WHERE clauses for filtering.
* Columns used in JOIN conditions.
* Columns used in ORDER BY or GROUP BY clauses.
* Columns with high cardinality (many distinct values).

When to Avoid/Be Cautious with Indexes

Photo by Markus Spiske on Pexels

Indexes aren't always a silver bullet. They come with overhead:
* Write Performance Impact: Every time you INSERT, UPDATE, or DELETE data in an indexed column, the index itself must also be updated. Too many indexes on a table can slow down DML operations significantly.
* Storage Space: Indexes consume disk space.
* Small Tables: For very small tables, a full table scan might be faster than using an index, as the overhead of reading the index could outweigh the benefit.
* Low Cardinality Columns (for B-tree): Indexing a column like GENDER (Male/Female) with a B-tree index is usually not efficient because the database will likely scan a large percentage of the table anyway. Bitmap indexes are better here.

Index Design Considerations

Photo by Nothing Ahead on Pexels

1. Selectivity: The higher the selectivity (the percentage of rows returned by a query), the more beneficial an index is. An index on a column with many unique values (e.g., SSN, PRIMARY KEY) is highly selective.
2. Column Order (Composite Indexes): For a composite index on (col1, col2, col3), it's usable for queries filtering on col1, (col1, col2), or (col1, col2, col3). It's generally not effective for queries only on col2 or col3. Put the most frequently filtered and selective columns first.
3. Data Type: Indexes work best on NUMBER and DATE types. VARCHAR2 can be less efficient if not managed correctly (e.g., leading wildcards in LIKE clauses).
4. NULL Values: B-tree indexes generally don't store entries for rows where all indexed columns are NULL. If your query relies on NULL values, you might need a special index or approach.

graph TD
    A[Query Performance Issue?] --> B{Analyze SQL Query};
    B -->|Check WHERE Clause| C{Filtering on what columns?};
    C -->|High Cardinality?| D{Column used in JOINs/ORDER BY?};
    D -->|Yes| E[Consider B-tree Index];
    C -->|Low Cardinality?| F[Consider Bitmap Index];
    B -->|Check Functions in WHERE?| G[Consider Function-Based Index];
    E --> H{Existing Index?};
    F --> H;
    G --> H;
    H -->|No| I[Create Index (Test Performance)];
    H -->|Yes| J[Is it used by Optimizer? (Check Explain Plan)];
    I --> K[Monitor DML Impact];
    J -->|No| L[Review Index Design / Gather Statistics];
    J -->|Yes| M[Performance Improved?];
    M -->|No| L;
    M -->|Yes| N[Done];
    K --> N;
    L --> I;

3. Worked Example

Let's say you have a large ORDERS table with millions of rows and frequent queries like this:

SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 12345
  AND order_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-06-30', 'YYYY-MM-DD')
ORDER BY order_date DESC;

Without an index, Oracle would perform a full table scan every time, which is slow.

Option 1: Individual Indexes
You could create separate B-tree indexes:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_order_date ON orders (order_date);

Oracle might use both via index merge, but it's not guaranteed.

Option 2: Composite Index (Better for this query)
A composite index on (customer_id, order_date) would be more effective because your query filters on both columns and orders by order_date. customer_id is likely more selective (fewer customers share the same ID) and comes first in the WHERE clause.

CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);

This single index can significantly speed up the query because:
1. Oracle can quickly find rows for customer_id = 12345.
2. Within those rows, the order_date values are already sorted, which helps with the BETWEEN clause and the ORDER BY order_date DESC.

After creating the index, you should always run the query and check its execution plan (using EXPLAIN PLAN FOR in SQL Developer or SQL*Plus) to confirm Oracle is actually using your new index.

4. Key Takeaways

• Indexes improve query performance by providing fast access paths to data.
• B-tree indexes are the most common and versatile type for various query patterns.
• Composite indexes are powerful when queries filter on multiple columns; column order matters.
• Over-indexing or poorly designed indexes can hurt DML (Insert, Update, Delete) performance.
• Always analyze your SQL queries' WHERE, JOIN, ORDER BY, and GROUP BY clauses to identify indexing candidates.
• Use EXPLAIN PLAN to verify if your indexes are being used by the Oracle optimizer.
• Avoid indexing columns with very low cardinality (few distinct values) using B-tree indexes.

5. Now Try It

Think of a table in a database you're familiar with (e.g., EMPLOYEES, PRODUCTS). Identify two columns that you think would be good candidates for indexing based on common queries against that table. Explain why you chose them and what type of index you'd use (e.g., B-tree, composite). Then, consider a scenario where adding an index might actually degrade performance for that table. What would it be and why?

Success looks like: You've correctly identified columns based on query patterns, chosen an appropriate index type, and articulated a valid scenario where an index could be detrimental, demonstrating an understanding of the trade-offs.