EXPLAIN says tablespace scan - index looks perfect on paper

Predicate is on the leading column of a unique index. RUNSTATS were this week. EXPLAIN still wants a tablespace scan on a 20M row table.

Host variable is typed differently than the column (CHAR vs VARCHAR). Could that alone kill matching?

Yes. Type mismatch can disable matching index use. Align the host variable with the column definition. Also check for residual predicates (stage 2) hiding in the WHERE.

And make sure you are looking at the plan that is actually bound in that environment. We wasted a day tuning a package that prod was not running.

Host var was VARCHAR, column CHAR. Fixed the declaration, rebound, index match. Feels silly in hindsight.