Oracle Tuning in and Exists____oracle

In and exists

I believe a lot of friends like me, have been for these two questions about who the faster problem tangled up, and also the brain for some reason to write down some of the standards, such as in the case of small child table in a faster, in the case of a smaller situation to use exists and so on.

But the actual 11g you do under the test, you will feel very strange.

In the case of a larger two-child table and a larger appearance, in and exists will get the same execution plan.

In the early 10g,oracle it seems that the difference between in and exists is optimized, and there is usually no further difference in performance direction, since the Oracle optimizer automatically rewrites the statements in and exists into the appropriate joins based on the size of the two tables.

So the conclusion is that in the current Oracle version, in and exists are basically equivalent in performance, and there's no need to consider using one of these to replace the other. SEMI Join and anti join

When you view the execution plan of the in and exists statements, you may often see the above two joins.

Semijoin Semi-junction

Antijoin Inverse Connection

The concept of in,exists : The execution plan, which is commonly used in the production of, and so on, takes an in statement for example. Like what

Select a.* from a where a.id in (1,3,5,7)

Let's say our last query takes a table-driven nestloop, when we take a record out of form a and then compare it to (1,3,5,7). If the record value is 1, the record returns true on the first comparison, and the record can be returned directly without having to do a one by one comparison with (3,5,7). This is equivalent to comparing the partial values in the list to determine the result. So called half coupling.

the concept of In,not: The implementation plan produced by the common language not the exists. and take notin examples.

Select a.* from a where a.id not in (1,3,5,7)

When we take a record from a, the record value is 1, and when we do the first comparison, we find that the result is false, then the record can be directly judged to be unsatisfied, and can be discarded directly to the next data in table A. In this case, as with the semi-join operation, there is no need to produce a comparison of the product of the two-table record number, so it can also save costs. This is the reverse link.

It should be emphasized that the semi-join and the inverse join is a kind of logical connection, like the equivalent connection, cross join, and so on, it is not the three implementation methods (Nest Loop,sort Merge,hash) other than the implementation. So there are three ways to implement the half join and the inverse join, for example, I can be a hash join anti (hash counter), Nest Loop anti (nested loop counter), and so on. Not in and not exists

Notin and not exists are logically not exactly equivalent. Therefore, be sure to verify that the logic is equivalent before you change the writing, and that you can overwrite it.

The notin and not exists are not equivalent because of the different processing of NULL.

First, it must be understood that null values cannot perform various arithmetic operations in an Oracle database, including = and <>. To determine whether or not to be empty, you can only use is null and is not NULL.

Select* from TabA where id = null;

Select* from TabA where ID <> null;

The above two SQL will not return any results, even if the ID column has null values. This is because null values cannot be equal or unequal to the judgment.

Then there is the difference between not and not exists.

Quote a picture of http://blog.csdn.net/elvis_dataguru/article/details/8426886.

This picture is a good understanding.

When not in is used, null values are not returned anyway. When there is a null value in the subquery, there is no return result.

And NOT exists because = the judge is in the subquery, so the null value of the outer surface can be returned.