Performance by Example: NULL or NOT NULL

updated on

Die meisten relationalen Datenbanken verfügen über NOT NULL Constraints. Allerdings hat deren Nutzung abhängig vom Datenbankhersteller unterschiedliche Auswirkungen. Schauen wir uns das Verhalten bei einer Oracle Datenbank einmal genauer an.

Hinweis: Für SQL*Plus AUTOTRACE (um die Ausführungspläne auszugeben) benötigt man entweder DBA Privilegien, die Rolle PLUSTRACE oder entsprechende Berechtigungen – siehe auch meinen Blog Berechtigungen für SQL Tuning.

Erzeugen wir eine Tabelle, um damit verschiedene Tests durchzuführen.

DROP TABLE MITARBEITER;

CREATE TABLE MITARBEITER
   (MID      Number Primary key,
    Vorname  varchar2(30),
    Nachname varchar2(30) NOT NULL,
    eMail    varchar2(80));

Einschub: Primary Key

Dazu werde ich sicher auch noch einen Blog verfassen…

Eine Tabelle kann nur einen Primary Key (PK) enthalten. Dieser stellt sicher, dass es keine doppelten Einträge gibt und ist automatisch NOT NULL. Wir fokussieren uns in unseren Beispiel aber auf die Spalten, die nicht zum PK gehören.

Als nächstes generieren wir einige Daten:

INSERT INTO MITARBEITER 
SELECT ROWNUM, COLUMN_NAME, TABLE_NAME, COLUMN_NAME || '.' || TABLE_NAME || '@' || OWNER || '.COM' 
  FROM ALL_TAB_COLUMNS;
 
COMMIT;

Jetzt noch zwei Indizes für Vorname bzw. Nachname erzeugen:

CREATE INDEX MITARBEITER_VORNAME_IDX ON MITARBEITER(VORNAME);
CREATE INDEX MITARBEITER_NACHNAME_IDX ON MITARBEITER(NACHNAME);

Ab jetzt nutzen wir wieder SQL*Plus Autotrace – alternativ geht das mit jedem beliebigen Programm (SQL Developer, Toad,…), das den Ausführungsplan des Statements anzeigen kann.

Zuerst fragen wir die Tabelle MITARBEITER mit einer Einschränkung auf den Nachnamen ab.
Hinweis: Es sollten wenige 100 Records als Ergebnis kommen. Werden mehr als 500 Records selektiert, muss der Suchbegriff so geändert werden, dass das Ergebnis-Set kleiner wird.

SET AUTOTRACE TRACEONLY

SELECT VORNAME, NACHNAME
  FROM MITARBEITER M
 WHERE NACHNAME like 'ALL_IND%'
   OR NACHNAME IS NULL;
   
237 Zeilen ausgewählt.


---------------------------------------------------------------------------...
| Id  | Operation                           | Name                     | Rows 
---------------------------------------------------------------------------...
|   0 | SELECT STATEMENT                    |                          |  237
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MITARBEITER              |  237
|*  2 |   INDEX RANGE SCAN                  | MITARBEITER_NACHNAME_IDX |  237
---------------------------------------------------------------------------...


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NACHNAME" LIKE 'ALL_IND%')
       filter("NACHNAME" LIKE 'ALL_IND%')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        282  consistent gets
          0  physical reads
          0  redo size
      22714  bytes sent via SQL*Net to client
        634  bytes received via SQL*Net from client
         17  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        237  rows processed

Auf der Spalte NACHNAME ist ein NOT NULL Constraint, damit erkennt der CBO (= Cost Based Optimizer von Oracle), dass es keine Nachnamen gibt, die NULL sein können, und der OR-Teil der WHERE Clause („OR NACHNAME IS NULL„) wird einfach ignoriert. Das ist in der Predicate Information erkennbar, es wird nur auf „NACHNAME“ LIKE ‚ALL_IND%‘ gesucht und gefiltert. Eine Prüfung auf NULL wird einfach weggelassen.

Wie sieht das Ganze aber aus, wenn wir statt dem Nachnamen den Vornamen nutzen?

SET AUTOTRACE TRACEONLY

SELECT  VORNAME, NACHNAME
  FROM MITARBEITER M
 WHERE VORNAME = 'TABLE_OWNER'
    OR VORNAME IS NULL;

189 Zeilen ausgewählt.
…
---------------------------------------------------------...
| Id  | Operation         | Name        | Rows  | Bytes |...
---------------------------------------------------------...
|   0 | SELECT STATEMENT  |             |   192 | 14592 |...
|*  1 |  TABLE ACCESS FULL| MITARBEITER |   192 | 14592 |...
---------------------------------------------------------...

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("VORNAME" IS NULL OR "VORNAME"='TABLE_OWNER')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
…
Statistiken
----------------------------------------------------------
         82  recursive calls
          0  db block gets
       2008  consistent gets
          0  physical reads
          0  redo size
      16121  bytes sent via SQL*Net to client
        601  bytes received via SQL*Net from client
         14  SQL*Net roundtrips to/from client
         14  sorts (memory)
          0  sorts (disk)
        189  rows processed

Obwohl es vergleichbar viele Ergebniszeilen gibt, hat sich der CBO zu einem FULL TABLE SCAN entschieden. Der Grund ist, dass er ohne NOT NULL Constraint davon ausgehen muss, dass einige Datensätze keinen Wert für einen Vornamen haben. Da Oracle bei Single Column Indizes die NULLs nicht im Index einträgt, bleibt dem CBO nichts anderes als ein Full Table Scan übrig.
Die Predicate Information zeigt das eindeutig: „VORNAME“ IS NULL OR „VORNAME“=’TABLE_OWNER‘ – es wird auch auf NULL geprüft.

Beim Vergleich der für das Ergebnis benötigten Aufwände sieht man ebenfalls den Vorteil, den der NOT NULL Constraint bringt:

  • NACHNAME (mit NOT NULL Constraint): 237 Zeilen im Ergebnis, 282 consistent gets (= logische Blockzugriffe)
  • VORNAME (ohne NOT NULL Constraint): 189 Zeilen im Ergebnis, 2008 consistent gets

Obwohl weniger Datensätze gefunden wurde, mussten um Faktoren mehr Blöcke durchsucht werden. Bei so kleinen Datenmengen wird die Statement-Laufzeit noch nicht relevant sein – aber der Zugriff auf die Vornamen hat schon 2-3 mal so lange gedauert wie auf die Nachnamen.

Wie verhält sich Oracle eigentlich, wenn es einen Compound (=mehrspaltigen) Index gibt, wo zumindest eine Spalte Not Null ist?

CREATE INDEX MITARBEITER_NAME_COMB_IDX ON MITARBEITER(VORNAME, NACHNAME);
DROP INDEX MITARBEITER_VORNAME_IDX;

SET AUTOTRACE TRACEONLY

SELECT VORNAME, NACHNAME
  FROM MITARBEITER M
 WHERE VORNAME = 'TABLE_OWNER'
    OR VORNAME IS NULL;

189 Zeilen ausgewählt.
…
------------------------------------------------------------------------...
| Id  | Operation          | Name                      | Rows  | Bytes |...
------------------------------------------------------------------------...
|   0 | SELECT STATEMENT   |                           |   204 |  6936 |...
|   1 |  VIEW              | VW_ORE_B3698217           |   204 |  6936 |...
|   2 |   UNION-ALL        |                           |       |       |...
|*  3 |    INDEX RANGE SCAN| MITARBEITER_NAME_COMB_IDX |   189 |  6426 |...
|*  4 |    INDEX RANGE SCAN| MITARBEITER_NAME_COMB_IDX |    15 |   510 |...
------------------------------------------------------------------------...

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("VORNAME"='TABLE_OWNER')
   4 - access("VORNAME" IS NULL)
       filter(LNNVL("VORNAME"='TABLE_OWNER'))
…
Statistiken
----------------------------------------------------------
         22  recursive calls
          0  db block gets
        398  consistent gets
          0  physical reads
          0  redo size
       8087  bytes sent via SQL*Net to client
        593  bytes received via SQL*Net from client
         14  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        189  rows processed

Der CBO hat jetzt den Compound Index genutzt, musste aber zwei mal Lesen. Beim ersten Mal (ID=3) wird auf Grund von „VORNAME“=’TABLE_OWNER‘ nach dem Vornamen gesucht, beim zweiten Mal (ID=4) wird geprüft, ob es welche mit NULL gibt („VORNAME“ IS NULL …). Der Grund, warum der Index zweimal gelesen wird, ist das „OR“ in der WHERE Clause. Entweder Vorname ODER Null löst der CBO auf mittels zwei Suchen im Index, die mit UNION-ALL verknüpft werden. Obwohl diese Vorgehensweise mit 398 consistent gets schon deutlich effizienter ist als zuvor (2008 consistent gets), bedeutet dies immer noch deutlich mehr Aufwand (als 282 consistent gets bei NOT NULL) durch das zweimalige Lesen im Index.

Führen wir jetzt diesen NOT NULL Constraint ein, um diese Aussage zu überprüfen:

ALTER TABLE MITARBEITER MODIFY (VORNAME NOT NULL);
DROP INDEX MITARBEITER_NAME_COMB_IDX ;
CREATE INDEX MITARBEITER_VORNAME_IDX ON MITARBEITER(VORNAME);

SET AUTOTRACE TRACEONLY

SELECT VORNAME, NACHNAME
  FROM MITARBEITER M
 WHERE VORNAME = 'TABLE_OWNER'
    OR VORNAME IS NULL;
    
189 Zeilen ausgewählt.
…
--------------------------------------------------------------------------...
| Id  | Operation                           | Name                    | Rows
--------------------------------------------------------------------------...
|   0 | SELECT STATEMENT                    |                         |  189
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MITARBEITER             |  189
|*  2 |   INDEX RANGE SCAN                  | MITARBEITER_VORNAME_IDX |  189
--------------------------------------------------------------------------...

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("VORNAME"='TABLE_OWNER')
…
Statistiken
----------------------------------------------------------
         19  recursive calls
          0  db block gets
        295  consistent gets
          2  physical reads
          0  redo size
       8087  bytes sent via SQL*Net to client
        593  bytes received via SQL*Net from client
         14  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        189  rows processed

Wie erwartet, wird der Index nur noch einmal gelesen, und dafür werden auch nur 295 consistent gets benötigt.

Oracle bietet neben dem NOT NULL Constraint noch CHECK Constraints an, um die Inhalte zu prüfen. Wie verhält es sich, wenn statt einem NOT NULL Constraint ein CHECK Constraint mit IS NOT NULL genutzt wird?

ALTER TABLE MITARBEITER ADD CONSTRAINT EMAIL_NOT_NULL CHECK (EMAIL IS NOT NULL);

CREATE INDEX MITARBEITER_EMAIL_IDX ON MITARBEITER(EMAIL);

SET AUTOTRACE TRACEONLY

SELECT VORNAME, NACHNAME, EMAIL
  FROM MITARBEITER M
 WHERE EMAIL LIKE 'TABLE_OWNER.ALL%'
    OR EMAIL IS NULL;

38 Zeilen ausgewählt.
…
---------------------------------------------------------...
| Id  | Operation         | Name        | Rows  | Bytes |...
---------------------------------------------------------...
|   0 | SELECT STATEMENT  |             |    43 |  3268 |...
|*  1 |  TABLE ACCESS FULL| MITARBEITER |    43 |  3268 |...
---------------------------------------------------------...

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMAIL" IS NULL OR "EMAIL" LIKE 'TABLE_OWNER.ALL%')
…
Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1352  consistent gets
          0  physical reads
          0  redo size
       3755  bytes sent via SQL*Net to client
        494  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         38  rows processed

Im Ergebnis sind nur noch 38 Zeilen zurück kommen, aber Oracle ignoriert den Index auf EMAIL und nutzt immer einen Full Table Scan. Obwohl wir auf Grund des Check Constraints auf IS NOT NULL sicher sind, dass keine Nulls vorhanden sein können, prüft der CBO immer noch auf „EMAIL“ IS NULL OR „EMAIL“ LIKE ‚TABLE_OWNER.ALL%‘.

Was wäre, wenn wir einen NOT NULL Constraint statt dem Check Constraint angelegt hätten?

ALTER TABLE MITARBEITER MODIFY (EMAIL NOT NULL);

SET AUTOTRACE TRACEONLY

SELECT VORNAME, NACHNAME, EMAIL
  FROM MITARBEITER M
 WHERE EMAIL LIKE 'TABLE_OWNER.ALL%'
    OR EMAIL IS NULL;

38 Zeilen ausgewählt.
…
------------------------------------------------------------------------...
| Id  | Operation                           | Name                  | Rows
------------------------------------------------------------------------...
|   0 | SELECT STATEMENT                    |                       |   38
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MITARBEITER           |   38
|*  2 |   INDEX RANGE SCAN                  | MITARBEITER_EMAIL_IDX |   38
------------------------------------------------------------------------...

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMAIL" LIKE 'TABLE_OWNER.ALL%')
       filter("EMAIL" LIKE 'TABLE_OWNER.ALL%')
…
Statistiken
----------------------------------------------------------
         30  recursive calls
          0  db block gets
        189  consistent gets
          0  physical reads
          0  redo size
       4391  bytes sent via SQL*Net to client
        494  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         38  rows processed

Schon wird der Index genutzt, weil dem CBO klar ist: Da kann keine NULL enthalten sind.

Wir wissen inzwischen, dass ein Single Column Index NULLs nicht indiziert. Ein Compound oder Composite (multicolumn) Index, der zumindest eine NOT NULL Spalte enthält, kann dazu genutzt werden, die NULLs in anderen Spalten zu zählen. Was passiert aber, wenn ein Compound Index nur NULLABLE Spalten enthält?

Entfernen wir zuerst die NOT NULL Constraints von den Spalten Vorname und Nachnahme und updaten dann 100 Datensätze so, dass Vorname und Nachname Null sind:

SET AUTOTRACE OFF

ALTER TABLE MITARBEITER MODIFY (VORNAME NULL);
ALTER TABLE MITARBEITER MODIFY (NACHNAME NULL);

UPDATE MITARBEITER SET VORNAME=NULL, NACHNAME=NULL WHERE ROWNUM <=100;
COMMIT;

Jetzt erzeugen wir wieder einen Compound Index auf diese Spalten und prüfen, wie viele Datensätze in der Tabelle sind und wie viele Indexeinträge im Index vorhanden sind:

CREATE INDEX MITARBEITER_NAME_COMB_IDX ON MITARBEITER(VORNAME, NACHNAME);

SELECT COUNT(*) FROM MITARBEITER;

  COUNT(*)
----------
    121765
    
SELECT NUM_ROWS FROM USER_INDEXES WHERE INDEX_NAME='MITARBEITER_NAME_COMB_IDX';
    
  NUM_ROWS
----------
    121665

Es sind genau 100 Einträge im Index weniger als in der Tabelle. Ein Compound Index alleine reicht nicht aus, um auf NULL anfragen zu können.

Zusammenfassung

Wie man schon an diesen einfachen Beispielen sieht, hat ein NOT NULL Constraint teilweise erhebliche Auswirkungen auf den Ausführungsplan und somit auf die Verarbeitungslaufzeit. Je komplexer Statements werden (Komplexe WHERE Clauses, JOINS und hier vor allem OUTER JOINS, etc.) um so wichtiger ist es, dass NOT NULL Constraints genutzt werden, wo immer es möglich ist.

  • NOT NULL Constraints werden vom CBO korrekt interpretiert. Check Constraints auf IS NOT NULL werden hingegen nicht in die Überlegungen zum Ausführungsplan berücksichtigt.
  • Wenn eine Spalte immer Werte haben muss, sollte diese auch einen NOT NULL Constraint haben, damit der CBO einen besseren Ausführungsplan erzeugen kann.
  • Single Column Indizes enthalten bei Oracle Datenbanken keine NULL Werte.
  • Bei Compound Indizes kommen sehr wohl auch NULLs vor, allerdings nur dann, wenn zumindest eine Spalte einen Wert hat. Für eine Abfrage auf NOT NULL ist das nicht ausreichend.
  • Ein Primary Key Constraint ist aus Sicht des CBO ein Unique Key inklusive NOT NULL.

Weiterführende Informationen

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

This site uses Akismet to reduce spam. Learn how your comment data is processed.