Efficient analysis with IN operator


SQL developers frequently hear and realize that the subqueries are not efficient,
avoid using them.
Indeed, many times those SELECT statements, which contains subqueries
in the WHERE or HAVING clause are not very efficient.

But this not a general rule!

I'd like to demonstrate that subqueries with IN operator and
correlated subqueries can be very efficient.

Let see the next example:
 
SELECT last_name, department_id, job_id 
FROM employees
WHERE department_id IN 
(SELECT department_id FROM departments WHERE location_id = 1700);

Here is the execution plan for the SELECT statement:

 
SQL_ID  gm32t8f65882q, child number 0
-------------------------------------
SELECT last_name, department_id, job_id FROM   employees WHERE
department_id IN (SELECT department_id FROM departments WHERE  location_id = 1700)
Plan hash value: 1021246405
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |       |       |     6 (100)|          |
|   1 |  NESTED LOOPS                |                   |       |       |            |          |
|   2 |   NESTED LOOPS               |                   |    37 |   814 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | DEPARTMENTS       |     4 |    20 |     4   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   170 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("LOCATION_ID"=1700)
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
====================================================================================================

Let us see the second solution and the execution plan!

 
SELECT last_name, e.department_id, job_id
FROM employees e,departments d
WHERE e.department_id =d.department_id and location_id = 1700;
 
SQL_ID  gq10rm3ypv7v2, child number 0
-------------------------------------
SELECT last_name, e.department_id, job_id FROM   employees
e,departments d WHERE  e.department_id =d.department_id and
location_id = 1700
Plan hash value: 1021246405
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |       |       |     6 (100)|          |
|   1 |  NESTED LOOPS                |                   |       |       |            |          |
|   2 |   NESTED LOOPS               |                   |    37 |   814 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | DEPARTMENTS       |     4 |    20 |     4   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   170 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("LOCATION_ID"=1700)
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
====================================================================================================

We can realize that both quires use the same execution plan (The plan Hash values are same!)
so they are same efficient!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: