News zu Oracle
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.
Schauen wir uns nun also an, wie sich die Operatoren NOT-IN und NOT-EXISTS in einer Query verhalten.
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 |
------------------------------------------------------------------------------------------
Share this article