Oracle where exists 2, oraclewhereexists

Oracle where exists 2, oraclewhereexists
Where exists 2

I have explained the basic usage of select according to my personal understanding. Of course, exists is not only after select. For example, you can use where exists for update.

Continue with the previous explanation. I can see it on the Internet. Where exists and In are different In efficiency. Let's test and compare how they are different.

First create a test table t4

Create table t4 as select * from emp;

Insert data

Insert into t4 select * from t4;

Select count (*) from t4;

COUNT (*)

----------

14680064

Commit;

Next, write two equivalent exists and in queries and analyze them according to the execution plan.

Set autot traceonly

Select empno, ename from emp where exists (select 1 from t4 where t4.deptno = emp. deptno );

14 rows selected.

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

--------------------------------------------------------------------------------

| 0 | select statement | 20 | 740 | 43 (0) | 00:00:01 |

| 1 | nested loops semi | 20 | 740 | 43 (0) | 00:00:01 |

| 2 | table access full | EMP | 20 | 480 | 3 (0) | 00:00:01 |

| * 3 | index range scan | DEPTNOIND | 3804K | 47M | 2 (0) | 00:00:01 |

--------------------------------------------------------------------------------

Select a. empno, a. ename from emp a where a. deptno in (select deptno from t4 );

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

--------------------------------------------------------------------------------

| 0 | select statement | 20 | 740 | 43 (0) | 00:00:01 |

| 1 | nested loops semi | 20 | 740 | 43 (0) | 00:00:01 |

| 2 | table access full | EMP | 20 | 480 | 3 (0) | 00:00:01 |

| * 3 | index range scan | DEPTNOIND | 3804K | 47M | 2 (0) | 00:00:01 |

--------------------------------------------------------------------------------

As shown above, the execution plans of the two statements are the same. I tested several exists and in statements repeatedly and found that, from the execution plan, I couldn't see anything, maybe it was too simple, so I found some information on the Internet and understood it with my own understanding.

I want to explain the execution principle.

Where exists is a loop. As mentioned before, exists should first cycle the parent table to continuously retrieve the data in the table. Then, the retrieved data is combined with the conditions in the subquery, and the returned value is returned. If there is a returned value, the output of this record is taken. If the record does not match, the returned value is not returned.

The principle of in is as follows.

Select a. empno, a. ename from emp a where a. deptno in (select deptno from t4); can be equivalent

Select a. empno, a. ename from emp a, (select distinct deptno from t4) B where a. deptno = B. deptno;

Then we can see the difference:

First, where exists will traverse the parent table and query the child table (although the traversal of the child table here should be returned only when the conditions are met, does not necessarily traverse the sub-table completely ). If the parent table is small and the child table is large, the writing efficiency is very high, and t4.deptno = emp. deptno can be indexed. Efficiency is not very bad. However, if the parent table is large, this efficiency will not be very high. Because the parent table needs to be traversed (full table scan ).

In the equivalent replacement of in (select distinct deptno from t4), if the table t4 is small, the efficiency is very fast. However, this statement is very inefficient when t4 is very large. First, oracle suspends the Statement of the parent query. First, after the sub-query is executed, the associated query is performed. At this time, if the parent table is large and the child table is small, the efficiency will be higher than where exists.

In general, the efficiency of in and where exists in two tables is similar.

However, if a large sub-table in the parent table is small, in is more efficient than where exists.

On the contrary, if the Sub-table is large but the parent table is small, the efficiency of where exists is faster than that of in.