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
Sie müssen den Inhalt von reCAPTCHA laden, um das Formular abzuschicken. Bitte beachten Sie, dass dabei Daten mit Drittanbietern ausgetauscht werden.
Mehr InformationenSie sehen gerade einen Platzhalterinhalt von Facebook. Um auf den eigentlichen Inhalt zuzugreifen, klicken Sie auf die Schaltfläche unten. Bitte beachten Sie, dass dabei Daten an Drittanbieter weitergegeben werden.
Mehr InformationenSie sehen gerade einen Platzhalterinhalt von Instagram. Um auf den eigentlichen Inhalt zuzugreifen, klicken Sie auf die Schaltfläche unten. Bitte beachten Sie, dass dabei Daten an Drittanbieter weitergegeben werden.
Mehr InformationenSie sehen gerade einen Platzhalterinhalt von X. Um auf den eigentlichen Inhalt zuzugreifen, klicken Sie auf die Schaltfläche unten. Bitte beachten Sie, dass dabei Daten an Drittanbieter weitergegeben werden.
Mehr Informationen