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 Anti-Pattern
--#SET TERMINATOR ;
CREATE INDEX tbl_idx ON tbl (date_column);--#SET TERMINATOR ;
SELECT COUNT(*)
FROM tbl
WHERE TO_CHAR(date_column, 'YYYY') = '2017';--#SET TERMINATOR ;
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) = 2017Explain 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
--#SET TERMINATOR ;
CREATE INDEX tbl_idx ON tbl (a, b);--#SET TERMINATOR ;
SELECT *
FROM tbl
WHERE a = 38
AND b = 1;--#SET TERMINATOR ;
SELECT *
FROM tbl
WHERE b = 1;--#SET TERMINATOR ;
DROP INDEX tbl_idx ;--#SET TERMINATOR ;
CREATE INDEX tbl_idx ON tbl (b, a);--#SET TERMINATOR ;
SELECT *
FROM tbl
WHERE a = 38
AND b = 1;--#SET TERMINATOR ;
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
--#SET TERMINATOR ;
CREATE INDEX tbl_idx ON tbl (text);--#SET TERMINATOR ;
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.......................Lerne mehr darüber
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.)
