Lesson 11

This module consolidated the practical index features Oracle DBAs use to improve query performance while controlling maintenance risk. Modern Oracle releases (including Oracle Database 23ai) still rely on the same core principle: choose the right index type, keep optimizer statistics current, and perform maintenance with minimal disruption.

You worked through a sequence of index enhancements and the "why" behind each choice:

1. Index categories and use-cases: When a B-tree is appropriate, when bitmap is appropriate, and when specialized indexing is required.
2. Descending indexes: Support workloads that frequently request "latest first" results without requiring extra sorts.
3. Bitmap indexes: Useful for low-cardinality columns in analytic / reporting workloads; generally avoided for high-concurrency OLTP due to locking/maintenance behavior.
4. Reverse-key indexes: Reduce hot-block contention for monotonically increasing keys (for example, sequence-based inserts) at the cost of range-scan usability.
5. Statistics management: How and when to keep object statistics accurate so the optimizer can reliably cost plans.
6. Domain indexes: Oracle's extensible indexing framework for specialized domains (text, spatial, multimedia, or user-defined indexing cartridges).
7. Online index maintenance: Rebuild/defragment index structures while keeping application availability as high as possible.
8. Function-based indexes: Index expressions so queries using functions can avoid full scans and reduce per-row computation.

Choosing the right index type requires understanding your workload characteristics:

Standard B-tree indexes when:

• You need efficient equality and range scan support
• Column cardinality is medium to high
• OLTP workload with frequent updates
• General-purpose access patterns predominate

Bitmap indexes when:

• Column cardinality is very low (few distinct values)
• Workload is predominantly read-only or batch-update
• Complex multi-condition WHERE clauses benefit from bitmap operations
• Data warehouse or decision support systems

Reverse-key indexes when:

• Index key values increase monotonically (sequences, timestamps)
• Right-edge hot block contention is measured or anticipated
• Range scans on the indexed column are rare or nonexistent

Function-based indexes when:

• Queries filter or sort on expressions rather than raw column values
• Case-insensitive searches are common (UPPER/LOWER functions)
• Date truncation or arithmetic calculations appear in predicates
• Computed columns need indexing without materializing the value

Index changes should always be validated with measurable performance data:

Before implementing an index enhancement:

1. Capture baseline metrics: Execution plans, consistent gets, physical reads, CPU time, and elapsed time for target queries.
2. Analyze current index usage: Query V$OBJECT_USAGE, DBA_INDEX_USAGE to identify unused indexes that add overhead without benefit.
3. Review optimizer statistics: Ensure existing statistics are current before making index decisions based on execution plans.

After creating or modifying indexes:

1. Compare execution plans: Verify the optimizer chooses the expected access path.
2. Measure actual performance: Test with representative data volumes and realistic bind variables.
3. Monitor DML impact: Especially for bitmap indexes, measure the overhead on INSERT/UPDATE/DELETE operations.
4. Track space consumption: Index growth over time can indicate rebuild needs or design issues.

Use the EXPLAIN PLAN command and execution plan analysis to confirm optimizer behavior:

EXPLAIN PLAN FOR
SELECT order_date, SUM(order_total)
FROM orders
WHERE TRUNC(order_date) = TRUNC(SYSDATE)
GROUP BY order_date;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

If the plan shows a full table scan despite a function-based index on TRUNC(order_date), verify the index is valid, visible, and that statistics are current.

Index proliferation

Every index adds overhead to DML operations. Avoid creating indexes speculatively. Instead, create indexes based on documented query patterns and measured performance needs. Periodically review and drop unused indexes.

Bitmap indexes in OLTP environments

Bitmap indexes lock entire ranges of rows during DML operations, creating severe contention in high-concurrency transactional systems. Reserve bitmap indexes for data warehouse and reporting databases where updates occur in controlled batch windows.

Ignoring statistics after index changes

Creating an index without gathering statistics can result in the optimizer not recognizing the new access path. Always gather statistics after creating indexes, or ensure automatic statistics gathering will run before the index is needed:

BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(
    ownname => 'SALES_SCHEMA',
    indname => 'ORDERS_DATE_FBI',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
  );
END;
/

Function-based indexes without matching predicates

Function-based indexes only help queries that use the exact expression indexed. An index on UPPER(last_name) does not help queries using LOWER(last_name) or last_name without a function.

Rebuilding indexes without evidence

Routine index rebuilds are rarely necessary in modern Oracle versions. Rebuild only when space usage analysis or performance metrics indicate actual fragmentation issues. Unnecessary rebuilds waste system resources and risk introducing problems.

• Prefer DBMS_STATS over ANALYZE for optimizer statistics. The ANALYZE statement is obsolete for gathering optimizer statistics; use DBMS_STATS for object statistics management and rely on Oracle's automated stats gathering where appropriate.
• Index changes should be evidence-driven. Validate with execution plans, runtime metrics, and representative bind values. Avoid "index sprawl," because every extra index adds DML overhead.
• Use safe operational techniques. For maintenance and testing, features such as invisible and unusable indexes can support controlled experimentation and bulk-load strategies, but they should be used intentionally and documented.
• Leverage online operations when available. Online index rebuilds and creations allow maintenance without blocking application access, reducing downtime windows.
• Consider composite indexes strategically. Multi-column indexes can support multiple query patterns, but column order matters significantly for optimizer usage.

Function-based indexes remain one of the most practical "index enhancements" for real applications: when queries filter on an expression, you can index that expression directly. The most common use cases include case-insensitive searches, date truncation, and mathematical calculations.

Example of a practical function-based index for case-insensitive email searches:

CREATE INDEX customers_email_fbi 
ON customers(UPPER(email_address));

-- This query can now use the index:
SELECT customer_id, customer_name
FROM customers
WHERE UPPER(email_address) = UPPER('JDoe@Example.Com');

Oracle requires that predicates match the indexed expression exactly to use the function-based index. The optimizer statistics should reflect the cardinality of the expression, not just the underlying column.

Index enhancements are one component of a comprehensive Oracle performance strategy:

• Query optimization: Even the best indexes cannot compensate for poorly written SQL with unnecessary joins, missing WHERE clauses, or inefficient subqueries.
• Table design: Partitioning, appropriate data types, and normalization decisions affect what indexes can accomplish.
• System resources: Adequate memory for buffer cache and PGA, fast storage for index tablespaces, and appropriate redo log configuration support index performance.
• Application patterns: Understanding whether workloads are OLTP, OLAP, or hybrid informs index strategy at a fundamental level.

The next module introduces Index-Organized Tables (IOTs), which represent an alternative table structure where the entire table is stored as an index, eliminating redundant storage and improving access patterns for certain use cases.

1. B-tree index: A balanced tree structure that supports efficient equality lookups and range scans by traversing branch blocks to leaf blocks that contain key values and row locators (ROWIDs).
2. Bitmap index: An index structure that uses bitmaps (arrays of bits) to represent the presence or absence of values, particularly efficient for low-cardinality columns and complex Boolean operations.
3. Cardinality: The number of distinct values in a column relative to the total number of rows. High cardinality means many unique values; low cardinality means few unique values.
4. Clustering factor: A statistic that measures how well table rows are ordered relative to index values. A low clustering factor means data is well-ordered; a high clustering factor indicates random physical distribution.
5. Function-based index: An index on an expression (SQL function, arithmetic expression, or deterministic user-defined function) so the optimizer can use the index for predicates that apply that expression.
6. Hot block contention: A performance issue where multiple sessions compete for access to the same data block, typically occurring at the right edge of B-tree indexes with monotonically increasing keys.
7. Leaf block sparsity: A condition where index leaf blocks contain fewer entries than optimal due to deletions, resulting in wasted space and reduced storage efficiency.
8. Reverse-key index: An index where the bytes of the index key are reversed before storage, distributing sequential values across the index structure to reduce hot block contention.

Click the Quiz link below to test your understanding of the concepts presented in this module.

Oracle Index - Quiz