Index Selectivity Distortion Under Non-Uniform Data Distributions in Oracle DB

Abstract

Index selectivity plays a central role in Oracle’s cost-based optimization process, yet it becomes highly
unreliable when data distributions exhibit Zipfian or power-law characteristics. In such cases, a small
number of high-frequency values distort the optimizer’s cardinality estimates, leading to inefficient
index range scans, unstable execution plans, and inconsistent query performance. This study examines
how selectivity distortion emerges under non-uniform value frequencies, evaluates the limitations of
histogram-based statistical modeling, and analyzes the conditions under which adaptive cursor sharing
and parameter-sensitivity detection can stabilize plan behavior. The results show that no single tuning
feature is sufficient; performance stability requires coordinated alignment between statistics
maintenance, workload predictability, and physical data organization. When data lifecycle monitoring
and query structure governance are applied consistently, index performance becomes more robust, even
in highly skewed enterprise environments.