三 EXISTS子查询的支持
SELECT * FROM t1 WHERE t1.a1 =(SELECT floor(avg(t2.a2)) FROM t2 WHERE t2.a2<10);
Oracle的执行计划:HASH JOIN SEMI表明嵌套层次消除,子查询被上拉,然后执行了半连接操作。
Plan hash value: 2230766683
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3021 | 66462 | 8 (13)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 3021 | 66462 | 8 (13)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T1 | 3030 | 27270 | 4 (0)| 00:00:01 |
| 3 | VIEW | VW_SQ_1 | 3027 | 39351 | 3 (0)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN| SYS_C0010114 | 3027 | 9081 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C772B8D1
2 - SEL$C772B8D1 / T1@SEL$1
3 - SEL$683B0107 / VW_SQ_1@SEL$7511BFD2
4 - SEL$683B0107 / T2@SEL$2
Outline Da
-------------
/*+
BEGIN_OUTLINE_DA
INDEX_FFS(@"SEL$683B0107" "T2"@"SEL$2" ("T2"."A2"))
USE_HASH(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2")
LEADING(@"SEL$C772B8D1" "T1"@"SEL$1" "VW_SQ_1"@"SEL$7511BFD2")
NO_ACCESS(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2")
FULL(@"SEL$C772B8D1" "T1"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$7511BFD2")
OUTLINE(@"SEL$2")
UNNEST(@"SEL$2")
OUTLINE_LEAF(@"SEL$C772B8D1")
OUTLINE_LEAF(@"SEL$683B0107")
ALL_ROWS
DB_VERSION('11.1.0.6')
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."A1"="ITEM_1")
4 - filter("T2"."A2">10)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "T1"."A1"[NUMBER,22], "T1"."ID1"[NUMBER,22],
"T1"."B1"[NUMBER,22]
2 - "T1"."ID1"[NUMBER,22], "T1"."A1"[NUMBER,22], "T1"."B1"[NUMBER,22]
3 - "ITEM_1"[NUMBER,22]
4 - "T2"."A2"[NUMBER,22]
四 NOT EXISTS子查询
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.a1= t2.a2 AND t2.a2>10);
Oracle的执行计划:NESTED LOOPS ANTI表明嵌套层次消除,子查询被上拉,然后执行了反半连接操作。
Plan hash value: 674198265
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3021 | 36252 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 3021 | 36252 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3030 | 27270 | 4 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| SYS_C0010114 | 9 | 27 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$1
3 - SEL$5DA710D3 / T2@SEL$2
Outline Da
-------------
/*+
BEGIN_OUTLINE_DA
USE_NL(@"SEL$5DA710D3" "T2"@"SEL$2")
LEADING(@"SEL$5DA710D3" "T1"@"SEL$1" "T2"@"SEL$2")
INDEX(@"SEL$5DA710D3" "T2"@"SEL$2" ("T2"."A2"))
FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
UNNEST(@"SEL$2")
OUTLINE_LEAF(@"SEL$5DA710D3")
ALL_ROWS
DB_VERSION('11.1.0.6')
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."A1"="T2"."A2")
filter("T2"."A2">10)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "T1"."ID1"[NUMBER,22], "T1"."A1"[NUMBER,22],
"T1"."B1"[NUMBER,22]
2 - "T1"."ID1"[NUMBER,22], "T1"."A1"[NUMBER,22], "T1"."B1"[NUMBER,22]