反连接的实验
来源：互联网 发布：如何降低wifi网络延迟 编辑：程序博客网 时间：2024/05/05 15:15
关于反连接的实验：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_sjSQL> 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
