Oracle 的谓词越界面积衰减计算（Area Under the Curve - AUC / Out-of-Range Value Selectivity Estimation）。这是一个非常精妙且体现了 Oracle CBO 成熟度的特性，旨在解决统计信息估算中的一个经典痛点。

背景：谓词越界问题

1. 场景： 当 SQL 查询包含一个范围谓词（如 WHERE column > :val 或 WHERE column < :val 或 WHERE column BETWEEN :low AND :high ），并且绑定变量 :val （或 :low / :high ）的值落在该列当前统计信息记录的最小值（ min ）和最大值（ max ） 之外 时，即发生谓词越界。

2. 传统一刀切（PG/MySQL等）的问题：

   • column > :val 且 :val > max ： 逻辑上应该返回 0 行。一刀切方法通常直接估算为 0 行或一个极小值（如 1 / num_rows ）。

   • column < :val 且 :val < min ： 逻辑上应该返回 0 行。一刀切方法同样估算为 0 行或极小值。

   • column BETWEEN :low AND :high 且整个区间都在 [min, max] 之外： 逻辑上应该返回 0 行。一刀切估算为 0 行或极小值。

   • column > :val 且 :val < min ： 逻辑上应该返回 所有行 （因为所有值都大于 min 自然也就大于 :val ）。一刀切方法通常能正确估算为 num_rows 或接近 num_rows （考虑 NULL）。

   • 核心痛点： column < :val 且 :val > max ： 逻辑上也应该返回 所有行 （因为所有值都小于 max 自然也就小于 :val ）。然而， 一刀切方法在这里常犯严重错误！ 许多优化器（如 PG, MySQL）会简单地估算为 0 行或一个极小值（如 1 / num_rows ）。

3. 一刀切估算的危害：

   • 严重低估： 将本应返回所有行（或大量行）的查询估算为只有 0 或 1 行。

   • 灾难性执行计划：

     • 错误选择索引： 优化器可能认为只需读取 1 行，错误地选择了一个高选择性但需要回表的索引（Index Scan + RID Lookup / Bookmark Lookup），而实际上全表扫描（Full Table Scan）或覆盖索引扫描（Index Only Scan）才是最优的。读取少量行的计划（索引访问）在需要读取所有行时效率极低。

     • 错误的 JOIN 类型： 如果这种低估发生在 JOIN 的驱动表上，优化器可能错误地选择 Nested Loops Join，因为它预估驱动表只有 1 行。而实际上驱动表有大量行，导致昂贵的笛卡尔积效应（驱动表的每一行都要扫描内表全表）。

     • 错误的连接顺序： 低估会导致优化器错误地评估不同连接顺序的成本。

   • 执行计划不稳定： 当绑定变量值从边界内移动到边界外时，估算值会从某个合理值（基于直方图或密度） 突然暴跌 到 0 或 1，导致执行计划发生剧烈变化（Plan Instability），即使实际结果集大小变化是连续的。

Oracle 的解决方案：面积衰减计算 (AUC)

Oracle 的 CBO 意识到 column < :val 且 :val > max 这种场景应该返回所有行，而 column > :val 且 :val > max 应该返回 0 行。但它没有采用简单的二元判断（0行 or 所有行），而是引入了一个更符合现实、更平滑的估算模型—— 面积衰减（Area Under the Curve） 。核心思想是： 越界的程度越深，返回所有行的可能性就越低（对于 column < :val 且 :val > max ）或者返回 0 行的确定性越高（对于 column > :val 且 :val > max ），但这个过程是渐进的，不是突变的。

ORACLE谓词越界选择率梯形面积衰减计算公式： 总行数 (N) 相异值数量 (NDV) 统计范围：最小值 (LV) 统计范围：最大值 (HV) 查询条件：谓词值 (PV) 不越界大于PV选择率计算： N * (HV - PV) / (HV - LV) 不越界小于PV选择率计算： N * (PV - LV) / (HV - LV) 大于PV谓词越界： (N/NDV) * (1 - (PV-HV)/(HV-LV)) 小于PV谓词越界： (N/NDV) * (1 - (LV-PV)/(HV-LV))

梯形概率分布模型 ：

选择率密度
   ↑
   |       梯形区域
   |      /----------\
   |     /            \
   |    /              \
   |---/----------------\----→ 列值
  LV                   HV

• 统计范围内的均匀分布 ：假设数据在[LV, HV]内均匀分布

• 统计范围外的线性衰减 ：假设数据在边界外以线性方式衰减到0

公式推导与验证

情况1：不越界时（PV在[LV, HV]内）

1. 大于PV选择率 ： N * (HV - PV) / (HV - LV)

   • 几何解释：梯形右侧部分的面积

   • 当PV=LV时： N * (HV - LV) / (HV - LV) = N (全部行)

   • 当PV=HV时： N * 0 / (HV - LV) = 0 (0行)

2. 小于PV选择率 ： N * (PV - LV) / (HV - LV)

   • 几何解释：梯形左侧部分的面积

   • 当PV=LV时： N * 0 / (HV - LV) = 0 (0行)

   • 当PV=HV时： N * (HV - LV) / (HV - LV) = N (全部行)

情况2：大于PV谓词越界（PV > HV）

公式 ： (N/NDV) * (1 - (PV-HV)/(HV-LV))

推导思路 ：

1. 基本单位 ： N/NDV 是每个不同值的平均行数

2. 衰减因子 ： 1 - (PV-HV)/(HV-LV)

   • 当PV=HV时： 1 - 0/(HV-LV) = 1 → 选择率 = N/NDV

   • 随着PV增大， (PV-HV)/(HV-LV) 增大，衰减因子减小

   • 当PV = HV + (HV-LV)时： 1 - 1 = 0 → 选择率 = 0

几何解释 ：从HV点开始，向右线性衰减到0

情况3：小于PV谓词越界（PV < LV）

公式 ： (N/NDV) * (1 - (LV-PV)/(HV-LV))

对称的衰减过程：

• 从LV点向左线性衰减

• 当PV = LV - (HV-LV)时，选择率衰减到0

原理与数学公式

1. 核心概念：假设数据分布形状： AUC 估算依赖于列上是否存在 直方图（Histogram） 。Oracle 假设在 min 和 max 之间的数据分布形状（由直方图描述）会在边界外以某种方式 延续 。通常假设为 指数衰减（Exponential Decay） 。这是模型的关键假设。

2. 计算可用面积（Available Area）： 首先计算 min 到 max 之间直方图所覆盖的总“面积”。在一个等宽直方图中，这大致对应于 num_rows （假设没有 NULL）。在频率直方图或混合直方图中，就是所有桶的总频率或总 Cardinality。

3. 计算衰减面积（Decaying Area）： 对于越界的谓词，计算从 min 或 max 延伸到越界值 :val 的“衰减区域”的面积。

4. 估算选择率（Selectivity）：

   • 场景 A: column < :val 且 :val > max (最常见且最需要衰减的场景)

     • 逻辑： 所有 min 到 max 的行都满足条件。对于 max 到 :val 的部分，Oracle 假设满足条件的行数会随着远离 max 而 指数衰减 。

     • 公式 (简化表示)：
       selectivity = (Area(min->max) + Area(max->:val)) / (Area(min->max) + Area(max->:val) + Area(:val->Infinity))

     • 关键点： Area(min->max) 是已知的（≈ num_rows ）。 Area(max->:val) 和 Area(:val->Infinity) 是模型估算的。核心在于 Area(max->:val) 的衰减计算。一个常用的简化模型是：
       Area(max->:val) = Area(min->max) * [ (distance_from_max) / ( (max - min) + distance_from_max ) ]
       其中 distance_from_max = :val - max 。

     • 结果： 当 :val 刚刚超过 max ( distance_from_max 很小) 时， Area(max->:val) 相对较大， selectivity 接近 1.0 (所有行)。随着 :val 远远超过 max ( distance_from_max 很大)， Area(max->:val) 趋近于 Area(min->max) * 1 (即 num_rows )，而 Area(:val->Infinity) 变得巨大，使得 selectivity 趋近于 (num_rows + num_rows) / (num_rows + num_rows + huge_number) ≈ 0 。 这是一个平滑的过渡。

   • 场景 B: column > :val 且 :val > max

     • 逻辑： 没有行在 :val 之上，因为 :val 已经比表中最大值还大。

     • 公式 (简化)：
       selectivity = Area(:val->Infinity) / (Area(min->max) + Area(max->:val) + Area(:val->Infinity))

     • 结果： 当 :val 刚刚超过 max 时， Area(:val->Infinity) 被估算为一个很小的值（但不是0）， selectivity 是一个很小的正数（大于0但远小于1）。随着 :val 远离 max ， Area(:val->Infinity) 迅速衰减到接近 0， selectivity 也趋近于 0。 避免了突然跳到绝对 0，但最终趋近于 0。

   • 场景 C: column < :val 且 :val < min

     • 逻辑： 没有行在 :val 之下。

     • 处理： 类似场景 B，估算一个很小的选择率（最终趋近于0）。

   • 场景 D: column > :val 且 :val < min

     • 逻辑： 所有行都满足条件。

     • 处理： Oracle 通常能正确估算为 num_rows (或 num_not_null )，不需要 AUC。一刀切方法通常也能正确处理此场景。

   • 场景 E: BETWEEN :low AND :high 完全越界

     • 处理： 根据 :low 和 :high 相对于 min / max 的位置，组合应用上述 A-D 场景的逻辑进行估算。

好处

1. 避免严重低估： 最主要的好处！ 完美解决了 column < :val 且 :val > max 被严重低估为 0 或 1 行的问题。 当 :val 刚超过 max 时，估算值仍然接近 num_rows 。

2. 更平滑的估算过渡： 当绑定变量值从边界内连续变化到边界外时，估算的选择率变化也是 连续且平滑 的，避免了悬崖效应（Cliff Effect）。这极大地提高了 执行计划的稳定性 。一个执行计划在 :val = max 和 :val = max + 1 时更可能保持一致。

3. 更符合现实数据分布： 指数衰减的假设虽然不完全精确，但比“所有值都严格小于等于 max ，超过 max 就绝对没有”的二元假设更符合许多真实数据集的特性（如自然增长但存在上限的 ID、随时间衰减的活跃度等）。它承认边界外可能存在零星或潜在的数据（即使当前没有），但可能性迅速降低。

4. 减少错误执行计划： 通过提供更合理的估算（特别是在 column < :val 且 :val > max 时返回接近 num_rows 的值），优化器更有可能选择正确的访问路径（如 FTS / Index Fast Full Scan）和 JOIN 方法（如 Hash Join 或 Merge Join），避免灾难性的 Nested Loops + 笛卡尔积。

5. 提升优化器健壮性： 对统计信息暂时未更新的情况（新数据插入导致 max 变大但统计信息未及时收集）有更好的容忍度。

坏处 / 挑战

1. 模型假设未必准确： 核心依赖“数据分布在边界外指数衰减”的假设。这 并非普遍真理 。有些数据可能确实存在硬性上限（如状态码），超过就绝对不会有数据，此时 AUC 估算（返回接近但小于 1.0 的选择率）反而 不如返回 0 精确 。或者数据分布可能是均匀的、突然截断的，指数衰减模型也不适用。 模型风险始终存在。

2. 计算复杂性增加： 相比简单的二元判断（ 0 或 num_rows ），AUC 计算涉及更多的算术运算和逻辑判断（需要区分不同越界场景）。虽然单次计算成本不高，但在需要解析海量 SQL 或复杂查询时，累积开销比简单方法大。

3. 依赖直方图质量： AUC 估算的质量直接依赖于直方图是否能准确反映 min 到 max 之间的数据分布形状。如果直方图桶数不足、统计信息过期或数据倾斜未被直方图捕捉，AUC 估算的准确性也会下降。

4. 潜在的高估： 在 column > :val 且 :val > max 的场景下，AUC 不会立即返回 0，而是返回一个很小的正数。在绝对不可能有数据的场景下，这是一个 轻微的高估 （虽然通常比严重低估的危害小得多）。

5. 可预测性/可调性： 对于 DBA 和开发者来说，理解 AUC 的行为比理解简单的 0 / num_rows 规则更复杂。当发生估算偏差时，精确调整或覆盖（如用 Hint 或 SQL Profile）可能更困难。

6. 参数依赖： Oracle 有一些隐藏参数（如 _beyond_max_selectivity , _before_min_selectivity ）可能影响 AUC 的行为，增加了内部机制的复杂度。

总结对比

结论：

Oracle 的谓词越界面积衰减计算（AUC）是 CBO 成熟度的一个重要标志。它通过引入基于指数衰减模型的平滑估算， 有效解决了传统“一刀切”方法在处理 column < :val 且 :val > max 谓词时导致的灾难性低估问题 ，并显著提高了执行计划在边界值附近的稳定性。尽管其模型假设存在局限性且计算稍复杂，但在绝大多数涉及连续变化数据或统计信息可能滞后的实际场景中，AUC 带来的好处（避免错误计划、提高稳定性）远大于其潜在的缺点。相比之下，PG 和 MySQL 等数据库采用的简单方法虽然实现成本低，但在处理特定类型的越界谓词时容易引发严重的性能问题，是其优化器有待改进的一个方面。理解这种差异对于诊断和优化跨数据库应用的性能问题至关重要。