SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> select count(1) from joinA A where A.CUST_ID in ( select B.cust_id from joinb B where B.CUST_FIRST_NAME like 'C%');
COUNT(1)
----------
39381
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set events '10053 trace name context forever,level 1';
Session altered.
SQL> select count(1) from joinA A where A.CUST_ID in ( select B.cust_id from joinb B where B.CUST_FIRST_NAME like 'C%');
COUNT(1)
----------
39381
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
D:\APP\ML\diag\rdbms\testem\testem\trace\testem_ora_6776.trc
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(1)"
FROM (SELECT "B"."CUST_ID" "CUST_ID"
FROM "SYS"."JOINB" "B"
WHERE "B"."CUST_FIRST_NAME" LIKE 'C%') "VW_NSO_1",
"SYS"."JOINA" "A"
WHERE "A"."CUST_ID" = "VW_NSO_1"."CUST_ID"
kkoqbc: optimizing query block SEL$683B0107 (#2)
SQL> alter session set events '10053 trace name context forever,level 1';
Session altered.
SQL> select count(1) from joinA A where exists ( select 1 from joinb B where B.CUST_FIRST_NAME like 'C%' and B.cust_id=A.cust_id );
COUNT(1)
----------
39381
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(1)"
FROM (SELECT "B"."CUST_ID" "ITEM_1"
FROM "SYS"."JOINB" "B"
WHERE "B"."CUST_FIRST_NAME" LIKE 'C%') "VW_SQ_1",
"SYS"."JOINA" "A"
WHERE "VW_SQ_1"."ITEM_1" = "A"."CUST_ID"
如以上10053 trace 所显示 对于2个分别使用了IN和Exists的查询， CBO Optimizer查询转换后得到的结果十分类似：
IN:
SELECT COUNT(*) "COUNT(1)"
FROM (SELECT "B"."CUST_ID" "CUST_ID"
FROM "SYS"."JOINB" "B"
WHERE "B"."CUST_FIRST_NAME" LIKE 'C%') "VW_NSO_1",
"SYS"."JOINA" "A"
WHERE "A"."CUST_ID" = "VW_NSO_1"."CUST_ID"
Exists:
SELECT COUNT(*) "COUNT(1)"
FROM (SELECT "B"."CUST_ID" "ITEM_1"
FROM "SYS"."JOINB" "B"
WHERE "B"."CUST_FIRST_NAME" LIKE 'C%') "VW_SQ_1",
"SYS"."JOINA" "A"
WHERE "VW_SQ_1"."ITEM_1" = "A"."CUST_ID"
CBO在完成Query transformations之后才会对转换后的语句进行APA(access path analysis)和后续的优化树构建、成本比较等等，一般来说如果你使用10g以后的CBO ，可以认为语义等价的IN或Exists在性能上也不会有太大的区别。
Oracle SQL Optimizer IN VS Exists Again
传统的SQL优化思想认为IN、Exists这2种写法不同数据量的环境中各擅胜场，Developer应当根据实际情况合理运用IN或Exists。
实际我认为，这种认识对于现代SQL优化器Optimizer(10g以后)不再适用。 CBO优化器中本身包含了查询转换(Query Transformation)的功能。
为什么CBO要做查询转换(Query Transformation)？
回答： 用户提交给SQL引擎的语句可能不是执行查询最有效的语句
查询转换(Query Transformation)做了什么？
回答： 查询转换按照固定的语法将语句重写为等意的SQL，但是优化了其SQL结构
查询转换(Query Transformation)包括启发式地查询转换和基于成本的查询转换。
对于IN、Exists , CBO optimizer 的查询装换效果如何？：