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;
