Learn about CREATE INDEX, B-Tree and Bitmap indexes, Function-Based Indexes, and best practices for their application to boost your Oracle SQL query speed.

Indexes for Performance

Indexes are schema objects that speed up data retrieval by providing quick access paths to rows in a table. They are essential for improving the performance of SQL queries, especially on large datasets.

CREATE INDEX: B-Tree, Bitmap Indexes

The CREATE INDEX statement is used to create an index on one or more columns of a table. Oracle 19c primarily uses B-Tree indexes for most scenarios and Bitmap indexes for data with low cardinality.

Example 1: CREATE B-Tree Index

CREATE INDEX employees_last_name_idx
ON employees (last_name); -- Creates a B-Tree index on the last_name column

Explanation This code creates a standard B-Tree index on the last_name column of the employees table, accelerating queries that filter or sort by last name.

Example 2: CREATE Unique B-Tree Index

CREATE UNIQUE INDEX employees_email_uk
ON employees (email); -- Creates a unique B-Tree index on the email column

Explanation This statement creates a unique B-Tree index on the email column, ensuring that no two employees can have the same email address.

Example 3: CREATE Composite B-Tree Index

CREATE INDEX orders_cust_prod_idx
ON orders (customer_id, product_id); -- Creates a composite B-Tree index on two columns

Explanation This code creates a composite index on customer_id and product_id, useful for queries filtering on both columns.

Example 4: CREATE Bitmap Index

CREATE BITMAP INDEX products_category_bmi
ON products (category_id); -- Creates a Bitmap index on the category_id column

Explanation This statement creates a Bitmap index on category_id, ideal for columns with few distinct values and typically used in data warehousing.

Example 5: CREATE Index with DESC/ASC Ordering

CREATE INDEX orders_order_date_desc_idx
ON orders (order_date DESC); -- Creates an index with descending order for efficient sorting

Explanation This code creates an index on order_date in descending order, optimizing queries that sort results by order_date in descending fashion.

Function-Based Indexes

Function-Based Indexes (FBIs) are indexes created on expressions or functions. They are invaluable for optimizing queries where the WHERE clause or ORDER BY clause involves functions or expressions on table columns.

Example 1: CREATE Function-Based Index for Uppercase Comparison

CREATE INDEX employees_upper_last_name_fbi
ON employees (UPPER(last_name)); -- Creates an FBI for case-insensitive last name searches

Explanation This index optimizes queries like WHERE UPPER(last_name) = 'SMITH', avoiding full table scans when searching for last names case-insensitively.

Example 2: CREATE Function-Based Index for Date Extraction

CREATE INDEX orders_year_fbi
ON orders (EXTRACT(YEAR FROM order_date)); -- Creates an FBI for filtering by year

Explanation This FBI accelerates queries that extract the year from the order_date column, such as WHERE EXTRACT(YEAR FROM order_date) = 2024.

Example 3: CREATE Function-Based Index for Substring Search

CREATE INDEX products_first_char_fbi
ON products (SUBSTR(product_name, 1, 1)); -- Creates an FBI for searching by first character

Explanation This index helps performance for queries looking up products by their first initial, for instance, WHERE SUBSTR(product_name, 1, 1) = 'A'.

Example 4: CREATE Function-Based Index for Concatenated Columns

CREATE INDEX customers_full_name_fbi
ON customers (first_name || ' ' || last_name); -- Creates an FBI for searches on full name

Explanation This FBI is useful for optimizing searches or sorts based on the concatenated full name, e.g., WHERE first_name || ' ' || last_name = 'John Doe'.

Example 5: CREATE Function-Based Index with NULL Handling

CREATE INDEX employees_commission_pct_fbi
ON employees (NVL(commission_pct, 0)); -- Creates an FBI to handle NULL values as zero

Explanation This index optimizes queries that treat NULL commission percentages as zero, like WHERE NVL(commission_pct, 0) > 0.10.

When to Use and Not Use Indexes

Indexes can significantly improve query performance, but they also incur overhead during DML operations. Understanding when to use them and when to avoid them is crucial for database optimization.

When to Use Indexes

Indexes are beneficial for columns frequently used in WHERE clauses, JOIN conditions, ORDER BY clauses, or GROUP BY clauses. They are especially effective on large tables where only a small percentage of rows are retrieved by a query.

Example 1: Indexing Foreign Keys for Joins

CREATE INDEX order_details_order_id_fk_idx
ON order_details (order_id); -- Indexing a foreign key column for faster joins

Explanation This index dramatically improves the performance of joins between orders and order_details tables on the order_id column.

Example 2: Indexing Columns in WHERE Clauses

CREATE INDEX products_price_idx
ON products (price); -- Indexing a column frequently filtered by

Explanation This index speeds up queries that filter products based on their price, such as SELECT * FROM products WHERE price > 100.

Example 3: Indexing Columns in ORDER BY

CREATE INDEX employees_hire_date_idx
ON employees (hire_date); -- Indexing a column used for sorting results

Explanation This index allows Oracle to retrieve sorted results directly from the index, avoiding a separate sort operation for queries using ORDER BY hire_date.

Example 4: Indexing High Cardinality Columns

CREATE INDEX customers_customer_id_idx
ON customers (customer_id); -- Indexing a unique or highly distinct column

Explanation customer_id is likely unique or has a high number of distinct values (high cardinality), making a B-Tree index very efficient for lookups.

Example 5: Indexing Columns for Aggregation (GROUP BY)

CREATE INDEX sales_region_idx
ON sales (region); -- Indexing a column used in GROUP BY for aggregated reports

Explanation This index helps speed up queries that group sales data by region, reducing the amount of data the database needs to scan for aggregation.

When Not to Use Indexes

Avoid indexing columns in small tables, columns with very few distinct values (low cardinality, unless for bitmap indexes), or columns that are rarely queried. Over-indexing can lead to slower DML operations and increased storage overhead.

Example 1: Small Tables

-- Assume 'lookup_countries' is a very small table (e.g., < 100 rows)
-- CREATE INDEX lookup_countries_code_idx ON lookup_countries (country_code);
-- Indexing small tables often provides no significant performance benefit.

Explanation For very small tables, a full table scan is often faster or as fast as using an index, as the overhead of index lookup outweighs the benefit.

Example 2: Columns with Very Low Cardinality (for B-Tree)

-- Assume 'employees' table has 'gender' column ('M', 'F')
-- CREATE INDEX employees_gender_idx ON employees (gender);
-- A B-Tree index on a column with very few distinct values is generally not effective.

Explanation For columns like gender where only a few distinct values exist, a B-Tree index often causes Oracle to perform a full table scan anyway, rendering the index ineffective. Bitmap indexes are an exception here.

Example 3: Columns Rarely Used in WHERE Clauses

-- Assume 'product_description_last_updated' is rarely used for filtering
-- CREATE INDEX products_desc_updated_idx ON products (product_description_last_updated);
-- Indexing columns not frequently queried is a waste of resources.

Explanation If a column is rarely part of WHERE, ORDER BY, or JOIN clauses, the overhead of maintaining its index outweighs any occasional query benefit.

Example 4: Columns with Frequent DML Operations

-- Assume 'transactions_status' is updated very frequently
-- CREATE INDEX transactions_status_idx ON transactions (status);
-- Frequent updates to an indexed column can degrade DML performance.

Explanation Every INSERT, UPDATE, or DELETE operation on an indexed column requires the index to be updated, which adds overhead and slows down DML.

Example 5: Columns with Large Text Data (unless specific search patterns)

-- Assume 'large_comments' column stores extensive text
-- CREATE INDEX articles_comments_idx ON articles (large_comments);
-- Indexing large text columns without a specific function-based approach is usually inefficient.

Explanation Directly indexing large text columns (VARCHAR2, CLOB) is typically not efficient for general keyword searches; instead, Oracle Text or Function-Based Indexes with specific functions (like SUBSTR) are preferred.

Monitoring Index Usage

Monitoring index usage is crucial to ensure that indexes are effectively contributing to database performance. Oracle 19c provides mechanisms to track whether indexes are being used by queries, helping identify unused or underutilized indexes.

Example 1: Enable Index Monitoring

ALTER INDEX employees_last_name_idx MONITORING USAGE; -- Starts monitoring for a specific index

Explanation This command enables monitoring for the employees_last_name_idx, allowing Oracle to track if and how often this index is used.

Example 2: Query Index Usage View

SELECT index_name, used
FROM V$OBJECT_USAGE
WHERE object_name = 'EMPLOYEES_LAST_NAME_IDX'; -- Checks if the index has been used since monitoring started

Explanation This query against V$OBJECT_USAGE shows whether employees_last_name_idx has been accessed by a query since monitoring was enabled.

Example 3: Disable Index Monitoring

ALTER INDEX employees_last_name_idx NOMONITORING USAGE; -- Stops monitoring for the index

Explanation This command disables usage monitoring for the specified index, ceasing the tracking of its activity.

Example 4: Drop Unused Index (Based on Monitoring)

-- After monitoring for a period and confirming no usage:
-- DROP INDEX employees_unused_idx; -- Removes an index confirmed to be unused

Explanation If monitoring reveals an index is never used, dropping it can reduce DML overhead and save storage space, improving overall database efficiency.

Example 5: Gathering Index Statistics

EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'EMPLOYEES_LAST_NAME_IDX'); -- Gathers statistics for an index

Explanation Gathering index statistics (DBMS_STATS.GATHER_INDEX_STATS) ensures the Oracle optimizer has up-to-date information about the index, helping it make better execution plan decisions.