Teradata非等值连接性能极差问题求助

我们有两张业务表：

• TRANSACTIONS：存储交易明细，单日数据量约45万行
• BIC_COUNTRY_RANGES（查询语句中写为BIC_COUNTRY_CODES）：存储卡号范围与对应发卡国家的静态映射表，已通过合并重叠范围将行数从100万缩减至16.8万行

业务需求是通过CARD_TYPE等值匹配，同时判断TRANSACTIONS表的CARD_NUMBER是否落在BIC表的RANGE_START与RANGE_END区间内，实现非等值连接，获取每笔交易对应的发卡国家。

当前遇到的问题：单日数据的连接查询耗时约30分钟，但业务需要处理全月数据。已完成的前置优化动作：

• 在TRANSACTIONS表的CARD_TYPE、CARD_NUMBER字段创建索引
• 在BIC_COUNTRY_RANGES表的CARD_TYPE、RANGE_START、RANGE_END字段创建索引
• 合并BIC表内的重叠卡号范围，将行数从100万压缩至16.8万

测试验证：若替换为纯等值连接，查询仅需数秒；且已确认同CARD_TYPE下的卡号范围无重叠。

以下是当前查询语句与执行计划，请求分析耗时原因并给出优化建议。

SELECT *
FROM TRANSACTIONS T
LEFT JOIN
BIC_COUNTRY_CODES B
ON B.RANGE_START <= T.CARD_NUMBER AND
B.RANGE_END >= T.CARD_NUMBER AND
B.CARD_TYPE = T.CARD_TYPE;

1) 首先，在TD_MAP1中对TRANSACTIONS表加读锁（保留RowHash）以防止全局死锁。
2) 接着，在TD_MAP1中对BIC_COUNTRY_CODES表加读锁（保留RowHash）以防止全局死锁。
3) 在TD_MAP1中对两张表正式加读锁。
4) 并行执行以下两个步骤：
     1) 在TD_MAP1中对BIC_COUNTRY_CODES执行全表扫描，过滤掉CARD_TYPE为NULL的行，结果存入Spool 2（所有AMP节点），再按CARD_TYPE的哈希值重分布到所有AMP，之后按行哈希排序。Spool 2预估行数167,424行（9,208,320字节），预估耗时0.02秒（置信度低）。
     2) 在TD_MAP1中对TRANSACTIONS执行全表扫描（无过滤条件），结果存入Spool 3（所有AMP节点），再按CARD_TYPE的哈希值重分布到所有AMP，之后按行哈希排序。Spool 3预估行数475,776行（16,176,384字节），预估耗时0.03秒（置信度低）。
5) 在TD_MAP1中执行全AMP连接：从Spool 2（最后一次使用）通过RowHash匹配扫描，与Spool 3（最后一次使用）通过RowHash匹配扫描进行**右外连接**，采用合并连接算法，非匹配过滤条件为"NOT (CARD_TYPE IS NULL)"，连接条件为"(RANGE_START <= CARD_NUMBER) AND ((RANGE_END >= CARD_NUMBER) AND (CARD_TYPE = CARD_TYPE))"。结果存入Spool 1（本地AMP构建）。Spool 1预估行数8,642,638行（777,837,420字节），预估耗时0.12秒（无置信度）。
6) 最后，向所有参与处理的AMP发送事务结束指令。
-> Spool 1的内容作为查询结果返回给用户，总预估耗时0.15秒。

1. 统计信息严重失真：执行计划预估总耗时0.15秒，但实际耗时30分钟，说明Teradata优化器依赖的表统计信息过时或不准确，导致优化器选择了完全不符合实际数据情况的低效执行路径。
2. 合并连接的非等值适配低效：当前采用的合并连接，在处理非等值区间匹配时，需对同CARD_TYPE下的所有交易卡号与所有范围逐一比对，时间复杂度接近O(N*M)（N为该类型交易数，M为该类型范围数），当某类卡的交易或范围数量较大时，会产生海量比对操作。
3. 索引未被有效利用：执行计划显示两张表均走全表扫描，说明优化器认为全表扫描比索引扫描更高效，这大概率是统计信息错误导致的判断偏差，或者单独字段的索引无法覆盖非等值连接的联合查询需求。
4. 数据重分布的负载倾斜：两张表均按CARD_TYPE哈希重分布到所有AMP，若某类卡的交易或范围数据占比极高，会导致部分AMP节点负载过高，成为性能瓶颈。

1. 更新统计信息 #

先修复统计信息失真问题，让优化器能做出正确的执行决策：

-- 收集TRANSACTIONS表的联合字段统计信息
COLLECT STATISTICS COLUMN (CARD_TYPE, CARD_NUMBER) ON TRANSACTIONS;
-- 收集BIC表的联合字段统计信息
COLLECT STATISTICS COLUMN (CARD_TYPE, RANGE_START, RANGE_END) ON BIC_COUNTRY_CODES;
-- 收集表级统计信息
COLLECT STATISTICS ON TRANSACTIONS;
COLLECT STATISTICS ON BIC_COUNTRY_CODES;

2. 重构表结构实现等值连接（核心优化） #

已知同CARD_TYPE下的卡号范围无重叠，可将区间匹配转化为等值匹配，彻底解决非等值连接的性能问题：

方案A：基于卡号前缀的等值匹配 #

如果卡号范围是按前缀（如前6位BIN号）划分的，可新增计算列实现前缀匹配：

• 为TRANSACTIONS表新增卡号前缀计算列：

  ALTER TABLE TRANSACTIONS ADD COLUMN CARD_BIN VARCHAR(6) AS SUBSTR(CARD_NUMBER, 1, 6);
  

• 预处理BIC表，将每个无重叠区间对应的唯一前缀提取出来，生成一张新的BIC_BIN_MAPPING表（包含CARD_TYPE、CARD_BIN、COUNTRY等字段）
• 改为等值连接查询：

  SELECT T.*, B.COUNTRY
  FROM TRANSACTIONS T
  LEFT JOIN BIC_BIN_MAPPING B
  ON T.CARD_TYPE = B.CARD_TYPE AND T.CARD_BIN = B.CARD_BIN;
  

方案B：基于区间编码的等值匹配 #

如果卡号是数值类型，可为每个无重叠区间分配唯一ID，通过编码实现等值匹配：

• 在BIC表中新增INTERVAL_ID字段，每个CARD_TYPE+区间对应唯一ID
• 创建UDF函数，输入CARD_TYPE和CARD_NUMBER，通过二分查找快速返回对应的INTERVAL_ID
• 在TRANSACTIONS表中新增计算列或实时调用UDF获取INTERVAL_ID，之后与BIC表按CARD_TYPE+INTERVAL_ID等值连接

3. 优化索引设计 #

针对当前非等值连接场景，创建联合覆盖索引，避免全表扫描：

-- TRANSACTIONS表的联合索引，覆盖连接所需字段
CREATE INDEX IX_TRANS_CARD_TYPE_NUMBER ON TRANSACTIONS (CARD_TYPE, CARD_NUMBER);

-- BIC表的联合索引，按CARD_TYPE排序，同时包含区间字段和国家字段
CREATE INDEX IX_BIC_CARD_TYPE_RANGE ON BIC_COUNTRY_CODES (CARD_TYPE, RANGE_START, RANGE_END) INCLUDE (COUNTRY);

4. 强制使用嵌套循环连接 #

如果同CARD_TYPE下的BIC表数据量较小，可强制优化器使用嵌套循环连接（小表驱动大表效率更高）：

SELECT /*+ USE_NL(B) */ *
FROM TRANSACTIONS T
LEFT JOIN BIC_COUNTRY_CODES B
ON B.CARD_TYPE = T.CARD_TYPE AND B.RANGE_START <= T.CARD_NUMBER AND B.RANGE_END >= T.CARD_NUMBER;

由于范围无重叠，可通过QUALIFY确保每条交易只返回一个匹配结果：

SELECT *
FROM TRANSACTIONS T
LEFT JOIN BIC_COUNTRY_CODES B
ON B.CARD_TYPE = T.CARD_TYPE AND B.RANGE_START <= T.CARD_NUMBER AND B.RANGE_END >= T.CARD_NUMBER
QUALIFY ROW_NUMBER() OVER (PARTITION BY T.TRANSACTION_ID ORDER BY 1) = 1;

5. 预处理中间表 #

如果业务允许，可按日预处理交易与国家的匹配结果，全月查询直接读取中间表，避免重复计算：

CREATE TABLE TRANSACTION_COUNTRY_MAPPING AS
SELECT T.*, B.COUNTRY
FROM TRANSACTIONS T
LEFT JOIN BIC_COUNTRY_CODES B
ON B.CARD_TYPE = T.CARD_TYPE AND B.RANGE_START <= T.CARD_NUMBER AND B.RANGE_END >= T.CARD_NUMBER
WITH DATA;

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