News & Events
SQL Tuning Tipp: NOT IN vs NOT EXISTS

NOT IN sollte nicht verwendet werden, wenn die in der Subquery referenzierte Spalte NULL enthalten kann.

Icon Unternehmen

Der Query Optimizer wandelt den NOT-IN-Operator intern in einen NOT-EXISTS-Operator um und legt eine interne NVL()-Funktion um die generierte WHERE-Klausel in der Subquery. Bedingt durch die NVL()- Funktion wird somit eine Indexnutzung unterbunden und ein Full Table Scan ausgeführt. Weitere Einzelheiten hierzu findet man in My Oracle Support, Doc ID 28934.1.

Query mit NOT-IN-Operator

SELECT *
FROM departments d
WHERE department_id NOT IN (
  SELECT department_id
  FROM job_history h);

-----------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |    21 |   525 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN ANTI NA                   |             |    21 |   525 |     6  (17)| 00:00:01 |
|   2 |   SORT JOIN                           |             |    27 |   567 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS |    27 |   567 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN                   | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  5 |   SORT UNIQUE                         |             |    10 |    40 |     4  (25)| 00:00:01 |
|   6 |    TABLE ACCESS FULL                  | JOB_HISTORY |    10 |    40 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Gleiche Query mit NOT-EXISTS-Operator

SELECT *
FROM departments d
WHERE NOT EXISTS (
  SELECT department_id
  FROM job_history h
  WHERE h.department_id=d.department_id);

------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |    21 |   525 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |                     |    21 |   525 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS         |    27 |   567 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | JHIST_DEPARTMENT_IX |     2 |     8 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


Newsletter-Archiv