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.

Holen Sie sich Markus

…für ein Training ins Büro.

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

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