The EXISTS and IN Constructs

In this section we will discuss how Oracle evaluates EXISTS and IN clauses, prerequisites for using Oracle’s semi-join access paths, and hints that influence semi-join query optimization. Then we’ll look at a few sample queries where semi-join access paths can be used to make the query more efficient.

How Oracle Evaluates EXISTS and IN Clauses

The SQL language as implemented in Oracle does not include a syntax for explicitly performing semi-joins, so you use an EXISTS or IN clause with a subquery to indicate to Oracle that a semi-join should be performed instead of a conventional join. Oracle will typically transform the subquery into a join wherever possible.
Oracle没有半连接的明确语法，但是我们可以使用exists和in进行暗示，希望Oracle可以采用半连接，半连接也有nested和hash两种半连接。

An interesting characteristic of Oracle’s query optimizer is that (according to Metalink document 144967.1), in Oracle 8i and 9i the decision of whether or not to transform a query by merging its subqueries into the main body is a heuristic one. Basically, Oracle will merge subqueries if it can, regardless of impact on cost. The assumption is that merging is always a good thing. In fact, Oracle performs query transformations (merging subqueries into the main body) before making a list of possible execution plans and evaluating their cost. This bit of trivia will come into play when we discuss the second example in this section.
Oracle会进行查询转换，在这里就是将子查询合并到父查询里面去。这也是Oracle优化器经常采用的一个方法，另外在处理视图的时候，oracle也倾向于将视图合并到表中去。

The Oracle Performance Tuning Guide in the Oracle 8i documentation set explains that EXISTS and IN are processed differently by the query optimizer. The manual explains a rule of thumb for when to use each, and provides an example to demonstrate. The Oracle 9i Performance Tuning Guide provides the same rule of thumb with a slightly more polished example. The Oracle 10g Performance Tuning Guide contains substantially the same text as the Oracle 9i manual on this subject.

The rule of thumb goes like this: If the main body of your query is highly selective, then an EXISTS clause might be more appropriate to semi-join to the target table. However, if the main body of your query is not so selective and the subquery (the target of the semi-join) is more selective, then an IN clause might be more appropriate.
Oracle的文档中有这么一个说法，如果主查询的可选择特别高，但是子查询的选择性相对较低，最还是使用exists，反过来，最好采用in。

The example in the Oracle 8i Performance Tuning Guide shows a query where the main body is not selective, while the target of the semi-join is highly selective. The example demonstrates that Oracle chooses a poor execution plan when an EXISTS clause is used, and a much more efficient execution plan when an IN clause is used instead. I was easily able to reproduce this example in an Oracle 8i environment with the same results as shown in the Performance Tuning Guide.

However, when I reproduced this same example in an Oracle 9i environment, Oracle chose an identical (and efficient) execution plan for both versions of the query. Furthermore, the example in the Oracle 9i Performance Tuning Guide gave an identical (and efficient) execution plan for both versions of the query in my Oracle 9i environment. These tests echo my previous experience—that Oracle 9i is smarter than Oracle 8i about EXISTS and IN clauses, and you don’t typically need to worry about when to use EXISTS versus IN.从oracle9i开始，就没有必要区分in和exists，因为他们会采用相同的执行计划。

The upshot is that if you are writing queries for an Oracle 8i (or earlier) system, then you should think about the selectivity of the predicates in the main query versus the subquery and choose whether to use an EXISTS or an IN clause wisely. If you are using Oracle 9i or later, it doesn’t hurt to follow this rule of thumb, but you may not need to worry as much.

   支持