对于anti-join来说leading或者ordered提示是否可以改变驱动表的...

bj-jn

1.首先以scott用户下的emp,dept表为例执行如下sql:
SQL> explain plan for
  2  select  ename from emp
  3  where not exists (select  1  from dept where dept.deptno=emp.deptno);

Explained.
SQL> select * from table(dbms_xplan.display);

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    12 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |         |     1 |    12 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   126 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     4 |    12 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   3 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

2.现在我的需求是希望让dept表为驱动表。于是执行下列sql:
SQL> explain plan for
  2      select  /*+ leading(dept) */ ename from emp
  3      where not exists (select  1  from dept where dept.deptno=emp.deptno);

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    12 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |         |     1 |    12 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   126 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     4 |    12 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   3 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")




提问:执行计划中的连接顺序依然没变。是否是我的hint添加有误，我知道leading对于normal join是生效的，不过对于特殊的anti-join不知道如何能够改变驱动表的顺序，虚心请教！

Maclean Liu(刘相兵

需要更多演示说明该问题， 问题延迟到 晚上回复

Maclean Liu(刘相兵

1. SQL>
   
2. SQL> create table emp as select * from scott.emp;
   
3. 
   
4. Table created.
   
5. 
   
6. SQL> create table dept as select * from scott.dept;
   
7. 
   
8. Table created.
   
9. 
   
10. 
    
11. 
    
12. SQL> create index pk_dept on dept(deptno);  
    
13. 
    
14. Index created.
    
15. 
    
16. 
    
17. 
    
18. select  ename
    
19.   from emp A
    
20. where not exists (select  1 from dept B where A.deptno = B.deptno);
    
21. 
    
22. 
    
23. 
    
24. Execution Plan
    
25. ----------------------------------------------------------
    
26. Plan hash value: 576271144
    
27. 
    
28. ------------------------------------------------------------------------------
    
29. | Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    
30. ------------------------------------------------------------------------------
    
31. |   0 | SELECT STATEMENT   |         |     1 |    33 |     2   (0)| 00:00:01 |
    
32. |   1 |  NESTED LOOPS ANTI |         |     1 |    33 |     2   (0)| 00:00:01 |
    
33. |   2 |   TABLE ACCESS FULL| EMP     |    14 |   280 |     2   (0)| 00:00:01 |
    
34. |*  3 |   INDEX RANGE SCAN | PK_DEPT |     4 |    52 |     0   (0)| 00:00:01 |
    
35. ------------------------------------------------------------------------------
    
36. 
    
37. Predicate Information (identified by operation id):
    
38. ---------------------------------------------------
    
39. 
    
40.    3 - access("A"."DEPTNO"="B"."DEPTNO")
    
41. 
    
42. Note
    
43. -----
    
44.    - dynamic sampling used for this statement
    
45. 
    
46. 
    
47. Statistics
    
48. ----------------------------------------------------------
    
49.           0  recursive calls
    
50.           0  db block gets
    
51.           5  consistent gets
    
52.           0  physical reads
    
53.           0  redo size
    
54.         322  bytes sent via SQL*Net to client
    
55.         481  bytes received via SQL*Net from client
    
56.           1  SQL*Net roundtrips to/from client
    
57.           0  sorts (memory)
    
58.           0  sorts (disk)
    
59.           0  rows processed
    
60. 
    
61.                   
    
62.                   
    
63.                   
    
64. SQL> oradebug setmypid
    
65. Statement processed.
    
66. SQL> oradebug event 10053 trace name context forever,level 1;
    
67. Statement processed.
    
68. SQL> explain plan for select  ename
    
69.   2    from emp A
    
70.   3   where not exists (select  1 from dept B where A.deptno = B.deptno);
    
71. 
    
72. Explained.
    
73. 
    
74. 
    
75. ******* UNPARSED QUERY IS *******
    
76. SELECT "A"."ENAME" "ENAME" FROM "SYS"."DEPT" "B","SYS"."EMP" "A" WHERE "A"."DEPTNO"="B"."DEPTNO"
    
77. kkoqbc-subheap (delete addr=0x7f99d52f16f8, in-use=23080, alloc=23736)
    
78. kkoqbc-end
    
79.           : call(in-use=22344, alloc=65552), compile(in-use=57328, alloc=106032)
    
80. apadrv-end: call(in-use=22344, alloc=65552), compile(in-use=58312, alloc=106032)
    
81. 
    
82. sql_id=frvs2rfn4z4g3.
    
83. Current SQL statement for this session:
    
84. explain plan for select  ename
    
85.   from emp A
    
86. where not exists (select  1 from dept B where A.deptno = B.deptno)
    
87. 
    
88. ============
    
89. Plan Table
    
90. ============
    
91. --------------------------------------+-----------------------------------+
    
92. | Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
    
93. --------------------------------------+-----------------------------------+
    
94. | 0   | SELECT STATEMENT    |         |       |       |     2 |           |
    
95. | 1   |  NESTED LOOPS ANTI  |         |     1 |    33 |     2 |  00:00:01 |
    
96. | 2   |   TABLE ACCESS FULL | EMP     |    14 |   280 |     2 |  00:00:01 |
    
97. | 3   |   INDEX RANGE SCAN  | PK_DEPT |     4 |    52 |     0 |           |
    
98. --------------------------------------+-----------------------------------+
    
99. 
    
100. Predicate Information:
     
101. ----------------------
     
102. 3 - access("A"."DEPTNO"="B"."DEPTNO")
     
103. 
     
104. 
     
105.                   

复制代码

对于ANTI JOIN来说 其原理要求driving table为特定表