Oracle SQL优化求助：40M与240M行表关联及CASE语句查询耗时过长

涉及表信息 #

• hit4表：40M行，用于JOIN操作
• trx表：240M行，用于JOIN操作
• avg_hits_pk表：仅含1个浮点值，用于CASE语句
• avg_hits_npk表：仅含1个浮点值，用于CASE语句
• params表：10行参数表，用于SELECT子句

原查询语句 #

SELECT DISTINCT
  trx.trx_id,
  hit4.customer_id,
  1 as value_pattern,
  hit4.trx_date,
  trx.trx_amount,
  role,
  tiv,
  tov,
  ratio,
  number_hits,
  CASE
      WHEN segment = 'PK' THEN round((SELECT avg_hits FROM avg_hits_pk))
      ELSE round((SELECT avg_hits FROM avg_hits_npk))
  END AS avg_hits,
  (SELECT param_value FROM params WHERE param_name = 'CSTR') as CSTR,
  trx.trx_type
FROM hit4
LEFT JOIN trx
  ON hit4.customer_id = trx.customer_id AND hit4.trx_date = trx.trx_date

优化建议 #

• 移除不必要的DISTINCT：DISTINCT会触发全量数据排序去重，对于百万级大表来说是极高开销的操作。先确认业务逻辑是否真的需要去重，若JOIN后出现重复行，优先检查JOIN条件是否存在逻辑漏洞，而非直接用DISTINCT掩盖问题。
• 优化JOIN索引：为trx表的customer_id和trx_date创建联合索引（而非单独两个索引），多条件JOIN场景下联合索引的匹配效率远高于单字段索引组合。如果hit4表作为驱动表，也可以在其customer_id+trx_date字段上创建索引，进一步降低JOIN的匹配成本。
• 替换标量子查询：SELECT子句中的标量子查询会逐行执行，对于百万级结果集来说会重复执行数百万次。可以：
  • 提前查询出avg_hits_pk、avg_hits_npk、CSTR的值，用变量存储后直接在主查询中引用；
  • 将三个小表做笛卡尔积JOIN到主查询中（因数据量极小，笛卡尔积无性能影响），避免逐行查询。
• 过滤大表数据：如果业务不需要hit4表的全量数据，添加WHERE条件提前过滤掉无关行，减少后续JOIN和计算的数据量。
• 分析执行计划：用EXPLAIN PLAN FOR查看查询的执行计划，重点关注是否存在全表扫描、排序操作（如SORT UNIQUE），针对性调整索引或语句逻辑。

已解决记录 #

已解决：
我为trx.trx_date和trx.customer_id添加了索引，移除了语句中的DISTINCT，并对hit4表进行过滤，现在执行耗时约7分钟。感谢帮助！

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