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!
Advertisement
