Unlock Oracle SQL Performance with the Rare num_index_keys Hint

Background

A SAP month‑end CO module experienced a severe performance issue. The offending SQL contained an IN clause and, despite multiple DBA attempts—including USE_CONCAT hints and index hints—the execution time remained high, causing a two‑day delay and considerable pressure on the DBA team.

Problem Exploration

The statement is a straightforward three‑table join. The original SQL text and its execution plan are shown below.

The statistics are accurate. The plan’s bottleneck is the index access: the optimizer reads only the first indexed column, then fetches the full row set before applying the filter. This results in a large amount of data being filtered after the index access, making the plan inefficient.

Solution Approach

Check index aggregation factors, which can reveal why a table scan is chosen instead of an index scan.

Examine table and index statistics, especially histograms or multi‑column statistics that affect cardinality estimates.

Rewrite the IN list as a series of OR predicates. In SAP environments this is often impractical for production deployment, but it can be useful for testing.

Generate a trace with event 10053 to understand why the optimizer selected the current plan.

Obscure Hint

The rarely used hint NUM_INDEX_KEYS can influence the optimizer’s “IN‑LIST ITERATOR” operation. It tells the optimizer how many index columns may be used for access. Community discussions (e.g., “Access and Filter changes when upgraded to 12c”) and Jonathan Lewis’s blog mention this hint.

Applying NUM_INDEX_KEYS with a value of 2 forces the optimizer to use the first two indexed columns instead of only the first column. The revised plan shows four index scans that avoid fetching the second column’s full data, yielding a dramatic performance improvement.

Conclusion

Filter‑heavy execution plans are generally undesirable, especially when the cost of ACCESS and FILTER are comparable. The NUM_INDEX_KEYS hint can force the optimizer to use additional index columns, reducing filter overhead. In a test case, applying the hint increased logical reads (buffers) and the number of INDEX RANGE SCAN starts, confirming the plan change.

For SQL review, a filter‑heavy plan should be flagged as high‑risk, but each case requires manual verification based on workload characteristics.