都说exits快，怎么快？哪快？为啥快？
1 环境准备
环境为oracle 12c db
1.只查询需要的列，不用*
2.IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况
create table a as select * from all_objects;
insert into a select * from a;
/
/
/
commit;
create table b as select * from all_objects whererownum < 500;
SQL> select count(*) from a;
COUNT(*)
----------
2850880
SQL> select count(*) from b;
COUNT(*)
----------
499
SQL>
2 大小表在in中驱动顺序不同执行时间差异
2.1 大表A表为驱动表（加hint结果）
SQL> select /*+ use_hash(a,b) leading(a) */ count(*) from a where a.object_name in (select object_name from b);
COUNT(*)
----------
15968
1 row selected.
Elapsed: 00:00:53.22
Execution Plan
----------------------------------------------------------
Plan hash value: 1819916167
------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 91 | | 576 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 91 | | | |
|* 2 | HASH JOIN SEMI | | 2 | 182 | 3224K| 576 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| A | 89090 | 2175K| | 417 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL| B | 1 | 66 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -access("A"."OBJECT_NAME"="OBJECT_NAME")
--大表A作为驱动表，执行时间Elapsed:00:00:53.22
2.2 小表B作为驱动表（不加hint，默认计划）
SQL> select count(*) from a where a.object_name in (selectobject_name from b);
COUNT(*)
----------
15968
1 row selected.
Elapsed: 00:00:01.81
Execution Plan
----------------------------------------------------------
Plan hash value: 894329177
------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 91 | 419 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 91 | | |
|* 2 | HASH JOIN RIGHT SEMI| | 2 | 182 | 419 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL | B | 1 | 66 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | A | 89090 | 2175K| 417 (1)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -access("A"."OBJECT_NAME"="OBJECT_NAME")
--小表B作为驱动表，执行时间00:00:01.81
结论：可以看到在使用in的情况下，使用小表B作为驱动表，效果明显，执行时间为1.81s，而大表A作为驱动表，却执行53s，相差甚大
3 大小表在exists中驱动顺序不同执行时间差异
3.1 大表A驱动
SQL> select /*+ use_hash(a,b) leading(a) */count(*) from a where exists(select 1 from b where a.object_name=b.object_name);
COUNT(*)
----------
15968
1 row selected.
Elapsed: 00:00:02.47
Execution Plan
----------------------------------------------------------
Plan hash value: 1819916167
------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 39 | | 17998 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 39 | | | |
|* 2 | HASH JOIN SEMI | | 27474 | 1046K| 100M| 17998 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| A | 2850K| 67M| | 12992 (1)| 00:00:01 |
| 4