In the world of database tuning, if a single-column index is a search light, a Composite Index (also known as a concatenated index) is a laser beam.

A composite index is a single index that contains multiple columns (e.g., LAST_NAME and FIRST_NAME). When used correctly, it can make your most complex queries run nearly instantaneously. But there are specific "rules of the road" you must follow to make them work.

1. When to Use a Composite Index

You should consider a composite index in the following three scenarios:

A. Highly Correlated Filters

If you have a table with 10 million rows and you frequently run queries like:WHERE region = 'South' AND status = 'Pending' Individually, "South" might return 2 million rows and "Pending" might return 1 million. But combined, they might only return 50 rows. A composite index on (region, status) allows Oracle to jump straight to those 50 rows.

B. Index-Only Access (The "Covering" Index)

This is the holy grail of performance. If your index contains every column that your query needs (both in the WHERE clause and the SELECT clause), Oracle doesn't even have to touch the physical table.

• Example: SELECT email FROM users WHERE user_id = 500;

• If you have an index on (user_id, email), Oracle gets the email directly from the index "leaf" and finishes the job without a single "Table Access" step.

C. Improving Selectivity

Sometimes a single column isn't "unique" enough to be efficient. While LAST_NAME might have many "Smiths," the combination of (LAST_NAME, DATE_OF_BIRTH) is much more unique, making the index more "selective" and faster.

2. The Golden Rule: Column Order Matters

This is where most developers get tripped up. The order of columns in a composite index is critical.

The Leading Column Rule: A composite index on (A, B, C) is highly effective for queries that filter by:

• A

• A and B

• A, B, and C

However, if your query only filters by B and C, Oracle may ignore the index entirely (or perform a much slower "Index Skip Scan").

Strategy: Always put the column you use most frequently (or the one that filters the data most aggressively) in the first position.

3. Comparison: Single vs. Composite

4. The Downsides to Watch For

• Increased Maintenance: Every time you update any of the columns in the index, Oracle has to rewrite the index entry. Don't build a 5-column composite index on a table that sees 1,000 updates per second.

• Redundancy: If you have an index on (A, B), you do not need a separate index on just (A). The composite index handles both. Having both is just wasting disk space and slowing down your inserts.

5. Summary Checklist

Use a composite index if:

1. [ ] You frequently use the same two or more columns in a WHERE clause with AND.

2. [ ] You can "cover" a query to avoid touching the table entirely.

3. [ ] You put the most selective/frequently used column first.

Pro-Tip: The "Prefix" Trick

If you are worried about the index becoming too large, remember that you only need to index the columns that help filter the data. You don't need to add every column in the SELECT list unless you are specifically trying to create a "Covering Index" for a high-traffic query.