Scd-Type-2维度与事实表Range Join优化：Between关联更优方案问询

问题背景 #

我有一张销售事实表和一张SCD-Type-2员工维度表，需生成按区域和年份统计的销售报表。当前关联查询逻辑可行，但在Spark/Databricks执行时收到提示：

Use range join optimization: This query has a join condition that can benefit from range join optimization. To improve performance, consider adding a range join hint.

想请教：使用Between条件关联时，是否存在更优的查询方式？

事实表结构与数据 #

create table sales
(name             string
,sale_date        date
,sold_amt         long);

insert into sales values
('John','2022-02-02',100),
('John','2022-03-03',100),
('John','2023-02-02',200),
('John','2023-03-03',200),
('Rick','2022-02-02',300),
('Rick','2023-02-02',400);

SCD-Type-2维度表结构与数据 #

create table employee_scd2
(name        string
,region      string
,start_date  date
,end_date    date
,is_current  boolean); -- 未使用，仅保留完整性

insert into employee_scd2 values
('John','NAM',  '2010-01-01', '2022-12-31', false),
-- John于2023年从NAM调任至APAC
('John','APAC', '2023-01-01', '9999-01-01', true),
('Rick','NAM',  '2020-01-01', '9999-12-31', true);

当前查询语句 #

select e.region,
       year(s.sale_date) as sale_year,
       SUM(s.sold_amt)   as sale_amt
from       sales s
left join  employee_scd2 e
  on     e.name       = s.name
  and    s.sale_date between e.start_date and e.end_date
group by e.region, year(s.sale_date);

优化方案 #

1. 核心优化：添加范围连接提示 #

Spark默认不会自动识别并启用最优的范围连接策略，针对等值+范围的连接条件，直接在JOIN子句中添加/*+ RANGE_JOIN(e.start_date, e.end_date) */提示，强制优化器使用范围连接，避免低效的全表比较。

优化后的查询：

select e.region,
       year(s.sale_date) as sale_year,
       SUM(s.sold_amt)   as sale_amt
from       sales s
left join  /*+ RANGE_JOIN(e.start_date, e.end_date) */ employee_scd2 e
  on     e.name       = s.name
  and    s.sale_date between e.start_date and e.end_date
group by e.region, year(s.sale_date);

2. 辅助性能提升手段 #

• 预分区与索引优化：
  • 对sales表按name和sale_date分区，employee_scd2表按name分区；
  • 在Databricks中为employee_scd2的start_date、end_date创建Z-Order索引，或在Spark中创建B树索引，减少扫描的数据范围。
• 提前过滤数据：
  如果报表仅需特定年份数据，先过滤sales表的无关记录，再执行关联，缩小计算规模：

  select e.region,
         year(s.sale_date) as sale_year,
         SUM(s.sold_amt)   as sale_amt
  from       (select * from sales where year(sale_date) in (2022, 2023)) s
  left join  /*+ RANGE_JOIN(e.start_date, e.end_date) */ employee_scd2 e
    on     e.name       = s.name
    and    s.sale_date between e.start_date and e.end_date
  group by e.region, year(s.sale_date);
  

• 精简查询字段：
  仅选择必要字段，避免扫描is_current等未使用字段，减少数据传输量。

优化原理 #

你的关联逻辑属于等值连接（name）+范围连接（sale_date在start_date与end_date之间），范围连接优化会按等值键分区数据，在每个分区内对日期字段进行有序匹配，避免传统嵌套循环或哈希连接的低效，数据量越大性能提升越显著。

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