(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 | 5 | 4 |00:00:00.01 | 14 |

|* 2 | TABLE ACCESS FULL| EMP1 | 1 | 5 | 4 |00:00:00.01 | 8 |

|* 3 | TABLE ACCESS FULL| FILTER | 1 | 3 | 1 |00:00:00.01 | 6 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("SEX"='女')

3 - filter(("FILTER"."SEX"='女' AND "FILTER"."SEX"="EMP1"."SEX"))

那么这里有了女的过滤，那么filter表只操作一次就可以了，所以starts是1.

insert into filter values('中') ;

commit;

这里我给filter表里也插入一个，再次执行一次：

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 | 11 |00:00:00.01 | 26 |

| 2 | TABLE ACCESS FULL| EMP1 | 1 | 10 | 11 |00:00:00.01 | 8 |

|* 3 | TABLE ACCESS FULL| FILTER | 3 | 5 | 3 |00:00:00.01 | 18 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter("FILTER"."SEX"="EMP1"."SEX")

看到3的A-Rows变化了，是3，所以A-Rows就是实际返回的行数。

嵌套全连接：

delete emp1 where sex='中';

commit；

SELECT /*+ gather_plan_statistics USE_NL(EMP1,FILTER) */ EMP1.* FROM EMP1,FILTER

WHERE FILTER.SEX=EMP1.SEX;

SELECT * FROM table(dbms_xplan.display_cursor(null,null,'iostats last'));

Plan hash value: 3269263915

---------------------------------------------------------------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

---------------------------------------------------------------------------------------

| 1 | NESTED LOOPS | | 1 | 25 | 26 |00:00:00.01 | 53 |

| 2 | TABLE ACCESS FULL| FILTER | 1 | 5 | 6 |00:00:00.01 | 9 |

|* 3 | TABLE ACCESS FULL| EMP1 | 6 | 5 | 26 |00:00:00.01 | 44 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter("FILTER"."SEX"="EMP1"."SEX")

看到3的starts是6了吧，因为是嵌套全连接，不是半连接，所以2的所以行都的比对一次。这里是根据行数取最少，成本最低的作为驱动表，所以看到filter是驱动表，emp1被驱动。这里3所以就操作了6次，返回26行。