exists/in 在oracle中的执行效率-浅谈
oracle version : 10.2.0.1
SQL>
SQL> select * from cui2
2 where exists (select 1 from t where t.id=cui2.id and t.pad is not null)
3 ;
已选择1100行。
执行计划
----------------------------------------------------------
Plan hash value: 2469730472
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4054 | 5 (20)| 00:00:01 | | |
| 1 | NESTED LOOPS SEMI | | 2 | 4054 | 5 (20)| 00:00:01 | | |
| 2 | TABLE ACCESS FULL | CUI2 | 2002 | 24024 | 4 (0)| 00:00:01 | | |
|* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 1 | 2015 | 0 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX UNIQUE SCAN | T_PK | 1 | | 0 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."PAD" IS NOT NULL)
4 - access("T"."ID"="CUI2"."ID")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
204 consistent gets
0 physical reads
0 redo size
16030 bytes sent via SQL*Net to client
1188 bytes received via SQL*Net from client
75 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1100 rows processed
SQL> select * from cui2
2 where exists (select rownum from t where t.id=cui2.id and t.pad is not null) ----> 存在 rownum伪列
3 ;
已选择1100行。
执行计划
----------------------------------------------------------
Plan hash value: 1821720693
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 4 (0)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
| 2 | TABLE ACCESS FULL | CUI2 | 2002 | 24024 | 4 (0)| 00:00:01 | | |
| 3 | COUNT | | | | | | | |
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 1 | 2015 | 0 (0)| 00:00:01 | ROWID | ROWID |
|* 5 | INDEX UNIQUE SCAN | T_PK | 1 | | 0 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T" "T" WHERE "T"."ID"=:B1 AND "T"."PAD" IS NOT NULL))
4 - filter("T"."PAD" IS NOT NULL)
5 - access("T"."ID"=:B1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1191 consistent gets
0 physical reads
0 redo size
16030 bytes sent via SQL*Net to client
1188 bytes received via SQL*Net from client
75 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1100 rows processed
SQL> select * from cui2
2 where exists (select max(1) from t where t.id=cui2.id and t.pad is not null group by t.id) ---->存在聚合函数
3 ;
已选择1100行。
执行计划
----------------------------------------------------------
Plan hash value: 1994869774
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 4 (0)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
| 2 | TABLE ACCESS FULL | CUI2 | 2002 | 24024 | 4 (0)| 00:00:01 | | |
| 3 | SORT GROUP BY NOSORT | | 1 | 2015 | 0 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 1 | 2015 | 0 (0)| 00:00:01 | ROWID | ROWID |
|* 5 | INDEX UNIQUE SCAN | T_PK | 1 | | 0 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T" "T" WHERE "T"."ID"=:B1 AND "T"."PAD" IS NOT NULL
GROUP BY "T"."ID"))
4 - filter("T"."PAD" IS NOT NULL)
5 - access("T"."ID"=:B1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1191 consistent gets
0 physical reads
0 redo size
16030 bytes sent via SQL*Net to client
1188 bytes received via SQL*Net from client
75 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1100 rows processed
SQL> select * from cui2
2 where exists (select max(id) from t where t.id=cui2.id and t.pad is not null)
3 ;
已选择2002行。
执行计划
----------------------------------------------------------
Plan hash value: 1867261139
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2002 | 24024 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| CUI2 | 2002 | 24024 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
150 consistent gets
0 physical reads
0 redo size
35378 bytes sent via SQL*Net to client
1848 bytes received via SQL*Net from client
135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2002 rows processed
SQL> select * from cui2
2 where exists (select max(id) from t where t.id=cui2.id and t.pad is not null group by t.id) ---->存在聚合函数
3 ;
已选择1100行。
执行计划
----------------------------------------------------------
Plan hash value: 1994869774
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 4 (0)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
| 2 | TABLE ACCESS FULL | CUI2 | 2002 | 24024 | 4 (0)| 00:00:01 | | |
| 3 | SORT GROUP BY NOSORT | | 1 | 2015 | 0 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 1 | 2015 | 0 (0)| 00:00:01 | ROWID | ROWID |
|* 5 | INDEX UNIQUE SCAN | T_PK | 1 | | 0 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T" "T" WHERE "T"."ID"=:B1 AND "T"."PAD" IS NOT NULL
GROUP BY "T"."ID"))
4 - filter("T"."PAD" IS NOT NULL)
5 - access("T"."ID"=:B1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1191 consistent gets
0 physical reads
0 redo size
16030 bytes sent via SQL*Net to client
1188 bytes received via SQL*Net from client
75 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1100 rows processed
SQL> select * from cui2
2 where exists (select id from t where t.id=cui2.id and t.pad is not null)
3 ;
已选择1100行。
执行计划
----------------------------------------------------------
Plan hash value: 2469730472
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4054 | 5 (20)| 00:00:01 | | |
| 1 | NESTED LOOPS SEMI | | 2 | 4054 | 5 (20)| 00:00:01 | | |
| 2 | TABLE ACCESS FULL | CUI2 | 2002 | 24024 | 4 (0)| 00:00:01 | | |
|* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 1 | 2015 | 0 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX UNIQUE SCAN | T_PK | 1 | | 0 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."PAD" IS NOT NULL)
4 - access("T"."ID"="CUI2"."ID")
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
206 consistent gets
0 physical reads
0 redo size
16030 bytes sent via SQL*Net to client
1188 bytes received via SQL*Net from client
75 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1100 rows processed
SQL> select * from cui2
2 where exists (select id from t where t.id=cui2.id and t.pad is not null minus select 11 from dual) --存在集合操作
3 ;
已选择1099行。
执行计划
----------------------------------------------------------
Plan hash value: 4118439068
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 4010 (50)| 00:00:49 | | |
|* 1 | FILTER | | | | | | | |
| 2 | TABLE ACCESS FULL | CUI2 | 2002 | 24024 | 4 (0)| 00:00:01 | | |
| 3 | MINUS | | | | | | | |
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 1 | 2015 | 0 (0)| 00:00:01 | ROWID | ROWID |
|* 5 | INDEX UNIQUE SCAN | T_PK | 1 | | 0 (0)| 00:00:01 | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS ( (SELECT /*+ */ "ID" FROM "T" "T" WHERE "T"."ID"=:B1 AND "T"."PAD" IS NOT
NULL)MINUS (SELECT /*+ */ 11 FROM "SYS"."DUAL" "DUAL")))
4 - filter("T"."PAD" IS NOT NULL)
5 - access("T"."ID"=:B1)
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
1192 consistent gets
0 physical reads
0 redo size
16019 bytes sent via SQL*Net to client
1188 bytes received via SQL*Net from client
75 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1099 rows processed
SQL> select * from cui2
2 where id in (select id from t where t.pad is not null minus select 11 from dual) --->子查询中不存在连接条件，单纯的返回结果，供主查询框定查询范围
3 ;
已选择1099行。
执行计划
----------------------------------------------------------
Plan hash value: 2655041843
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 501 | 12525 | 10 (20)| 00:00:01 | | |
| 1 | TABLE ACCESS BY INDEX ROWID| CUI2 | 501 | 6012 | 4 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 501 | 12525 | 10 (20)| 00:00:01 | | |
| 3 | VIEW | VW_NSO_1 | 1 | 13 | 6 (34)| 00:00:01 | | |
| 4 | MINUS | | | | | | | |
| 5 | SORT UNIQUE | | 1 | 2015 | | | | |
| 6 | PARTITION RANGE ALL | | 1 | 2015 | 2 (0)| 00:00:01 | 1 | 4 |
|* 7 | TABLE ACCESS FULL | T | 1 | 2015 | 2 (0)| 00:00:01 | 1 | 4 |
| 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | |
|* 9 | INDEX RANGE SCAN | IND_CUI2_ID | 501 | | 2 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("T"."PAD" IS NOT NULL)
9 - access("ID"="$nso_col_1")
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
2076 consistent gets
0 physical reads
0 redo size
15995 bytes sent via SQL*Net to client
1188 bytes received via SQL*Net from client
75 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1099 rows processed
SQL> select * from cui2
2 where id in (select id from t where t.pad is not null )
3 ;
已选择1100行。
执行计划
----------------------------------------------------------
Plan hash value: 3915725801
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2027 | 4 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY INDEX ROWID| CUI2 | 2 | 24 | 2 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 2027 | 4 (0)| 00:00:01 | | |
| 3 | PARTITION RANGE ALL | | 1 | 2015 | 2 (0)| 00:00:01 | 1 | 4 |
|* 4 | TABLE ACCESS FULL | T | 1 | 2015 | 2 (0)| 00:00:01 | 1 | 4 |
|* 5 | INDEX RANGE SCAN | IND_CUI2_ID | 2 | | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T"."PAD" IS NOT NULL)
5 - access("ID"="ID")
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
2063 consistent gets
0 physical reads
0 redo size
16006 bytes sent via SQL*Net to client
1188 bytes received via SQL*Net from client
75 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1100 rows processed
SQL> select * from cui2
2 where id in (select id from t where t.pad is not null group by id)
3 ;
已选择1100行。
执行计划
----------------------------------------------------------
Plan hash value: 4061204666
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 50 | 3 (34)| 00:00:01 | | |
| 1 | TABLE ACCESS BY INDEX ROWID | CUI2 | 2 | 24 | 2 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 2 | 50 | 3 (34)| 00:00:01 | | |
| 3 | VIEW | VW_NSO_1 | 1 | 13 | 0 (0)| 00:00:01 | | |
| 4 | HASH GROUP BY | | 1 | 2015 | 0 (0)| 00:00:01 | | |
|* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 1 | 2015 | 0 (0)| 00:00:01 | ROWID | ROWID |
| 6 | INDEX FULL SCAN | T_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 7 | INDEX RANGE SCAN | IND_CUI2_ID | 2 | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("T"."PAD" IS NOT NULL)
7 - access("ID"="$nso_col_1")
统计信息
----------------------------------------------------------
31 recursive calls
0 db block gets
2070 consistent gets
0 physical reads
0 redo size
16006 bytes sent via SQL*Net to client
1188 bytes received via SQL*Net from client
75 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1100 rows processed
SQL> select * from cui2
2 where id in (select id from t where cui2.id=t.id and t.pad is not null group by id) -->存在连接条件，子查询有聚合
3 ;
已选择1100行。
执行计划
----------------------------------------------------------
Plan hash value: 4211419426
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 4 (0)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
| 2 | TABLE ACCESS FULL | CUI2 | 2002 | 24024 | 4 (0)| 00:00:01 | | |
|* 3 | FILTER | | | | | | | |
| 4 | SORT GROUP BY NOSORT | | 1 | 2015 | 0 (0)| 00:00:01 | | |
|* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 1 | 2015 | 0 (0)| 00:00:01 | ROWID | ROWID |
|* 6 | INDEX UNIQUE SCAN | T_PK | 1 | | 0 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T" "T" WHERE "T"."ID"=:B1 AND "T"."PAD" IS NOT NULL
GROUP BY "ID" HAVING "ID"=:B2))
3 - filter("ID"=:B1)
5 - filter("T"."PAD" IS NOT NULL)
6 - access("T"."ID"=:B1)
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
1193 consistent gets
0 physical reads
0 redo size
16030 bytes sent via SQL*Net to client
1188 bytes received via SQL*Net from client
75 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1100 rows processed
SQL> select * from cui2
2 where id in (select id from t where cui2.id=t.id and t.pad is not null minus select 11 from dual)
3 ;
已选择1099行。
执行计划
----------------------------------------------------------
Plan hash value: 3494826756
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 4010 (50)| 00:00:49 | | |
|* 1 | FILTER | | | | | | | |
| 2 | TABLE ACCESS FULL | CUI2 | 2002 | 24024 | 4 (0)| 00:00:01 | | |
| 3 | MINUS | | | | | | | |
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 1 | 2015 | 0 (0)| 00:00:01 | ROWID | ROWID |
|* 5 | INDEX UNIQUE SCAN | T_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 6 | FILTER | | | | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS ( (SELECT /*+ */ "ID" FROM "T" "T" WHERE "ID"=:B1 AND "T"."PAD" IS NOT
NULL)MINUS (SELECT /*+ */ 11 FROM "SYS"."DUAL" "DUAL" WHERE :B2=11)))
4 - filter("T"."PAD" IS NOT NULL)
5 - access("ID"=:B1)
6 - filter(:B1=11)
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
1192 consistent gets
0 physical reads
0 redo size
16019 bytes sent via SQL*Net to client
1188 bytes received via SQL*Net from client
75 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1099 rows processed
SQL> select * from cui2
2 .
SQL>
SQL> l
1* select * from cui2
SQL> select * from cui2
2 where exists (select /*+no_unnest */ 1 from t where cui2.id=t.id and t.pad is not null) -->加此提示，为消除 子查询非嵌套，默认情况下，是选择 子查询非嵌套的
3 ;
已选择1100行。
执行计划
----------------------------------------------------------
Plan hash value: 3759741530
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 4 (0)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
| 2 | TABLE ACCESS FULL | CUI2 | 2002 | 24024 | 4 (0)| 00:00:01 | | |
|* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 1 | 2015 | 0 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX UNIQUE SCAN | T_PK | 1 | | 0 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T" "T" WHERE "T"."ID"=:B1 AND "T"."PAD" IS
NOT NULL))
3 - filter("T"."PAD" IS NOT NULL)
4 - access("T"."ID"=:B1)
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
1193 consistent gets
0 physical reads
0 redo size
16030 bytes sent via SQL*Net to client
1188 bytes received via SQL*Net from client
75 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1100 rows processed
SQL> spool off
总结：由于存在 exists/in 这种子查询时，且与主查询有连接条件的情况下，默认oracle会选择子查询非嵌套，即选择 hash_join/nest_loop等类似于两表直接关联的形式，已提高查询效率，如果不能执行搞笑的执行效率，就只能选择 filter这种 相关联型的查询，即主查询检索一行，嵌套查询遍历一遍。但oracle能将这种嵌套查询非嵌套化是有条件的，即子查询中不能存在 集合操作、聚合操作、rownum伪列，如上示例。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10037372/viewspace-682991/ ，如需转载，请注明出处，否则将追究法律责任。
