News zu Oracle
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. Ich habe hier einige Beispiele zusammengestellt, welche das verdeutlichen sollen:
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 |
----------------------------------------------------------------------------------
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 |
--------------------------------------------------------------------------------
nicht abbildbar.
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
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 |
----------------------------------------------------------------------------------------------
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 |
-----------------------------------------------------------------------------------------------
Share this article