News zu Oracle

SQL Tuning Tipp: NOT IN vs NOT EXISTS

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 unter­bunden und ein Full Table Scan ausge­fü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 |
------------------------------------------------------------------------------------------
Hier findest du weitere Posts zu den Themen SQL Tuning bzw. Performance Tuning aus unserem News Bereich. 
icon-arrow_right_medium-violet-blue.svg

Share this article

Share on facebook
Facebook 
Share on twitter
Twitter 
Share on linkedin
LinkedIn 
Share on xing
XING 
Share on whatsapp
WhatsApp 
Share on email
Email