子查询优化关键在于让Oracle优化器成功下推谓词：避免嵌套过深、用EXISTS替代IN、分区键逻辑外提、物化视图固化结果，并确保统计信息最新。
子查询嵌套过深导致执行计划失效
Oracle优化器对多层嵌套子查询的推导能力有限，尤其是当外层WHERE条件无法下推到内层时，会强制全表扫描。典型表现是EXPLAIN PLAN里出现大量VIEW节点且Cardinality估算严重偏离实际（比如预估10行，实际返回百万行）。
实操建议：
- 用
DBMS_XPLAN.DISPLAY检查执行计划，重点看PREDICATE列是否含filter而非access——前者说明谓词没下推成功 - 把最外层驱动表（通常是数据量最小、过滤性最强的表）提前放到子查询顶层，例如把
SELECT * FROM (SELECT ... FROM big_table) WHERE id IN (SELECT id FROM small_table)改成SELECT * FROM small_table s JOIN (SELECT ... FROM big_table) b ON s.id = b.id - 避免在子查询中使用
ROWNUM或ORDER BY，它们会阻止谓词推入；如需分页，改用OFFSET ... FETCH NEXT（Oracle 12c+）
IN/NOT IN子查询引发全表扫描
当子查询返回大量值（尤其>1000行），IN会被Oracle转为HASH JOIN或NESTED LOOPS，但若子查询本身无索引支撑，就会触发主表全扫。错误日志里常见TABLE ACCESS FULL紧挨着IN-LIST ITERATOR。
实操建议：
- 用
EXISTS替代IN：把WHERE col IN (SELECT key FROM t2)改为WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.key = t1.col)，尤其当t2有合适索引时 - 若必须用
IN且子查询结果固定，考虑物化为临时表（CREATE GLOBAL TEMPORARY TABLE），并为其创建索引 - 检查子查询字段类型是否与主表匹配——
CHAR和VARCHAR2隐式转换、数字与字符串混用都会使索引失效
视图里子查询阻断分区裁剪
在分区表上建视图时，如果子查询包含函数（如SUBSTR(date_col,1,6)）或表达式，Oracle无法识别分区键，导致整个分区段被扫描。即使执行计划显示PARTITION RANGE ALL，实际I/O也远超预期。
实操建议：
- 把分区键相关逻辑提到视图外层：
CREATE VIEW v AS SELECT * FROM partitioned_table WHERE dt >= TRUNC(SYSDATE)，而不是在子查询里写WHERE SUBSTR(dt,1,8) = '20260614' - 对分区字段建立函数索引前，确认该函数支持分区裁剪（如
TO_CHAR(dt,'YYYYMMDD')不行，但TRUNC(dt)可以） - 用
DBMS_XPLAN.DISPLAY_CURSOR查真实执行时的Partition Id列，验证是否只访问目标分区
物化视图替代动态子查询
当子查询涉及多表聚合且基础数据T+1更新时，硬编码子查询每次执行都要重算，而物化视图可固化中间结果。但直接CREATE MATERIALIZED VIEW可能因刷新锁表失败，尤其在OLTP系统。
实操建议：
- 先建普通视图验证逻辑，再用
CREATE MATERIALIZED VIEW mv_name BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND创建，避免ON COMMIT导致DML阻塞 - 刷新时加
ATOMIC_REFRESH => FALSE参数（Oracle 10g+），让物化视图先truncate再insert，避免长事务锁表 - 用
DBA_MVIEWS查LAST_REFRESH_DATE和STALENESS，确保刷新任务真正跑完，别只看调度日志
子查询优化不是改写语法那么简单，关键在于让优化器“看见”可下推的过滤条件。最容易被忽略的是统计信息过期——哪怕SQL完全正确，DBMS_STATS.GATHER_TABLE_STATS没跑过，执行计划照样错得离谱。
