1. IN查询被转换为Exists的原因及Oracle自动转换逻辑 #

这是Oracle基于成本的优化器（CBO）的正常行为，核心目的是生成更高效的执行计划。具体原因包括：

• 半连接特性：Exists属于半连接（Semi-Join），只要找到匹配的记录就会停止扫描，不需要处理所有符合条件的数据；而IN查询在某些场景下（比如子查询返回大量重复值），需要先去重再匹配，成本更高。CBO会自动评估两种写法的执行成本，当Exists的预估成本更低时，就会做这个转换。
• 数据分布适配：如果主表数据量很大，而子查询的过滤条件能快速定位匹配行，Exists的嵌套循环连接会比IN的哈希连接或合并连接更高效。另外，当子查询涉及多表关联时，CBO也更倾向于转换为Exists来减少不必要的数据处理。

简单说，Oracle会自动根据数据量、统计信息、索引情况等因素，选择IN或Exists中成本更低的执行路径，转换是优化器的自主决策，不是语法层面的强制转换。

2. 处理~分隔字符串参数的查询优化建议 #

你现在的场景是传入长串分隔值，执行耗时10-15分钟，大概率是字符串拆分+关联的环节拖慢了速度，给你几个针对性优化方向：

• 替换低效的字符串拆分方法
  避免用递归CONNECT BY或者老旧的自定义拆分函数，改用Oracle 12c+支持的高效拆分方式：

  -- 用JSON_TABLE拆分（推荐，性能稳定）
  SELECT trim(value) AS id
  FROM JSON_TABLE('["' || REPLACE(:param, '~', '","') || '"]', '$[*]' COLUMNS value VARCHAR2(20) PATH '$')
  

  或者用XMLTABLE：

  SELECT trim(column_value) AS id
  FROM XMLTABLE(('"' || REPLACE(:param, '~', '","') || '"'))
  

  这些方法比递归拆分的效率高很多，尤其是处理几百个值的长串时。
• 拆分结果落地到临时表并加索引
  如果拆分后的ID数量较多（比如超过100条），可以把拆分结果插入到全局临时表（GTT），然后给临时表的ID列创建索引：

  CREATE GLOBAL TEMPORARY TABLE tmp_ids (id VARCHAR2(20)) ON COMMIT PRESERVE ROWS;
  -- 插入拆分后的数据
  INSERT INTO tmp_ids SELECT ... -- 上面的拆分语句
  CREATE INDEX idx_tmp_ids ON tmp_ids(id);
  -- 用临时表关联主表
  SELECT * FROM main_table m JOIN tmp_ids t ON m.id = t.id;
  

  临时表的索引能大幅加快和主表的关联速度，避免全表扫描。
• 改用集合类型参数替代字符串
  让应用端直接传入集合类型（比如自定义的TABLE类型），而不是拼接字符串：

  -- 先定义自定义类型
  CREATE OR REPLACE TYPE id_list AS TABLE OF VARCHAR2(20);
  -- 存储过程参数改为该类型
  PROCEDURE proc_name(p_ids id_list) IS
  BEGIN
    SELECT * FROM main_table m JOIN TABLE(p_ids) t ON m.id = t.column_value;
  END;
  

  这样完全省去字符串拆分的开销，直接用集合关联，效率提升最明显。
• 确保主表关联列有合适的索引
  检查主表的ID列（和拆分后ID关联的列）是否有主键索引或普通索引，如果没有，创建索引能避免主表全表扫描：

  CREATE INDEX idx_main_table_id ON main_table(id);
  

• 更新统计信息
  如果表的统计信息过时，CBO可能生成差的执行计划。定期收集主表和临时表的统计信息：

  EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'MAIN_TABLE', CASCADE => TRUE);
  

• 避免硬解析
  确保存储过程中使用绑定变量，不要用字符串拼接SQL（比如EXECUTE IMMEDIATE 'SELECT ... WHERE id IN (' || p_param || ')'），绑定变量能让Oracle复用执行计划，减少硬解析开销。

3. 生成存储过程的完整执行计划 #

要获取存储过程中所有SQL的完整执行计划，常用的方法有这几种：

• 方法1：用DBMS_XPLAN.DISPLAY_CURSOR获取实际执行计划
  先执行存储过程，然后找到对应SQL的SQL_ID（可以通过V$SQL视图查询），再执行：

  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('你的SQL_ID', NULL, 'ALLSTATS LAST'));
  

  这个方法能得到实际运行时的执行计划，包含行数、时间、缓冲区等真实执行统计，是调优的首选。
• 方法2：SQL*Plus/SQL Developer中开启AUTOTRACE
  在SQL*Plus中执行：

  SET AUTOTRACE TRACEONLY EXPLAIN;
  

  然后调用存储过程，会显示存储过程中所有SQL的预估执行计划。注意如果存储过程有多个SQL语句，会依次显示每个的计划。
• 方法3：在存储过程中嵌入EXPLAIN PLAN
  针对存储过程中的目标查询，手动添加计划生成语句：

  PROCEDURE proc_name(p_param VARCHAR2) IS
  BEGIN
    -- 生成预估执行计划
    EXPLAIN PLAN FOR
      SELECT * FROM main_table m WHERE m.id IN (
        -- 你的拆分逻辑
      );
    -- 查看计划
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
    -- 实际执行查询
    ...
  END;
  

  这种方法得到的是预估计划，和实际执行可能有偏差，但适合快速查看逻辑。
• 方法4：使用SQL Developer的执行计划捕获
  打开SQL Developer，在调用存储过程前，点击“执行计划”按钮（或者按F10），然后执行存储过程，就能在“执行计划”面板看到所有执行的SQL语句及其计划。

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