Oracle执行计划：多用户查询为何比单用户查询成本更低？

一、这不是成本显示问题，In List Iterator确实更高效 #

当使用IN子句包含多用户ID时，优化器触发的In List Iterator计划本质是将每个列表值作为独立单元迭代处理：

• 它会利用user_service和user_service_transaction上的(user_id, svc_id)复合索引，精准定位每个user_id对应的行，避免大范围的索引扫描；
• 对于不存在的用户ID（如-1），迭代时会直接过滤掉，不会产生无效的扫描开销；
• 更关键的是，这种迭代模式下优化器的基数估算更准确——单值查询时，如果用户ID的分布不均匀（比如部分用户关联的服务/交易极多），优化器可能错误估算行数，选择不够优的计划；而In List Iterator针对每个值单独估算，基数判断更精准，整体成本自然更低。

二、重构视图让单用户查询自动采用高效计划 #

可以通过以下几种方式调整视图结构，引导优化器选择类似In List Iterator的高效计划：

1. 简化视图逻辑，消除冗余嵌套 #

原视图的两层GROUP BY是冗余的——内层GROUP BY仅用于去重txn_name，可以直接用LISTAGG(DISTINCT ...)合并为一步，减少嵌套层级，让优化器更容易将外层的USER_ID过滤条件下推到基表：

CREATE OR REPLACE VIEW VIEW_SERVICE_TRANSACTIONS AS
SELECT 
    AUS.USER_ID, 
    AUS.SERVICE_NAME, 
    LISTAGG(DISTINCT AUST.txn_name, ',') AS TRANSACTION_NAMES
FROM user_service AUS 
INNER JOIN user_service_transaction AUST 
    ON AUS.USER_ID = AUST.USER_ID AND AUS.svc_id = AUST.svc_id  
WHERE AUS.STATUS='A' AND AUST.STATUS ='A'
GROUP BY AUS.USER_ID, AUS.SERVICE_NAME;

这种结构下，优化器能直接将WHERE USER_ID = ?的条件下推到基表的JOIN阶段，利用索引快速定位目标用户的行，效果和In List Iterator类似。

2. 添加优化器提示强制谓词下推 #

如果简化逻辑后优化器仍未自动下推条件，可以添加/*+ PUSH_PRED */提示，强制优化器将外层的过滤条件应用到基表查询阶段，而非视图聚合之后：

CREATE OR REPLACE VIEW VIEW_SERVICE_TRANSACTIONS AS
SELECT /*+ PUSH_PRED */
    AUS.USER_ID, 
    AUS.SERVICE_NAME, 
    LISTAGG(DISTINCT AUST.txn_name, ',') AS TRANSACTION_NAMES
FROM user_service AUS 
INNER JOIN user_service_transaction AUST 
    ON AUS.USER_ID = AUST.USER_ID AND AUS.svc_id = AUST.svc_id  
WHERE AUS.STATUS='A' AND AUST.STATUS ='A'
GROUP BY AUS.USER_ID, AUS.SERVICE_NAME;

3. 更新表统计信息 #

优化器的计划选择严重依赖统计信息，如果user_id列的统计信息过时或缺失直方图，会导致基数估算偏差。执行以下命令更新统计信息：

-- 替换SCHEMA_NAME为实际的 schema 名称
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'USER_SERVICE', METHOD_OPT=>'FOR COLUMNS USER_ID SIZE AUTO');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'USER_SERVICE_TRANSACTION', METHOD_OPT=>'FOR COLUMNS USER_ID SIZE AUTO');

更新后，优化器能更准确地判断单用户查询的基数，自动选择高效的索引定位计划。

额外小技巧（无需修改视图） #

如果允许调整查询语句，将WHERE USER_ID = 189791改写为WHERE USER_ID IN (189791)，也能直接触发In List Iterator计划，这是最简单的临时解决方案。

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