之前写过一篇标量子查询缓存http://www.minniebaby.tech/2024/12/21/oracle-scalar-subquery-caching/
描述了标量子查询或者filter在执行时，会使用”_query_execution_cache_max_size”对外表关联列进行缓存，避免重复多余的扫描内表，具体原理参考之前的文章，这里就不描述了，本文的主要内容是验证对于subquery unnest之后的semi/anti join是否适用。
ANTI:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
SQL Monitoring Report SQL Text ------------------------------ select /*+LEADING(@"SEL$5DA710D3" "T2"@"SEL$1" "T1"@"SEL$2") USE_NL(@"SEL$5DA710D3" "T1"@"SEL$2") MONITOR*/ * from t2 where not exists (select 1 from t1 where t1.status=t2.status) Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : SYS (27:36207) SQL ID : gcdg7hmp9m967 SQL Execution ID : 16777217 Execution Started : 02/10/2026 17:02:26 First Refresh Time : 02/10/2026 17:02:26 Last Refresh Time : 02/10/2026 17:02:26 Duration : .0472s Module/Action : sqlplus@db (TNS V1-V3)/- Service : SYS$USERS Program : sqlplus@db (TNS V1-V3) Fetch Calls : 1 Global Stats =========================================================================== | Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | =========================================================================== | 0.05 | 0.05 | 0.00 | 0.00 | 1 | 1777 | 2 | 16384 | =========================================================================== SQL Plan Monitoring Details (Plan Hash Value=640702527) ============================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) | ============================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 0 | | | | | | 1 | NESTED LOOPS ANTI | | 42191 | 169K | 1 | +0 | 1 | 0 | | | | | | 2 | TABLE ACCESS FULL | T2 | 84381 | 482 | 1 | +0 | 1 | 84381 | | | | | | 3 | INDEX RANGE SCAN | IDX_11111 | 42169 | 2 | 1 | +0 | 2 | 2 | 2 | 16384 | | | ============================================================================================================================================== |
可以发现anti join也是存在cache的。关闭_query_execution_cache_max_size，发现anti join cache不起作用了，说明该参数对于anti join同样适用。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL> alter session set "_query_execution_cache_max_size"=0; Global Stats ================================================= | Elapsed | Cpu | Other | Fetch | Buffer | | Time(s) | Time(s) | Waits(s) | Calls | Gets | ================================================= | 0.12 | 0.11 | 0.00 | 1 | 1784 | ================================================= SQL Plan Monitoring Details (Plan Hash Value=640702527) =============================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | =============================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 0 | | | | 1 | NESTED LOOPS ANTI | | 42191 | 169K | 1 | +0 | 1 | 0 | | | | 2 | TABLE ACCESS FULL | T2 | 84381 | 482 | 1 | +0 | 1 | 84381 | | | | 3 | INDEX RANGE SCAN | IDX_11111 | 42169 | 2 | 1 | +0 | 84381 | 84381 | | | ============================================================================================================================== |
semi join这里就不测试了，应该也是可以的。
总结一下：
_query_execution_cache_max_size参数主要控制subquery执行中，对于重复的关联列如果之前执行过并且cache住了就无需再执行内表SEMI/ANTI匹配了，经过测试对于子查询基本都适用。
适用场景：
- 标量子查询
- FILTER
- SEMI/ANTI