反连接的实验

关于反连接的实验：not exists 和not in等都属于反连接
------------------------------------------------------------------------------
实验1：
SQL> Explain plan for Select  * from emp
  2  where not exists
  3  (Select * from dept where dept.deptno=emp.deptno)
  4  ;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3496123964
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    40 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |         |     1 |    40 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   518 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     5 |    15 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
 
15 rows selected


这里的反连接not exists在where后面做为子查询被解嵌套了转化成表连接了NESTED LOOPS ANTI
注意这个关键词 ANTI，反，代表着反连接被转化的。
------------------------------------------------------------------------------


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

实验2：
SQL> Explain plan for Select  * from emp
  2  where not exists
  3  (Select /*+ no_unnest*/ * from dept where dept.deptno=emp.deptno)
  4  ;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1783302997
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     9 |   333 |     3   (0)| 00:00:01 |
|*  1 |  FILTER            |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   518 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT"
              WHERE "DEPT"."DEPTNO"=:B1))
   3 - access("DEPT"."DEPTNO"=:B1)
 
17 rows selected


这里加Hints让它不不要解嵌套，这就是典型的走filter了，第二步出来的14行，每出一行
都要去3步里面过滤一遍，这里类似于嵌套循环的方式，效率肯定不高

------------------------------------------------------------------------------
------------------------------------------------------------------------------
实验3：

SQL> Explain plan for Select  * from emp
  2  where not exists
  3  (select /*+ HASH_AJ*/ * from dept where dept.deptno=emp.deptno)
  4  ;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1958379418
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    40 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |         |     1 |    40 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   518 |     3   (0)| 00:00:01 |
|   3 |   INDEX FULL SCAN  | PK_DEPT |     5 |    15 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
 
15 rows selected
 

这里用了hints /*+ HASH_AJ*/，在内部将反连接做成了hash连接的方式，通过执行计划
HASH JOIN ANTI就可以看出，这种情况用做于子查询返回的结果集教多的情况下适用，当
然也不是绝对的返回少就得走嵌套，多就走HASH
------------------------------------------------------------------------------
------------------------------------------------------------------------------
实验4：
反连接用hash_aj,半连接用hash_sj
SQL> explain plan for  Update  t_To_Insurance
  2     Set Insurance_Type_Id = 2, Insurance_Buy_Type = 1
  3   Where Guest_Id In (Select /*+ HASH_SJ */  * From lee_temp_ccc)
  4  ;
 
Explained
 
SQL> set col 1000;
SQL> set linesize 1000;
SQL> set pagesize 1000;
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------ 
Plan hash value: 1781353589
------------------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |                |  3220K|   150M|       | 18424   (5)| 00:03:42 |
|   1 |  UPDATE               | T_TO_INSURANCE |       |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|                |  3220K|   150M|  5016K| 18424   (5)| 00:03:42 |
|   3 |    TABLE ACCESS FULL  | LEE_TEMP_CCC   |   205K|  2606K|       |   569   (3)| 00:00:07 |
|   4 |    TABLE ACCESS FULL  | T_TO_INSURANCE |  3220K|   110M|       | 10048   (6)| 00:02:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("GUEST_ID"="LEE_TEMP_CCC"."GUEST_ID")
 
16 rows selected