优化查询（单次调用子查询）：子查询是否逐行执行及优化方案咨询

子查询是否会针对每一行执行？ #

是的，你原查询的高成本大概率是因为针对MY_TABLE的每一行都重复执行了select * from b_ids子查询。

虽然你用了CTE（WITH b_ids as (...)），但如果数据库优化器没选择将CTE物化（提前计算结果并缓存），再加上TO_NUMBER(R_ID)的转换操作干扰了优化判断，就会把CTE当成相关子查询处理，导致每行都触发一次子查询执行。而第二个查询用常量列表时，数据库可以直接把常量集合构建成哈希表快速匹配，所以成本骤降。

优化方法 #

以下是几种实用的优化思路，按优先级排序：

1. 用JOIN替代IN子查询，避免重复计算 #

把CTE结果和内层查询做JOIN，提前标记符合条件的行，确保b_ids只计算一次：

WITH b_ids as (select b_id from v where v_id = (select max(v_id) from v2) AND t = 23)
SELECT
    CASE WHEN b.b_id IS NOT NULL THEN 'E' ELSE s.S END AS S,
    CASE WHEN b.b_id IS NOT NULL THEN '1000' ELSE s.S_ID END AS S_ID
FROM (
    SELECT 
        S, S_ID, DECODE(Z_ID, 1, 6021, 2, 159, B_ID) AS R_ID
    FROM MY_TABLE
) s
LEFT JOIN b_ids b ON TO_NUMBER(s.R_ID) = b.b_id;

2. 强制物化CTE（部分数据库支持） #

像Oracle这类数据库，可以通过MATERIALIZE关键字强制优化器把CTE结果物化到临时存储，避免重复计算：

WITH b_ids as (select b_id from v where v_id = (select max(v_id) from v2) AND t = 23) MATERIALIZE
SELECT
    CASE WHEN TO_NUMBER(R_ID) IN (select * from b_ids) THEN 'E' ELSE S END AS S,
    CASE WHEN TO_NUMBER(R_ID) IN (select * from b_ids) THEN '1000' ELSE S_ID END AS S_ID
FROM (
    SELECT 
        S, S_ID, DECODE(Z_ID, 1, 6021, 2, 159, B_ID) AS R_ID
    FROM MY_TABLE
);

3. 优化索引提升子查询效率 #

• 给v表创建(v_id, t, b_id)组合索引，让b_ids查询直接通过索引返回结果，无需全表扫描。
• 给v2表创建v_id索引，加速max(v_id)的计算。
• 如果R_ID是字符串类型，考虑创建函数索引：CREATE INDEX idx_my_table_r_id_num ON MY_TABLE(TO_NUMBER(DECODE(Z_ID, 1, 6021, 2, 159, B_ID)))，避免转换操作导致索引失效。

4. 用EXISTS替代IN子查询 #

部分场景下EXISTS的执行计划比IN更高效，尤其是当b_ids结果集较大时：

WITH b_ids as (select b_id from v where v_id = (select max(v_id) from v2) AND t = 23)
SELECT
    CASE WHEN EXISTS (SELECT 1 FROM b_ids b WHERE TO_NUMBER(s.R_ID) = b.b_id) THEN 'E' ELSE s.S END AS S,
    CASE WHEN EXISTS (SELECT 1 FROM b_ids b WHERE TO_NUMBER(s.R_ID) = b.b_id) THEN '1000' ELSE s.S_ID END AS S_ID
FROM (
    SELECT 
        S, S_ID, DECODE(Z_ID, 1, 6021, 2, 159, B_ID) AS R_ID
    FROM MY_TABLE
) s;

5. 手动将b_ids结果存入临时表 #

如果以上方法效果不佳，可以手动把b_ids结果写入临时表，再做JOIN：

CREATE TEMP TABLE temp_b_ids AS 
select b_id from v where v_id = (select max(v_id) from v2) AND t = 23;

SELECT
    CASE WHEN b.b_id IS NOT NULL THEN 'E' ELSE s.S END AS S,
    CASE WHEN b.b_id IS NOT NULL THEN '1000' ELSE s.S_ID END AS S_ID
FROM (
    SELECT 
        S, S_ID, DECODE(Z_ID, 1, 6021, 2, 159, B_ID) AS R_ID
    FROM MY_TABLE
) s
LEFT JOIN temp_b_ids b ON TO_NUMBER(s.R_ID) = b.b_id;

DROP TABLE temp_b_ids;

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