Indexing, Query Optimization & Performance Tuning in Oracle

TAKEAWAYS:

• Indexes are ordered data structures (like phonebooks) that speed up searches but slow down writes—choose index types (B-tree, bitmap, composite) based on query patterns.
• The Oracle optimizer decides the best execution plan using statistics (histograms, column stats) and can be influenced via hints (/*+ INDEX */) or cost-based adjustments.
• Composite indexes and partial indexes reduce storage overhead for frequently filtered columns (e.g., WHERE status = 'active').
• Query optimization involves analyzing execution plans (via EXPLAIN PLAN), identifying full table scans, and rewriting SQL to avoid correlated subqueries.
• Index-organized tables (IOTs) store data physically sorted by the index key, ideal for unique, frequently accessed columns (e.g., customer_id in a bank’s transaction table).
• Monitoring tools like AWR reports and V$SQL views help detect slow queries and index inefficiencies in production systems (e.g., Daraz’s order-processing delays).

1. What is Indexing? Why Does It Matter?

Indexes are pre-sorted data structures that map column values to physical row locations, eliminating the need for full table scans. Think of them like the index of a textbook—you wouldn’t read every page to find a topic; you’d jump to the page number listed in the index.

How Indexes Work

When you query a table with a WHERE clause, Oracle uses the index to:

1. Locate the row ID (ROWID) without scanning the entire table.
2. Retrieve the row from the data blocks using the ROWID.

stateDiagram-v2
    [*] --> Query: SELECT * FROM orders WHERE customer_id = 1001
    Query --> Check: Index exists on customer_id?
    Check --> Yes: Use B-tree index to find ROWID
    Use B-tree index --> Fetch: Retrieve row from data blocks
    Check --> No: Full table scan (slow!)

Real-World Example: eSewa’s Transaction Logs

eSewa processes thousands of transactions per second. Without indexes, searching for a user’s transaction history would require scanning millions of rows. Instead:

• A B-tree index on transaction_id and user_id ensures instant lookups.
• A composite index on (user_id, transaction_date) speeds up queries like:

  SELECT * FROM transactions
  WHERE user_id = 'USER123' AND transaction_date > '2023-01-01';
  

2. Types of Indexes in Oracle

Oracle supports multiple index types, each suited for specific use cases. The choice impacts performance, storage, and maintenance overhead.

erDiagram
  customers ||--o{ orders : places
  orders ||--o{ transactions : "has"
  customers {
    string customer_id PK
    string phone_number
    string status
  }
  orders {
    string order_id PK
    string customer_id FK
    date order_date
  }
  transactions {
    string transaction_id PK
    string order_id FK
    decimal amount
    date transaction_date
  }
  idx_customer_phone ||--|{ customers : "B-tree on phone_number"
  idx_order_status ||--|{ orders : "Bitmap on status"
  idx_composite ||--|{ transactions : "Composite (order_id, transaction_date)"

Comparison Table: Index Types

When to Use Which Index?

• B-tree: Default choice for most scenarios (e.g., PRIMARY KEY, UNIQUE constraints).
• Bitmap: Ideal for data warehouses with static data (e.g., gender, product_category).
• Composite: Use when querying multiple columns together (e.g., (store_id, product_id) in an inventory system).
• Function-Based: Optimize queries on transformed data (e.g., LOWER(email) for case-insensitive searches).

3. How Indexes Speed Up Queries

sequenceDiagram
  participant User
  participant Oracle
  participant BTreeIndex
  participant DataBlocks

  User->>Oracle: SELECT * FROM customers WHERE phone_number = '98XXXXXXXX'
  Oracle->>BTreeIndex: Traverse index (B-tree)
  BTreeIndex-->>Oracle: Return ROWID (e.g., 0000000000000000)
  Oracle->>DataBlocks: Fetch row using ROWID
  DataBlocks-->>Oracle: Return customer record
  Oracle-->>User: Response in ~1ms

  Note right of Oracle: Without index: Full scan (~500ms for 50M rows)

Example: Full Table Scan vs. Indexed Search

Consider a table employees with 1 million rows. Without an index:

-- Full table scan (slow!)
SELECT * FROM employees WHERE salary > 50000;

Oracle scans every row, checking the salary column. With a B-tree index on salary:

1. Oracle traverses the B-tree to find the first salary > 50000.
2. It retrieves only the relevant ROWIDs, reducing I/O operations by 90%.

Worked Example: Ncell’s Customer Lookup

Ncell’s customer service system queries a table customers with 50 million records. Without an index:

• A query like SELECT * FROM customers WHERE phone_number = '98XXXXXXXX' would take seconds.
• With a B-tree index on phone_number, the lookup completes in milliseconds.

sequenceDiagram
    User->>+Database: Query: SELECT * FROM customers WHERE phone_number = '98XXXXXXXX'
    Database->>Index: Traverse B-tree to find ROWID
    Index-->>Database: Return ROWID
    Database->>Data Blocks: Fetch row using ROWID
    Database-->>-User: Return customer record

4. Indexing Strategies and Best Practices

A. Choosing Columns for Indexes

1. High-Cardinality Columns: Columns with many unique values (e.g., customer_id, email) benefit most from indexing.
2. Frequently Filtered Columns: Columns used in WHERE, JOIN, or ORDER BY clauses.
3. Avoid Over-Indexing: Each index adds overhead to INSERT, UPDATE, and DELETE operations.

B. Composite Indexes: Order Matters

A composite index on (A, B) is not the same as (B, A). Oracle uses the leftmost prefix rule:

-- Efficient (uses index on (A, B))
SELECT * FROM orders WHERE A = 10 AND B = 20;

-- Inefficient (cannot use index on (A, B))
SELECT * FROM orders WHERE B = 20; -- Missing leading column A

C. Partial Indexes

Index only a subset of rows (e.g., active customers):

CREATE INDEX idx_active_customers ON customers(customer_id)
WHERE status = 'active';

5. Query Optimization Techniques

A. Analyzing Execution Plans

Use EXPLAIN PLAN to visualize how Oracle executes a query:

EXPLAIN PLAN FOR
SELECT * FROM orders WHERE order_date > '2023-01-01';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Look for:

• Full Table Scan: Indicates missing indexes.
• Index Range Scan: Desired for indexed columns.
• Nested Loops: Can be slow for large datasets.

B. SQL Rewrite and Hints

Force Oracle to use a specific index:

-- Hint to use idx_customer_name
SELECT /*+ INDEX(customers idx_customer_name) */ *
FROM customers
WHERE name = 'John Doe';

C. Optimizing Joins

• Avoid Cartesian Products: Always include a JOIN condition.
• Use Hash Joins for Large Tables: Oracle automatically chooses the best join method, but you can influence it with hints.

D. Materialized Views

Pre-compute and store query results for complex reports (e.g., monthly sales summaries for NEPSE stock data).

6. Monitoring and Tuning Indexes

A. Identifying Unused Indexes

SELECT index_name, table_name
FROM dba_indexes
WHERE index_name NOT IN (
    SELECT index_name
    FROM dba_index_usage
);

Drop unused indexes to reduce storage overhead:

DROP INDEX unused_index;

B. Using AWR Reports

Automatic Workload Repository (AWR) reports highlight:

• Top SQL statements by execution time.
• Index usage statistics.
• Wait events (e.g., disk I/O bottlenecks).

Example from a Bank’s Loan System: An AWR report might show that SELECT * FROM loans WHERE status = 'approved' is slow. The fix:

1. Add a bitmap index on status.
2. Rewrite the query to select only needed columns (SELECT loan_id, amount FROM loans...).

7. Real-World Applications

A. eSewa: Transaction Processing

• Problem: Millions of transactions daily; queries like SELECT * FROM transactions WHERE user_id = 'USER123' were slow.
• Solution:
  • Created a composite index on (user_id, transaction_date).
  • Used partitioning by date to reduce scan ranges.
• Result: Query response time dropped from 500ms to 10ms.

B. Daraz: Order Fulfillment

• Problem: High latency in SELECT * FROM orders WHERE status = 'processing'.
• Solution:
  • Added a bitmap index on status (low cardinality).
  • Implemented index-organized tables for the orders table.
• Result: Order status checks now complete in <5ms.

C. Ncell: Customer Data Lookup

• Problem: SELECT * FROM customers WHERE phone_number = '98XXXXXXXX' took 2 seconds.
• Solution:
  • Created a B-tree index on phone_number.
  • Used function-based indexes for UPPER(phone_number) searches.
• Result: Lookup time reduced to <1ms.

8. Common Pitfalls and Mistakes

1. Over-Indexing: Too many indexes slow down INSERT/UPDATE operations.
   • Fix: Monitor index usage and drop unused ones.
2. Non-Selective Indexes: Indexes on columns with few distinct values (e.g., gender) are ineffective.
   • Fix: Use bitmap indexes for low-cardinality columns.
3. Ignoring Execution Plans: Assuming a query is optimized without checking EXPLAIN PLAN.
   • Fix: Always analyze plans for slow queries.
4. Composite Index Order Errors: Creating (B, A) when queries filter on A first.
   • Fix: Design indexes based on query patterns.

Exam Tip

1. Define Indexing Clearly:

   • Start with: "Indexes are data structures that improve query performance by reducing the need for full table scans. They work by storing a sorted copy of column values and their corresponding ROWIDs."
   • Marks: 2 for definition, 3 for explanation.

2. Compare Index Types:

   • Use a table to compare B-tree, bitmap, and composite indexes (as shown above). Highlight pros/cons for each.
   • Marks: 4 for comparison, 2 for real-world examples.

3. Worked Examples:

   • Always tie examples to real systems (e.g., eSewa, Ncell). Show:
     • The query before optimization.
     • The index created.
     • The performance improvement.
   • Marks: 5 for a complete example with SQL and explanation.

4. Optimization Techniques:

   • Mention at least two of:
     • EXPLAIN PLAN analysis.
     • SQL hints (/*+ INDEX */).
     • Composite index design.
   • Marks: 3 for techniques, 2 for applying them to a scenario.

5. Avoid Common Mistakes:

   • Examiners test for over-indexing and non-selective indexes. Always discuss trade-offs.
   • Marks: 2 for identifying pitfalls, 2 for solutions.

Final Note: Indexing is 80% of query optimization. Master the types, know when to use them, and practice analyzing execution plans. Real-world systems like eSewa and Ncell rely on these techniques to handle millions of transactions efficiently.

In the real world

• eSewa: Uses composite indexes on (user_id, transaction_date) to instantly retrieve user transaction histories, reducing query time from seconds to milliseconds for 10M+ daily transactions.
• Ncell: Relies on B-tree indexes on phone_number and customer_id to handle 50M+ customer lookups per day in under 1ms each.
• Daraz: Employs bitmap indexes for low-cardinality columns like order_status (e.g., 'active', 'cancelled') to speed up analytics on 100M+ orders, cutting report generation time by 80%.