Hi Experts,
Yesterday, I was searching for the differences between IN and EXIST operators. I googled it and I read a thread in Tom Kyte's site (http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:953229842074). In brief, Tom has written that the usage of both operators are different. They are also processed differently by the optimizer. IN operator works more like join two tables (hased, sorted, etc.) On the other hand, EXIST works more like NESTED LOOP. In other words, Inner loop works once for each row of outer loop. He also suggests that, if the sub query is very huge and the upper query in small. And lets say that the subquery use index, EXIST operator becomes much more appropriate. However, if the subquery is small IN operator become efficient.
In addition, Jonathan Lewis also mentions nested loop in his book (Cost-Based Oracle Fundemantals) with one chapter. He says the following.
"The execution plan for a nested loop join with an index on the inner table can have two different forms from 9i onward: one when the optimizer uses the index on the inner table for a unique scan, and another when the optimizer uses the index for a range scan. The second form ceases to be an option, however, if the outer table is guaranteed to return a single row."
My question is, If you look at the nested loop in the query plans. It is always working like one row returns by unique index foreach row of the outer query like the following. I mean you cannot come accross a nested loop between two tables with FULL TABLE SCAN (Generally Hash join used for this). Does optimizer work like this? If the join condition contains index between two table and one of them is entirely huge according to other. Then does the optimizer always use NESTED LOOP instead of other join methods?
If the above is correct, can we write the following formula?
NESTED LOOP = OUTER QUERY (has low num of rows FTS) & SUB QUERY (has high num of rows with INDEX)
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 58 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Regards
Charlie