无额外过滤条件下大分区表与小维度表的动态分区Pruning触发问题

1. 调整查询写法适配裁剪逻辑 #

• 优先使用LEFT SEMI JOIN替代INNER JOIN，多数SQL引擎对LEFT SEMI JOIN的分区裁剪支持更直接——它仅聚焦小表中存在的键值，不会生成额外关联结果行，更易触发动态裁剪。
• 禁止对小表键列或大表分区列做任何转换操作（如函数转换、类型强转），必须保持关联条件为直接等值匹配：
  错误示例：ON fact.part_col = CAST(dim.key_col AS STRING)
  正确示例：ON fact.part_col = dim.key_col

2. 强制小表作为驱动表并广播 #

• 显式指定小表为驱动表，让引擎先扫描小表获取所有目标键值，再用这些值过滤大表分区。不同引擎的语法如下：
  • Spark SQL：使用广播提示强制小表广播

    SELECT * FROM fact
    /*+ BROADCAST(dim) */
    LEFT SEMI JOIN dim ON fact.part_col = dim.key_col
    

  • Hive：开启自动小表转换或手动指定MapJoin

    SET hive.auto.convert.join=true;
    SELECT * FROM fact
    /*+ MAPJOIN(dim) */
    LEFT SEMI JOIN dim ON fact.part_col = dim.key_col
    

3. 完善统计信息收集（含列级） #

• 仅计算表级统计信息不够，必须确保小表键列、大表分区列的列级统计信息被正确收集：
  • Spark：

    ANALYZE TABLE dim COMPUTE STATISTICS FOR COLUMNS key_col;
    ANALYZE TABLE fact PARTITION(part_col) COMPUTE STATISTICS;
    

  • Hive：

    ANALYZE TABLE dim COMPUTE STATISTICS FOR COLUMNS key_col;
    ANALYZE TABLE fact PARTITION(part_col) COMPUTE STATISTICS;
    

  准确的列级统计能帮助CBO判断小表数据量，避免误判为大表导致驱动顺序反转。

4. 确认CBO与动态裁剪的核心配置开启 #

• 不同引擎的关键配置存在差异，以下为常见引擎的必要配置：
  • Spark SQL：

    SET spark.sql.cbo.enabled=true;
    SET spark.sql.cbo.joinReorder.enabled=true;
    SET spark.sql.dynamicPartitionPruning.enabled=true;
    SET spark.sql.dynamicPartitionPruning.useStats=true;
    

  • Hive：

    SET hive.cbo.enable=true;
    SET hive.compute.query.using.stats=true;
    SET hive.stats.autogather=true;
    SET hive.optimize.dynamic.partition.pruning=true;
    

5. 绕过无过滤小表的优化逻辑 #

• 当小表无过滤条件时，部分引擎会将其视为全量数据集，跳过动态裁剪逻辑。可添加一个无实际过滤效果的条件（不影响结果）来绕过：

  SELECT * FROM fact
  LEFT SEMI JOIN (SELECT key_col FROM dim WHERE 1=1) dim 
  ON fact.part_col = dim.key_col
  

6. 验证列类型完全匹配 #

• 检查大表分区列与小表键列的类型是否完全一致，隐性类型差异（如INT与BIGINT、DATE与字符串格式日期）会导致引擎无法识别等值关联，从而无法触发裁剪。可通过DESCRIBE命令确认：

  DESCRIBE fact part_col;
  DESCRIBE dim key_col;
  

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