传统的SQL优化思想认为IN、Exists这2种写法不同数据量的环境中各擅胜场&＃xff0c;Developer应当根据实际情况合理运用IN或Exists。 实际我认为&＃xff0c;这种认识对于现代SQL优化器Optimizer(10g以后)不再适用。 CBO优化器中本身包含了查询转换(Query Transformation)的功能。 为什么CBO要做查询转换(Query Transformation)&＃xff1f; 回答&＃xff1a; 用户提交给SQL引擎的语句可能不是执行查询最有效的语句 查询转换(Query Transformation)做了什么&＃xff1f; 回答&＃xff1a; 查询转换按照固定的语法将语句重写为等意的SQL&＃xff0c;但是优化了其SQL结构 查询转换(Query Transformation)包括启发式地查询转换和基于成本的查询转换。 对于IN、Exists , CBO optimizer 的查询装换效果如何&＃xff1f;&＃xff1a;

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 - ProductionSQL> select count(1) from joinA A where A.CUST_ID in ( select B.cust_id from joinb B where B.CUST_FIRST_NAME like &＃39;C%&＃39;);COUNT(1)
----------39381SQL> alter system flush shared_pool;System altered.SQL> alter system flush shared_pool;System altered.SQL> alter session set events &＃39;10053 trace name context forever,level 1&＃39;;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 &＃39;C%&＃39;);COUNT(1)
----------39381SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
D:\APP\ML\diag\rdbms\testem\testem\trace\testem_ora_6776.trcFinal 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 &＃39;C%&＃39;) "VW_NSO_1","SYS"."JOINA" "A"WHERE "A"."CUST_ID" &＃61; "VW_NSO_1"."CUST_ID"kkoqbc: optimizing query block SEL$683B0107 (#2)SQL> alter session set events &＃39;10053 trace name context forever,level 1&＃39;;Session altered.SQL> select count(1) from joinA A where exists ( select 1 from joinb B where B.CUST_FIRST_NAME like &＃39;C%&＃39; and B.cust_id&＃61;A.cust_id );COUNT(1)
----------39381Final 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 &＃39;C%&＃39;) "VW_SQ_1","SYS"."JOINA" "A"WHERE "VW_SQ_1"."ITEM_1" &＃61; "A"."CUST_ID"       如以上10053 trace 所显示 对于2个分别使用了IN和Exists的查询&＃xff0c; CBO Optimizer查询转换后得到的结果十分类似&＃xff1a; IN: SELECT COUNT(*) "COUNT(1)" FROM (SELECT "B"."CUST_ID" "CUST_ID" FROM "SYS"."JOINB" "B" WHERE "B"."CUST_FIRST_NAME" LIKE &＃39;C%&＃39;) "VW_NSO_1", "SYS"."JOINA" "A" WHERE "A"."CUST_ID" &＃61; "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 &＃39;C%&＃39;) "VW_SQ_1", "SYS"."JOINA" "A" WHERE "VW_SQ_1"."ITEM_1" &＃61; "A"."CUST_ID" CBO在完成Query transformations之后才会对转换后的语句进行APA(access path analysis)和后续的优化树构建、成本比较等等&＃xff0c;一般来说如果你使用10g以后的CBO &＃xff0c;可以认为语义等价的IN或Exists在性能上也不会有太大的区别。