Optimizing Composite Indexes in Oracle

Optimizing Composite Indexes in Oracle: Balancing Selectivity and Index Skip Scan

How to Decide the Leading Column Order in Oracle Composite Indexes? (Using Index Skip Scan Effectively)

As a Developer or Application DBA you may have challenges to setting fist column in composite indexes as a Leading Column based on many SQL statements in your environment, in this topic I demonstrate all aspects of these situations and finally present a formula to handling these type of concerns.

In the previous post, I have a comparison between Oracle and PostgreSQL in Skip Scan concept, it it better to fist visit mentioned post to get a deeply detailed guide.

https://lnkd.in/dP6f3hU2

🔹 1. General rule: leading column should be highly selective

In most cases, Oracle recommends that the most selective column should come first in a composite index.

Reason:
• The optimizer can use the index efficiently when filtering by the first column.
• The fewer rows per key value, the faster it can find matching entries.

Example:

CREATE INDEX idx_t_color_machine ON t (machine_id, color);

If machine_id has many distinct values (high selectivity), this is good when most queries filter on machine_id first.

🔹 2. Problem: what if your queries filter by different combinations?
Let’s say you have queries like:

SELECT * FROM t WHERE color = 'BLUE';
SELECT * FROM t WHERE color = 'RED' AND machine_id = 123;
SELECT * FROM t WHERE machine_id = 123;

Now you have a dilemma:
• If you make (color, machine_id) → good for the first two, bad for the third.
• If you make (machine_id, color) → good for the last two, bad for the first.

So what’s best?

🔹 3. Enter: Index Skip Scan

Oracle can skip scan a composite index when the leading column is not specified in the WHERE clause — but only if the leading column has low cardinality (few distinct values).

So if color has few values like 'RED', 'BLUE', 'GREEN', and machine_id is very selective, then:

CREATE INDEX idx_t_color_machine ON t (color, machine_id);

is actually useful for all these:
• WHERE color = 'BLUE' → normal index range scan
• WHERE color = 'RED' AND machine_id = 123 → normal index range scan
• WHERE machine_id = 123 → index skip scan may kick in
Oracle internally scans index partitions per color value and then looks for machine_id=123.
This is efficient only if the number of distinct color values is small.

🔹 4. Summary table

▶️ If most queries filter on one specific column (and it’s highly selective):
→ Put that selective column first in the index.
Reason: Oracle can use it directly for fast lookups.

▶️ If most queries filter on both columns together:
→ Put the most selective column first.
Reason: Classic composite index design — minimizes scanned rows.

▶️ If some queries omit the first column, and that column has low cardinality (few distinct values):
→ Put that low-cardinality column first.
Reason: Enables Index Skip Scan, allowing Oracle to still use the index efficiently even when the first column isn’t in the WHERE clause.

▶️ If some queries omit the first column, and that column has high cardinality (many distinct values):
→ Don’t rely on skip scans — instead, create separate indexes for different query patterns.
Reason: Skip scans become inefficient when the leading column has too many distinct values.

🔹 5. example
Index created on  (color, machine_id)
And queries are:

1. SELECT * FROM table WHERE color = 'blue';
2. SELECT * FROM table WHERE color = 'red' AND machine_id = 123;
3. SELECT * FROM table WHERE machine_id = 123;

If:
• color = low cardinality (say 5–10 colors)
• machine_id = high cardinality (many machines)

Then  (color, machine_id) is actually best:
• #1 and #2 → normal index scan
• #3 → index skip scan (because color has few distinct values)

But if:
• color = high cardinality (hundreds of distinct values)
• machine_id = low cardinality
Then skip scan will be expensive → better to use (machine_id, color) or two separate indexes.

🟪 Final Recommendation

IF (leading column used in most queries)
    THEN make it leading
ELSE IF (leading column has low cardinality AND others have high)
    THEN put low-cardinality column first (for skip scan)
ELSE
    use separate indexes;