News & Events
SQL Tuning Tipp: Analytische Funktionen

Analytische Funktionen sind ein mächtiges Werkzeug zur Formulierung von Abfragen, die nicht oder nur aufwändig mit herkömmlichen Aggregatfunktionen zu formulieren wären.

Icon Unternehmen

Hinsichtlich Performance liegt die Stärke analytischer Funktionen darin, dass die analytischen Aggregate gleich parallel zur eigentlichen Abfrage berechnet werden – statt separater Berechnung in Subqueries verbunden mit JOIN-Kosten.

Beispiel 1: Ermittle die Differenz der Gehälter zum Abteilungsdurchschnitt

Mit herkömmlichen Aggregatfunktionen:

SELECT first_name, last_name, salary emp_salary, avg_salary dept_avg, salary - avg_salary salary_diff
FROM employees e JOIN (
  SELECT department_id, round(AVG(salary)) avg_salary
  FROM employees
  GROUP BY department_id) USING ( department_id ); 

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |   339 | 19323 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN           |           |   339 | 19323 |     7  (15)| 00:00:01 |
|   2 |   VIEW               |           |    11 |   286 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |           |    11 |   176 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMPLOYEES |   107 |  1712 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMPLOYEES |   107 |  3317 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Mit analytischer Funktion:

SELECT first_name, last_name, salary emp_salary, 
  round(avg(salary) over (partition by department_id)) dept_avg, 
  salary - round(avg(salary) over (partition by department_id)) salary_diff
FROM employees;

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

 

Beispiel 2: Bestimme das Ranking der Mitarbeiter innerhalb ihrer Abteilung nach Gehalt

Mit herkömmlichen Aggregatfunktionen nicht abbildbar.

Mit analytischer Funktion:

SELECT first_name, last_name, department_name, salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) rang
FROM employees JOIN departments using (department_id);

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |   106 |  4982 |     7  (29)| 00:00:01 |
|   1 |  WINDOW SORT                  |             |   106 |  4982 |     7  (29)| 00:00:01 |
|   2 |   MERGE JOIN                  |             |   106 |  4982 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |             |   107 |  3317 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL         | EMPLOYEES   |   107 |  3317 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

FIRST_NAME           LAST_NAME            DEPARTMENT_NAME          SALARY       RANG
-------------------- -------------------- -------------------- ---------- ----------
Jennifer             Whalen               Administration             4400          1
Michael              Hartstein            Marketing                 13000          1
Pat                  Fay                  Marketing                  6000          2
Den                  Raphaely             Purchasing                11000          1
Alexander            Khoo                 Purchasing                 3100          2
Shelli               Baida                Purchasing                 2900          3
Sigal                Tobias               Purchasing                 2800          4

 

Beispiel 3: Ermittle das zweithöchste Gehalt je Abteilung

Mit herkömmlichen Aggregatfunktionen:

SELECT department_name, max(salary) salary – von diesen Gehältern das größte
FROM employees e JOIN departments d on (e.department_id=d.department_id)
-- alle Gehälter, die kleiner als das größte sind
WHERE salary < ANY (  
  SELECT salary 
  FROM employees
  WHERE department_id=e.department_id)
GROUP BY department_name;

----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |     5 |   240 |    10  (20)| 00:00:01 |
|   1 |  HASH GROUP BY                 |             |     5 |   240 |    10  (20)| 00:00:01 |
|*  2 |   HASH JOIN SEMI               |             |     5 |   240 |     9  (12)| 00:00:01 |
|   3 |    MERGE JOIN                  |             |   106 |  3392 |     6  (17)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  6 |     SORT JOIN                  |             |   107 |  1712 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1712 |     3   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL           | EMPLOYEES   |   107 |  1712 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Mit analytischer Funktion:

SELECT distinct department_name, salary
FROM (
  SELECT department_name, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) rang
  FROM employees JOIN departments USING (department_id))
WHERE rang=2;

-----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |    27 |  1026 |     8  (38)| 00:00:01 |
|   1 |  HASH UNIQUE                    |             |    27 |  1026 |     8  (38)| 00:00:01 |
|*  2 |   VIEW                          |             |   106 |  4028 |     7  (29)| 00:00:01 |
|*  3 |    WINDOW SORT PUSHED RANK      |             |   106 |  3392 |     7  (29)| 00:00:01 |
|   4 |     MERGE JOIN                  |             |   106 |  3392 |     6  (17)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  7 |      SORT JOIN                  |             |   107 |  1712 |     4  (25)| 00:00:01 |
|   8 |       TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1712 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

 

 

 

 


Newsletter-Archiv