问题的引出：
开发人员转过来的问题，对同一个表的两次查询，响应速度大不相同(查询20日数据需要40秒左右，而查询26日数据居然需要0.016秒)，看了一下，果然奇怪，两次中查询条件只有查询时间不同,查询日期为20的情况下,子查询返回的查询结果为非空,查询日期为26的情况下,子查询返回的查询结果为空,解决问题的过程比较简单，从原理上来分析的话，却很值得研究。

[@more@]下面就是这两个SQL语句:
（用20表示第一次查询，26表示第二次查询，下同）
20.
select SEARCHENGINE,USERNAME,CLIENTNAME,KEYWORD,STATDATE,sysdate
from SEARCHENGINE_DATA t
where t.STATDATE=to_date('2008-3-20','yyyy-mm-dd')
and exists (select 1 from SEARCHENGINE_DATA where STATDATE=to_date('2008-3-20','yyyy-mm-dd')
and keyword = t.keyword
and KEYWORD not like '%??%' group by STATDATE,SEARCHENGINE,CLIENTNAME,KEYWORD having count(*) >1) ;
26.
select SEARCHENGINE,USERNAME,CLIENTNAME,KEYWORD,STATDATE,sysdate
from SEARCHENGINE_DATA t
where t.STATDATE=to_date('2008-3-26','yyyy-mm-dd')
and exists (select 1 from SEARCHENGINE_DATA where STATDATE=to_date('2008-3-26','yyyy-mm-dd')
and keyword = t.keyword
and KEYWORD not like '%??%' group by STATDATE,SEARCHENGINE,CLIENTNAME,KEYWORD having count(*) >1) ;
检查执行计划(在PL/SQL Developer上得到，三列分别为cost，cardinality和bytes，未包含rows数据):
20. 慢（两次全表扫描，外层循环处理的行超大，造成最终COST超大）
SELECT STATEMENT, GOAL = ALL_ROWS 301174 1 42
FILTER
TABLE ACCESS FULL SEM SEARCHENGINE_DATA 72 8264 347088
FILTER
HASH GROUP BY 73 1 30
TABLE ACCESS FULL SEM SEARCHENGINE_DATA 72 1 30
26. 快（两次全表扫描，但是外层循环基数为0）
SELECT STATEMENT, GOAL = ALL_ROWS 145 1 42
FILTER
TABLE ACCESS FULL SEM SEARCHENGINE_DATA 72 1 42
FILTER
HASH GROUP BY 73 1 30
TABLE ACCESS FULL SEM SEARCHENGINE_DATA 72 1 30
分析：查询20日数据情况下，子查询返回不为空，在子查询内部使用了全表扫描和HASH GROUP BY,外层查询执行全表扫描（STATDATE列未建索引），符合条件的列数为8264，所以最终的总的COST超大。
查询26日数据时，情况有所不同，父查询的and条件之一（statdate条件）返回结果为NULL，这样在与子查询的返回结果做关联时，符合条件的列数为0，CARDINALITY为1，所以虽然再次做了全表扫描，但是执行超快。
OK，既然是执行计划的原因，开发人员在等着结果，考虑建索引来解决问题,
建立index如下:
create index IND_SEARCHENGINE_KW on searchengine_data(keyword) ;
并对表和索引进行分析，
analyze table searchengine_data compute statistics for table for all indexes for all indexed columns ;
再次检查执行计划，
20： 快(建立索引后的COST比前次大大减少)
SELECT STATEMENT, GOAL = ALL_ROWS 20754 1 42
FILTER
TABLE ACCESS FULL SEM SEARCHENGINE_DATA 72 8264 347088
FILTER
HASH GROUP BY 5 1 30
TABLE ACCESS BY INDEX ROWID SEM SEARCHENGINE_DATA 4 1 30
INDEX RANGE SCAN SEM IND_SEARCHENGINE_KW 1 2

26： 快(使用索引后效果与前次相近，COST略有下降)
SELECT STATEMENT, GOAL = ALL_ROWS 77 1 42
FILTER
TABLE ACCESS FULL SEM SEARCHENGINE_DATA 72 1 42
FILTER
HASH GROUP BY 5 1 30
TABLE ACCESS BY INDEX ROWID SEM SEARCHENGINE_DATA 4 1 30
INDEX RANGE SCAN SEM IND_SEARCHENGINE_KW 1 2

分析：20日的查询与前次执行计划相比较，有很明显的不同，子查询中使用了列keyword上的索引range scan,尽管父查询中用到了多个列，所以仍旧使用了全表扫描，整体的效率还是大大提高（20倍左右，从40秒降至2秒左右）。
而26日的查询与前次执行计划相近（子查询中的微小变化），不再解释。

考虑到semi join这种较高效率的查询发生的条件除了exists以外，通常的还有in，下面继续作探讨，
我们知道，使用exists与使用in的驱动顺序恰好相反，而在前面20日的查询中，很明显，效率低是由于外层循环没有selectivity而导致，分析SQL可以发现子循环具有较大的选择性，因此下面使用in来重写上面的查询,
首先把新建的index删除，
drop index IND_SEARCHENGINE_KW ;
20.
select SEARCHENGINE,USERNAME,CLIENTNAME,KEYWORD,STATDATE,sysdate
from SEARCHENGINE_DATA
where STATDATE=to_date('2008-3-20','yyyy-mm-dd')
and KEYWORD in (select KEYWORD from SEARCHENGINE_DATA where STATDATE=to_date('2008-3-20','yyyy-mm-dd')
and KEYWORD not like '%??%' group by STATDATE,SEARCHENGINE,CLIENTNAME,KEYWORD having count(*) >1) ;
26.
select SEARCHENGINE,USERNAME,CLIENTNAME,KEYWORD,STATDATE,sysdate
from SEARCHENGINE_DATA
where STATDATE=to_date('2008-3-26','yyyy-mm-dd')
and KEYWORD in (select KEYWORD from SEARCHENGINE_DATA where STATDATE=to_date('2008-3-26','yyyy-mm-dd')
and KEYWORD not like '%??%' group by STATDATE,SEARCHENGINE,CLIENTNAME,KEYWORD having count(*) >1) ;
分别检查执行计划，
20: 快 (HASH JOIN RIGHT SEMI)
SELECT STATEMENT, GOAL = ALL_ROWS 146 445 37380
HASH JOIN RIGHT SEMI 146 445 37380
VIEW SYS VW_NSO_1 74 393 16506
FILTER
HASH GROUP BY 74 393 11790
TABLE ACCESS FULL SEM SEARCHENGINE_DATA 72 7851 235530
TABLE ACCESS FULL SEM SEARCHENGINE_DATA 72 8264 347088

26: 快 (in不对NULL进行处理)
SELECT STATEMENT, GOAL = ALL_ROWS 145 1 42
FILTER
TABLE ACCESS FULL SEM SEARCHENGINE_DATA 72 1 42
FILTER
HASH GROUP BY 73 1 30
TABLE ACCESS FULL SEM SEARCHENGINE_DATA 72 1 30

分析：查询20日数据情况下，子查询返回不为空，在子查询内部使用了全表扫描和HASH GROUP BY,原表首先做全表扫描（STATDATE列未建索引），然后与子查询结果（VIEW vw_nso_1）做关联，由于这次关联使用了HASH JOIN RIGHT SEMI，并且子查询的结果集很小，所以整个查询的性能还不错（两次全表扫描+HASH JOIN RIGHT SEMI）。
查询26日数据时，情况有所不同，子查询获得结果为NULL，这样在与父查询做关联时，因为IN不对NULL进行处理，所以in条件直接返回false,这时外层循环的CARDINALITY为1（没有满足条件的记录），所以虽然再次做了全表扫描，但是执行也超快。

最后，为了比较semi join与一般的join之间在性能上的差异，再把上面使用in和exists的查询与下面的查询做个比较，
20.
select t.SEARCHENGINE,t.USERNAME,t.CLIENTNAME,t.KEYWORD,t.STATDATE,sysdate
from SEARCHENGINE_DATA t,(select STATDATE,SEARCHENGINE,CLIENTNAME,KEYWORD
from SEARCHENGINE_DATA where STATDATE=to_date('2008-3-20','yyyy-mm-dd')
and KEYWORD not like '%??%' group by STATDATE,SEARCHENGINE,CLIENTNAME,KEYWORD having count(*) >1) t1
where t.STATDATE=to_date('2008-3-20','yyyy-mm-dd')
and t.keyword = t1.keyword ;

26.
select t.SEARCHENGINE,t.USERNAME,t.CLIENTNAME,t.KEYWORD,t.STATDATE,sysdate
from SEARCHENGINE_DATA t,(select STATDATE,SEARCHENGINE,CLIENTNAME,KEYWORD
from SEARCHENGINE_DATA where STATDATE=to_date('2008-3-26','yyyy-mm-dd')
and KEYWORD not like '%??%' group by STATDATE,SEARCHENGINE,CLIENTNAME,KEYWORD having count(*) >1) t1
where t.STATDATE=to_date('2008-3-26','yyyy-mm-dd')
and t.keyword = t1.keyword ;
检查执行计划，
20.
SELECT STATEMENT, GOAL = ALL_ROWS 146 445 38270
HASH JOIN 146 445 38270
VIEW SEM 74 393 17292
FILTER
HASH GROUP BY 74 393 11790
TABLE ACCESS FULL SEM SEARCHENGINE_DATA 72 7851 235530
TABLE ACCESS FULL SEM SEARCHENGINE_DATA 72 8264 347088
26.
SELECT STATEMENT, GOAL = ALL_ROWS 145 1 86
HASH JOIN 145 1 86
TABLE ACCESS FULL SEM SEARCHENGINE_DATA 72 1 42
VIEW SEM 73 1 44
FILTER
HASH GROUP BY 73 1 30
TABLE ACCESS FULL SEM SEARCHENGINE_DATA 72 1 30

可以发现在没有使用索引的情况下，两次查询的执行计划略有差别，但几乎等同，最重要的在外层循环中都使用了HASH JOIN，从而避免了全表扫描，所以效率较高，并且同时还具有相对稳定的执行计划(对数据变化相对不敏感，极端情况下的NULL),不必存储执行计划。
结论：
1. 作SQL语句的调优时，当判断是否使用in或者exists（包括not in或者not exists）,还是一般的子查询JOIN方式时，要检查执行计划来确定当前应当采用的最高效方式，在执行计划中，in和exists往往会导致CBO选择semi join（或者not in和not exists，将导致anti join）这种高效的查询方式，如果有则往往这个查询计划是性能更优的，这是因为semi join相对于一般的join来说具有更好的性能；
2. 在判断使用in还是exists时，重要的是判断外层循环与内循环的特点，
exists适合于使用在外层循环具有较好的选择性，而内层循环有索引的情况
in适合于使用在内循环具有较好的选择性，而外层循环建有索引的情况。
一般情况下，外层循环和子循环是不同的表，特例情况下，如题目中所示的情况，对于子循环和外层循环都在同一张表上的SQL语句，需要判断子查询和外层查询子集的大小，如果子循环大而外层循环小，则选择exists,反之，若子循环小而外层循环大，则选择in，
总的来说，把选择性应该放在第一位，索引放在第二位；
3. 在确定SQL语句的写法时，还要充分考虑可能的子查询返回null的情况，因为这种情况in和exists的处理方式是不同的，会导致完全不同的执行计划和查询性能，可能会以某种数据库性能不稳定的情况而出现（往往重现还比较困难）。
NOTE:题目中的表并不大，总数据量在2万多条，所以可能有些结果数据不是很具有代表意义，导致结论也不是很准确，期待在以后的实践中继续验证和丰富以上结论。