News & Events
SQL Tuning Tipp: Subquery in SELECT-Klausel

In der SELECT-Klausel sollten keine Subqueries auftauchen. Die so bezogenen Daten können immer auch über einen Join referenziert werden.

Icon Unternehmen

Subquery in der SELECT-Klausel

SELECT first_name, last_name,
  (SELECT department_name
  FROM departments
  WHERE department_id = e.department_id)
FROM employees e;

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   107 |  1926 |    14   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|   3 |  TABLE ACCESS FULL          | EMPLOYEES   |   107 |  1926 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Ersetzung durch einen Join

SELECT first_name, last_name, department_name
FROM employees e 
  LEFT JOIN departments d using (department_id);

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   107 |  3638 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |             |   107 |  3638 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES   |   107 |  1926 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------


Newsletter-Archiv