Efficient Analysis with correlated subquires


Many developers think that correlated subquires are not efficient.
The correct answer starts with "It depends on...".
Sometimes we can achieve the same efficient perfomance
what we can do with inline views.
Let's see the following example:
(Who earns more then his/her departments's average salary?)

SELECT  e.last_name, e.department_id, e.salary  FROM employees e WHERE e.salary> (SELECT AVG(salary) FROM employees where department_id=e.department_id );

SQL_ID  3jr7194pfhh3s, child number 0
-------------------------------------
select  e.last_name, e.department_id, e.salary  from employees e where
e.salary> (select avg(salary) from employees where
department_id=e.department_id )
Plan hash value: 2167610409
----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |       |       |     8 (100)|          |
|*  1 |  HASH JOIN           |           |     5 |   190 |     8  (25)| 00:00:01 |
|   2 |   VIEW               | VW_SQ_1   |    11 |   286 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |           |    11 |    77 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMPLOYEES |   107 |   749 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMPLOYEES |   107 |  1284 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ITEM_1"="E"."DEPARTMENT_ID")
filter("E"."SALARY">"AVG(SALARY)")
====================================================================================================
We can try with inline view:

SELECT e.last_name, e.department_id, e.salary ,d.average FROM employees e, (SELECT department_id,AVG(salary) average FROM employeesGROUP BY department_id ) d WHERE e.department_id=d.department_id AND e.salary>d.average;

SQL_ID  b77bmkd7rn84h, child number 0
-------------------------------------
select e.last_name, e.department_id, e.salary ,d.average from employees
e,(select department_id,avg(salary) average from employees group by
department_id ) d where e.department_id=d.department_id and
e.salary>d.average
Plan hash value: 2695105989
----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |       |       |     8 (100)|          |
|*  1 |  HASH JOIN           |           |     5 |   190 |     8  (25)| 00:00:01 |
|   2 |   VIEW               |           |    11 |   286 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |           |    11 |    55 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMPLOYEES |   107 |   535 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMPLOYEES |   107 |  1284 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."SALARY">"D"."AVERAGE")
====================================================================================================
As we can see the result and execution plans are identical!
Just the name of the temporary views are different.
We can realize that Oracle - sometimes - rewrite our SELECT statement achieving the same result,
but with better performance
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: