Recently, on Oracle 184.108.40.206, I saw a join of three tables, using three times TABLE ACCESS FULL and HASH JOIN, without an obvious reason. During separating and understanding its components, the following scenario turned out to be the culprit.
The optimizer is unable to calculate the cardinality of the “OBJECT_ID<DATA_OBJECT_ID“ predicate, and thus, it ends up with underestimating the cardinality, and using a mass access path. (The “gather_plan_statistics” hint was added to see details as real buffer gets and does not change the behavior.)
Execution Plan 1
Fighting a loosing battle(?)
So how does this come? Oracle statistics (even histograms) only store information about the distribution of values in a column. The optimizer can’t combine two column statistics in a way, that would allow to quantify “one is smaller than the other” over the whole table. The CBO can’t combine column statistics anyway, that’s why we have to use extended statistics. But in this case, we simply can’t create an extended statistic representing an inequality predicate.