qb_name and leading

看了落落的blog  http://blog.csdn.net/robinson1988/article/details/10551467 （如何让in/exists 子查询(半连接)作为驱动表） 

我也实验了下

SELECT *
  FROM TEST A
 WHERE A.MGR IN (SELECT B.MGR FROM SCOTT.EMP B WHERE JOB = 'SALESMAN');
 
执行计划
----------------------------------------------------------
Plan hash value: 822613440

-----------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |    13 |  1287 |	5 |
|*  1 |  HASH JOIN SEMI    |	  |    13 |  1287 |	5 |
|   2 |   TABLE ACCESS FULL| TEST |    14 |  1218 |	2 |
|*  3 |   TABLE ACCESS FULL| EMP  |	3 |    36 |	2 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."MGR"="B"."MGR")
   3 - filter("B"."MGR" IS NOT NULL AND "JOB"='SALESMAN')

Note
-----
   - cpu costing is off (consider enabling it)
   - dynamic sampling used for this statement (level=2)

SQL> SELECT /*+ leading(B@bb) */ *
  FROM TEST A
 WHERE A.MGR IN (SELECT /*+ qb_name(bb) */ B.MGR
                   FROM SCOTT.EMP B
                  WHERE JOB = 'SALESMAN');
  2    3    4    5  
Execution Plan
----------------------------------------------------------
Plan hash value: 3406938101

----------------------------------------------------------------------------
| Id  | Operation	    | Name | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	 6 |   300 |	 8  (25)| 00:00:01 |
|*  1 |  HASH JOIN	    |	   |	 6 |   300 |	 8  (25)| 00:00:01 |
|   2 |   SORT UNIQUE	    |	   |	 3 |	36 |	 3   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| EMP  |	 3 |	36 |	 3   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL | TEST |	13 |   494 |	 3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."MGR"="B"."MGR")
   3 - filter("JOB"='SALESMAN' AND "B"."MGR" IS NOT NULL)
   4 - filter("A"."MGR" IS NOT NULL)

SQL> SELECT /*+ leading(B@bb) use_nl(B@bb,a) */ *
  FROM TEST A
 WHERE A.MGR IN (SELECT /*+ qb_name(bb) */ B.MGR
                   FROM SCOTT.EMP B
                  WHERE JOB = 'SALESMAN');
Execution Plan
----------------------------------------------------------
Plan hash value: 1820597472

----------------------------------------------------------------------------
| Id  | Operation	    | Name | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	 6 |   300 |	 8  (13)| 00:00:01 |
|   1 |  NESTED LOOPS	    |	   |	 6 |   300 |	 8  (13)| 00:00:01 |
|   2 |   SORT UNIQUE	    |	   |	 3 |	36 |	 3   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| EMP  |	 3 |	36 |	 3   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL | TEST |	 2 |	76 |	 2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("JOB"='SALESMAN' AND "B"."MGR" IS NOT NULL)
   4 - filter("A"."MGR" IS NOT NULL AND "A"."MGR"="B"."MGR")