----------
SQL_ID 1p9a7sq3tdb4p, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM EMP1 WHERE sex in (SELECT /*+
USE_HASH */ sex FROM FILTER)
Plan hash value: 3840124480
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | 10 |00:00:00.01 | 20 |
| 2 | TABLE ACCESS FULL| EMP1 | 1 | 10 | 10 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| FILTER | 2 | 1 | 2 |00:00:00.01 | 12 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("SEX"=:B1)
这里可以看到用的filter，性能比半连接的性能还好。
嵌套半连接：
alter session set "_always_semi_join" = 'CHOOSE';先修改回来
SELECT /*+ gather_plan_statistics */ * FROM EMP1 WHERE EXISTS
(SELECT /*+ NL_SJ */ 0 FROM FILTER
WHERE FILTER.SEX=EMP1.SEX);
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'iostats last'));
Plan hash value: 2921932404
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 1 | NESTED LOOPS SEMI | | 1 | 10 | 10 |00:00:00.01 | 20 |
| 2 | TABLE ACCESS FULL| EMP1 | 1 | 10 | 10 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| FILTER | 2 | 5 | 2 |00:00:00.01 | 12 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("FILTER"."SEX"="EMP1"."SEX")
NL_SJ需要放到IN子句,或者EXISTS和NOT EXISTS语句中，是强制走嵌套半连接的意思，这里看到1就走了嵌套半连接了。
所以starts为什么是2呢？其实是emp1驱动表中sex只有男和女，所以需要比两次。
insert into emp1 values(100,'中') ;
commit；
SELECT /*+ gather_plan_statistics */ * FROM EMP1 WHERE EXISTS
(SELECT /*+ NL_SJ */ 0 FROM FILTER
WHERE FILTER.SEX=EMP1.SEX);
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'iostats last'));
插入一个'中'的性别，再查看计划：
Plan hash value: 2921932404
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 1 | NESTED LOOPS SEMI | | 1 | 10 | 10 |00:00:00.01 | 27 |
| 2 | TABLE ACCESS FULL| EMP1 | 1 | 10 | 11 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| FILTER | 3 | 5 | 2 |00:00:00.01 | 19 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("FILTER"."SEX"="EMP1"."SEX")
大家看到这里starts是3了。对filter表操作了3次，也就是比对了3次。
如果我们再加一个条件：
SELECT /*+ gather_pla