von Markus Winand.

DB2 Skripts für den “3-Minuten Test”


Dieser Abschnitt enthält die create, insert und select Kommandos für den „3-Minuten Test“. Mach den Test doch selbst, bevor du weiter liest.

Die create und insert Anweisungen findest du in diesem Download.

Frage 1 — DATE Anit-Pattern

CREATE INDEX tbl_idx ON tbl (date_column)
SELECT COUNT(*)
  FROM tbl
 WHERE TO_CHAR(date_column, 'YYYY') = '2017'
SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= DATE'2017-01-01'
   AND date_column <  DATE'2018-01-01'

Der erste Ausführungsplan liest den ganzen Index (IXSCAN ohne START/STOP aber SARG in den Prädikatinformationen)

Explain Plan
-----------------------------------------------------
ID | Operation          |                 Rows | Cost
 1 | RETURN             |                      |   27
 2 |  GRPBY (COMPLETE)  |    1 of 40 (  2.50%) |   27
 3 |   IXSCAN TBL_IDX   | 40 of 1000 (  4.00%) |   27

Predicate Information
 3 - SARG ( TO_CHAR(TIMESTAMP(Q1.DATE_COLUMN, 0), 'YYYY') = '2017')

Die zweite Abfrage kann den Index optimal nutzen (IXSCAN ohne SARG und mit START/STOP in den Prädikatinformationen).

Explain Plan
------------------------------------------------------
ID | Operation          |                  Rows | Cost
 1 | RETURN             |                       |    6
 2 |  GRPBY (COMPLETE)  |    1 of 100 (  1.00%) |    6
 3 |   IXSCAN TBL_IDX   | 100 of 1000 ( 10.00%) |    6

Predicate Information
 3 - START ('01/01/2017' <= Q1.DATE_COLUMN)
      STOP (Q1.DATE_COLUMN < '01/01/2018')

Beachte, dass DB2 LUW extract-Ausdrücke gut optimieren kann:

SELECT text, date_column
  FROM tbl
 WHERE EXTRACT(YEAR FROM date_column) = 2017
Explain Plan
----------------------------------------------------
ID | Operation          |                Rows | Cost
 1 | RETURN             |                     |   34
 2 |  FETCH TBL         |  19 of 19 (100.00%) |   34
 3 |   RIDSCN           |  19 of 19 (100.00%) |    6
 4 |    SORT (UNIQUE)   |  19 of 19 (100.00%) |    6
 5 |     IXSCAN TBL_IDX | 19 of 186 ( 10.22%) |    6

Predicate Information
 2 - SARG ('01/01/2017' <= Q1.DATE_COLUMN)
     SARG (Q1.DATE_COLUMN <= '12/31/2017')
 5 - START ('01/01/2017' <= Q1.DATE_COLUMN)
      STOP (Q1.DATE_COLUMN <= '12/31/2017')

Frage 2 — Indizierte Top-N-Abfrage

Die Abfrage nutzt den Index (IXSCAN) in umgekehrter Reihenfolge (REVERSE). Beachte vor allem, dass es keine Sortieroperation gibt.

Explain Plan
-----------------------------------------------------------
ID | Operation                  |               Rows | Cost
 1 | RETURN                     |                    |   13
 2 |  FETCH TBL                 |   1 of 7 ( 14.29%) |   21
 3 |   IXSCAN (REVERSE) TBL_IDX | 7 of 186 (  3.76%) |    6

Predicate Information
 3 - START (Q1.A = +00012.)
      STOP (Q1.A = +00012.)

Frage 3 — Spaltenreihenfolge

CREATE INDEX tbl_idx ON tbl (a, b)
SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1
SELECT *
  FROM tbl
 WHERE b = 1
DROP INDEX tbl_idx 
CREATE INDEX tbl_idx ON tbl (b, a)
SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1
SELECT *
  FROM tbl
 WHERE b = 1

Die erste Abfrage kann beide Indizes optimal nutzen:

Explain Plan
-------------------------------------------------
ID | Operation        |               Rows | Cost
 1 | RETURN           |                    |   21
 2 |  FETCH TBL       |   7 of 7 (100.00%) |   21
 3 |   IXSCAN TBL_IDX | 7 of 186 (  3.76%) |    6

Predicate Information
 3 - START (Q1.A = +00038.)
     START (Q1.B = +00001.)
      STOP (Q1.A = +00038.)
      STOP (Q1.B = +00001.)
Explain Plan
-------------------------------------------------
ID | Operation        |               Rows | Cost
 1 | RETURN           |                    |   21
 2 |  FETCH TBL       |   7 of 7 (100.00%) |   21
 3 |   IXSCAN TBL_IDX | 7 of 186 (  3.76%) |    6

Predicate Information
 3 - START (Q1.B = +00001.)
     START (Q1.A = +00038.)
      STOP (Q1.B = +00001.)
      STOP (Q1.A = +00038.)

Die zweite Abfrage kann den ersten Index nicht nutzen, und führt stattdessen einen Full-Table-Scan durch:

Explain Plan
----------------------------------------------
ID | Operation   |                 Rows | Cost
 1 | RETURN      |                      |   99
 2 |  TBSCAN TBL | 40 of 1000 (  4.00%) |   99

Predicate Information
 2 - SARG (Q1.B = +00001.)

Dreht man die Spaltenreihenfolge im Index um, kann auch die zweite Abfrage einen optimalen Indexzugriff durchführen:

Explain Plan
-----------------------------------------------------
ID | Operation          |                 Rows | Cost
 1 | RETURN             |                      |   34
 2 |  FETCH TBL         |   40 of 40 (100.00%) |   34
 3 |   RIDSCN           |   40 of 40 (100.00%) |    6
 4 |    SORT (UNIQUE)   |   40 of 40 (100.00%) |    6
 5 |     IXSCAN TBL_IDX | 40 of 1000 (  4.00%) |    6

Predicate Information
 2 - SARG (Q1.B = +00001.)
 5 - START (Q1.B = +00001.)
      STOP (Q1.B = +00001.)

Frage 4 — LIKE

CREATE INDEX tbl_idx ON tbl (text)
SELECT *
  FROM tbl
 WHERE text LIKE 'TJ%'

Die Abfrage kann den Index optimal nutzen:

Explain Plan
-------------------------------------------------
ID | Operation        |               Rows | Cost
 1 | RETURN           |                    |   13
 2 |  FETCH TBL       |   1 of 1 (100.00%) |   13
 3 |   IXSCAN TBL_IDX | 1 of 300 (   .33%) |    6

Predicate Information
 3 - START ('TJ..................................
      STOP (Q1.TEXT <= 'TJ.......................

Frage 5 — Index Only Scan

CREATE INDEX tbl_idx ON tbl (a, date_column)
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
 GROUP BY date_column
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
   AND b = 1
 GROUP BY date_column

Die erste Abfrage nutzt den Index, um auf der Spalte A zu suchen, kann aber auch die selektierte Spalte DATE_COLUMN aus dem Index lesen (kein FETCH).

Explain Plan
---------------------------------------------------
ID | Operation          |               Rows | Cost
 1 | RETURN             |                    |    0
 2 |  GRPBY (COMPLETE)  |   2 of 2 (100.00%) |    0
 3 |   IXSCAN TBL_IDX   | 2 of 300 (   .67%) |    0

Predicate Information
 3 - START (Q1.A = +00038.)
      STOP (Q1.A = +00038.)

Die zweite Abfrage muss zusätzlich in die Tabelle sehen, um den Filter auf der Spalte B zu prüfen (FETCH). Obwohl dieser Zugriff das Ergebnis schmälert, wird die Abfrage viel langsamer.

Explain Plan
---------------------------------------------------
ID | Operation          |               Rows | Cost
 1 | RETURN             |                    |    6
 2 |  GRPBY (COMPLETE)  |             0 of 0 |    6
 3 |   FETCH TBL        |   0 of 2 (   .00%) |    6
 4 |    IXSCAN TBL_IDX  | 2 of 300 (   .67%) |    0

Predicate Information
 3 - SARG (Q1.B = +00001.)
 4 - START (Q1.A = +00038.)
      STOP (Q1.A = +00038.)

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Sein Buch bei Amazon kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz