Thursday, January 28, 2010

Performance Tuning using Windowing Aggregate Functions to avoid table self-join


SQL> select first_name,last_name,salary,department_id
2 from hr.employees a
3 where a.salary=(select max(salary) from hr.employees b
4 where a.department_id=b.department_id);

FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID
-------------------- ------------------------- ---------- -------------
Nancy Greenberg 12000 100
Den Raphaely 11000 30
Michael Hartstein 13000 20
Hermann Baer 10000 70
Steven King 24000 90
Shelley Higgins 12000 110
Adam Fripp 8200 50
Susan Mavris 6500 40
John Russell 14000 80
Jennifer Whalen 4400 10
Alexander Hunold 9000 60

11 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1769107035

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 5 (20)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 48 | 5 (20)| 00:00:01 |
| 3 | VIEW | VW_SQ_1 | 1 | 26 | 4 (25)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | HASH GROUP BY | | 1 | 10 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1070 | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 22 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter(MAX("SALARY")>0)
7 - access("A"."DEPARTMENT_ID"="ITEM_1")
8 - filter("A"."SALARY"="MAX(SALARY)")




SQL> select first_name,last_name,salary,department_id
2 from (select max(salary) over (partition by department_id) as max_salary,
3 first_name,last_name,salary,department_id
4 from hr.employees) a
5 where a.salary=max_salary and a.department_id is not null;

FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID
-------------------- ------------------------- ---------- -------------
Jennifer Whalen 4400 10
Michael Hartstein 13000 20
Den Raphaely 11000 30
Susan Mavris 6500 40
Adam Fripp 8200 50
Alexander Hunold 9000 60
Hermann Baer 10000 70
John Russell 14000 80
Steven King 24000 90
Nancy Greenberg 12000 100
Shelley Higgins 12000 110

11 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 720055818

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 6890 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 106 | 6890 | 4 (25)| 00:00:01 |
| 2 | WINDOW SORT | | 106 | 2332 | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMPLOYEES | 106 | 2332 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"."SALARY"="MAX_SALARY")
3 - filter("DEPARTMENT_ID" IS NOT NULL)