CTAS语句新增WHERE关联条件后执行无限期卡顿问题优化求助

以下是针对4亿行级大表关联查询超时的可行优化方案：

• SQL语法改写，替换嵌套IN为JOIN关联
  多层IN子查询容易被Oracle优化器判定为相关子查询，触发大表逐行匹配子查询的低效逻辑。改写为显式JOIN+子查询去重的结构，引导优化器生成哈希连接执行计划，参考语句如下：

  CREATE TABLE TEMP_NEW_01 NOLOGGING AS
  SELECT /*+ leading(tc tm td) use_hash(td) */
      td.COL1,td.COL2,td.COL3,td.COL4,td.COL5,td.COL6
  FROM TEMP_DATA td
  INNER JOIN (
      -- 提前对小表结果去重，避免关联时重复匹配
      SELECT DISTINCT tm.COL1
      FROM temp_m2 tm
      INNER JOIN t_category tc 
      ON tm.SHORT_CAPTION = tc.SHORT_CAPTION
      WHERE tc.scat_caption IN ('P','V')
  ) t ON td.COL1 = t.COL1;
  

• 补充关联字段索引，减少全表扫描开销
  针对小表的筛选和关联字段建索引，避免全表扫描小表，符合条件可建覆盖索引跳过回表步骤：
  • 给t_category表的scat_caption字段建索引，高频查询场景可建覆盖索引idx_t_category_scat(scat_caption, SHORT_CAPTION)
  • 给temp_m2表的SHORT_CAPTION字段建索引，可同步建覆盖索引idx_temp_m2_cap(SHORT_CAPTION, COL1)
  • 若TEMP_DATA表的COL1字段重复率低于10%，可补充该字段的普通索引，重复率高则无需建索引避免额外开销
• 预固化小结果集，拆分执行步骤
  先将小表关联得到的过滤结果单独存入临时表，再和大表做关联，避免优化器错误估算行号生成错误执行计划：

  -- 第一步：预生成过滤用的COL1集合
  CREATE TABLE temp_col1_filter NOLOGGING AS
  SELECT DISTINCT tm.COL1
  FROM temp_m2 tm
  INNER JOIN t_category tc ON tm.SHORT_CAPTION = tc.SHORT_CAPTION
  WHERE tc.scat_caption IN ('P','V');
  -- 第二步：关联大表生成目标表
  CREATE TABLE TEMP_NEW_01 NOLOGGING AS
  SELECT td.COL1,td.COL2,td.COL3,td.COL4,td.COL5,td.COL6
  FROM TEMP_DATA td
  INNER JOIN temp_col1_filter t ON td.COL1 = t.COL1;
  

• 开启并行查询加速大表扫描
  服务器CPU资源充足的前提下，给SQL增加并行执行hint，利用多核心资源加速扫描和关联过程，并行度可根据服务器配置调整为CPU核数的1~2倍：

  CREATE TABLE TEMP_NEW_01 NOLOGGING AS
  SELECT /*+ parallel(8) */
      td.COL1,td.COL2,td.COL3,td.COL4,td.COL5,td.COL6
  FROM TEMP_DATA td
  -- 其余关联逻辑不变
  

• 排查执行计划和潜在异常
  先通过执行计划确认是否存在低效执行逻辑：
  • 执行EXPLAIN PLAN FOR 待检查的SQL语句，再执行SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());查看执行计划
  • 确认关联字段是否存在隐式类型转换、字符集不匹配的问题，导致索引失效
  • 确认是否有锁表、表统计信息过时的问题，可重新收集表统计信息后再执行查询

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