Oracle 12C CBO：维度表过滤时如何使用事实表外键直方图？

这个问题太典型了！在数据仓库里遇到这种极端倾斜的维度-事实映射时，CBO很容易在过滤维度业务键时踩坑——毕竟直方图只建在事实表的外键上，它没法自动关联到维度业务键的分布逻辑。下面给你几个实用的解决方案，按推荐优先级排序：

1. 创建多表扩展统计信息（最推荐，一劳永逸） #

Oracle的CBO默认只会做单表统计，但我们可以让它学习到维度表business_key和事实表surrogate_key之间的关联分布。具体来说，创建一个跨FACT和DIM的扩展统计项，让CBO明确知道DIM.business_key = '(blank)'对应FACT.surrogate_key = 0的海量数据。

首先执行这个PL/SQL块创建扩展统计：

BEGIN
  DBMS_STATS.CREATE_EXTENDED_STATS(
    ownname => '你的模式名',
    tabname => 'FACT',
    extension => '(surrogate_key, DIM.business_key)'
  );
END;
/

然后重新收集事实表的统计信息，确保扩展统计被正确计算：

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => '你的模式名',
  tabname => 'FACT',
  cascade => TRUE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS (surrogate_key, DIM.business_key) SIZE AUTO'
);

这样CBO下次处理查询时，就能直接关联到事实表外键的直方图数据，基数估计就会准确了。

2. 用动态采样临时救急 #

如果暂时没法创建扩展统计，可以给查询加动态采样提示，让CBO在编译时直接采样实际数据来估算基数。Oracle 12c默认的动态采样级别是2，我们可以提高到4（更高的级别会采样更多数据，结果更准确）：

SELECT /*+ DYNAMIC_SAMPLING(4) */ * 
FROM FACT, DIM 
WHERE FACT.surrogate_key = DIM.surrogate_key 
  AND DIM.business_key = '(blank)';

这个方法不用修改统计信息，但只对单个查询生效，适合临时调试场景。

3. 手动调整维度表的统计信息（适合特殊场景） #

如果扩展统计不好用，你也可以手动告诉CBODIM.business_key = '(blank)'对应的事实表行数。首先先算出实际的匹配行数：

SELECT COUNT(*) FROM FACT WHERE surrogate_key = 0; -- 得到5300万这个数值

然后更新维度表business_key的直方图，把'(blank)'对应的权重设为实际的事实表行数：

BEGIN
  DBMS_STATS.SET_COLUMN_STATS(
    ownname => '你的模式名',
    tabname => 'DIM',
    colname => 'business_key',
    histtype => 'FREQUENCY',
    endpoint_values => SYS.DBMS_STATS.VARCHAR2_ARRAY('(blank)'),
    endpoint_counts => SYS.DBMS_STATS.NUMBER_ARRAY(53000000)
  );
END;
/

不过这个方法需要手动维护，每次数据变化都要更新，不如扩展统计省心。

4. 重写查询（快速临时解决方案） #

如果不想碰统计信息，也可以调整查询写法，让CBO先找到维度的代理键，再用事实表的直方图估算行数：

-- 子查询写法
SELECT * 
FROM FACT 
WHERE surrogate_key = (SELECT surrogate_key FROM DIM WHERE business_key = '(blank)');

-- 或者CTE写法
WITH dim_blank AS (
  SELECT surrogate_key FROM DIM WHERE business_key = '(blank)'
)
SELECT * FROM FACT JOIN dim_blank ON FACT.surrogate_key = dim_blank.surrogate_key;

这种写法会让CBO先定位到维度表的1行结果，然后直接用事实表外键的直方图来计算匹配行数，基数估计就会准确了。

总结一下，优先用多表扩展统计，这是最可持续的方案，不需要后续手动维护。动态采样和查询重写适合临时解决问题，手动调整统计只在特殊场景下考虑。

内容的提问来源于stack exchange，提问作者aloofball