oracle+semijoin,【semi join】IN/EXISTS子查询的区别及其优化方式

SQL> set autotrace traceonly exp

SQL> SELECT * FROM a

2  WHERE EXISTS(

3  SELECT 1 FROM b WHERE a.employee_id=b.employee_id);

执行计划

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

Plan hash value: 2317816356

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

| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT       |        |   217K|    15M|  1375   (2)| 00:00:17 |

|*  1 |  HASH JOIN             |        |   217K|    15M|  1375   (2)| 00:00:17 |

|   2 |   SORT UNIQUE          |        |   217K|   848K|   126   (1)| 00:00:02 |

|   3 |    INDEX FAST FULL SCAN| IDX2_B |   217K|   848K|   126   (1)| 00:00:02 |

|   4 |   TABLE ACCESS FULL    | A      |   217K|    14M|   620   (1)| 00:00:08 |

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

Predicate Information (identified by operation id):

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

1 - access("A"."EMPLOYEE_ID"="B"."EMPLOYEE_ID")

SQL> SELECT * FROM a

2  WHERE a.employee_id IN (SELECT b.employee_id FROM b);

执行计划

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

Plan hash value: 2317816356

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

| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT       |        |   217K|    15M|  1375   (2)| 00:00:17 |

|*  1 |  HASH JOIN             |        |   217K|    15M|  1375   (2)| 00:00:17 |

|   2 |   SORT UNIQUE          |        |   217K|   848K|   126   (1)| 00:00:02 |

|   3 |    INDEX FAST FULL SCAN| IDX2_B |   217K|   848K|   126   (1)| 00:00:02 |

|   4 |   TABLE ACCESS FULL    | A      |   217K|    14M|   620   (1)| 00:00:08 |

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

Predicate Information (identified by operation id):

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

1 - access("A"."EMPLOYEE_ID"="B"."EMPLOYEE_ID")