DB2 Skripte 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.

Tabellen Setup

Beispiel C.13.

CREATE TABLE tbl (
  id          NUMERIC NOT NULL,
  date_column DATE,
  a           NUMERIC,
  b           NUMERIC,
  text        VARCHAR(255),
  state       CHAR(1),
  PRIMARY KEY (id)
);
--#SET TERMINATOR ;;
CREATE FUNCTION random_string(minlen NUMERIC, maxlen NUMERIC)
RETURNS VARCHAR(1000)
LANGUAGE SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN
  DECLARE rv  VARCHAR(1000) DEFAULT '';
  DECLARE i   NUMERIC       DEFAULT 0;
  DECLARE len NUMERIC       DEFAULT 0;

  IF maxlen < 1 OR minlen < 1 OR maxlen < minlen THEN
    RETURN NULL;
  END IF;

  SET i = floor(rand()*(maxlen-minlen)) + minlen;
  WHILE (i > 0)  DO
    SET rv = rv || chr(97+CAST(rand() * 25 AS INTEGER));
    SET i  =  i - 1;
  END WHILE;
  RETURN rv;
END
;;
--#SET TERMINATOR ;
INSERT INTO tbl (id, date_column, a, b, text, state)
WITH generate_series (n) AS ( 
     SELECT 1 FROM sysibm.sysdummy1 
  UNION ALL 
     SELECT n+1 
       FROM generate_series 
      WHERE n < 50000 
) 
SELECT generate_series.n 
     , CURRENT_DATE - generate_series.n DAYS
     , MOD(generate_series.n,1234) 
     , CAST(RAND() * 10 AS NUMERIC), random_string(20, 20) 
     , CASE MOD(generate_series.n,5) WHEN 0 THEN 'X' ELSE 'A' END 
FROM generate_series;

Question 1 — DATE Anit-Pattern

Beispiel C.14.

CREATE INDEX tbl_idx ON tbl (date_column);
EXPLAIN PLAN FOR
 SELECT text, date_column
   FROM tbl
  WHERE TO_CHAR(date_column, 'YYYY') = '2015';
EXPLAIN PLAN FOR
 SELECT text, date_column
   FROM tbl
  WHERE date_column >= TO_DATE('2015-01-01', 'YYYY-MM-DD')
    AND date_column <  TO_DATE('2016-01-01', 'YYYY-MM-DD');
DROP INDEX tbl_idx;

Der erste Ausführungsplan liest die ganze Tabelle (TBSCAN). Der zweite Ausführungsplan verwendet den Index (IXSCAN).

Explain Plan
--------------------------------------------------------------------------
ID | Operation   |                    Rows |             ActualRows | Cost
 1 | RETURN      |                         |  285 of 2000 ( 14.25%) |  621
 2 |  TBSCAN TBL | 2000 of 50000 (  4.00%) | 285 of 50000 (   .57%) |  621

Predicate Information
 2 - SARG ( TO_CHAR(TIMESTAMP(Q1.DATE_COLUMN, 0), 'YYYY') = '2015')
Explain Plan
------------------------------------------------------------------------------
ID | Operation        |                   Rows |             ActualRows | Cost
 1 | RETURN           |                        |   285 of 285 (100.00%) |   40
 2 |  FETCH TBL       |   285 of 285 (100.00%) |   285 of 285 (100.00%) |   40
 3 |   IXSCAN TBL_IDX | 285 of 50000 (   .57%) | 285 of 50000 (   .57%) |   15

Predicate Information
 3 - START ('2015-01-01-00.00.00.000000' <= Q1.DATE_COLUMN)
      STOP (Q1.DATE_COLUMN < '2016-01-01-00.00.00.000000')

Question 2 — Indexed Top-N

Beispiel C.15.

CREATE INDEX tbl_idx ON tbl (a, date_column);
SELECT id, date_column
  FROM tbl
 WHERE a = 123
 ORDER BY date_column DESC
 FETCH FIRST 1 ROW ONLY;
DROP INDEX tbl_idx;

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

Explain Plan
-------------------------------------------------------------------------------------
ID | Operation                  |                  Rows |           ActualRows | Cost
 1 | RETURN                     |                       |     1 of 1 (100.00%) |   20
 2 |  FETCH TBL                 |     1 of 40 (  2.50%) |    1 of 40 (  2.50%) |  183
 3 |   IXSCAN (REVERSE) TBL_IDX | 40 of 50000 (   .08%) | 1 of 50000 (   .00%) |   13

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

Question 3 — Column Order

Beispiel C.16.

CREATE INDEX tbl_idx ON tbl (a, b);
SELECT id, a, b
  FROM tbl
 WHERE a = 123
   AND b = 1;
SELECT id, a, b
  FROM tbl
 WHERE b = 1;
DROP INDEX tbl_idx;

Die Abfrage liest die gesamte Tabelle (TBSCAN).

Explain Plan
--------------------------------------------------------------------------
ID | Operation        |                 Rows |           ActualRows | Cost
 1 | RETURN           |                      |     3 of 4 ( 75.00%) |   13
 2 |  FETCH TBL       |     4 of 4 (100.00%) |     3 of 4 ( 75.00%) |   13
 3 |   IXSCAN TBL_IDX | 4 of 50000 (   .01%) | 3 of 50000 (   .01%) |    6

Predicate Information 
 3 - START (Q1.A = +00123.)
     START (Q1.B = +00001.)
      STOP (Q1.A = +00123.)
      STOP (Q1.B = +00001.)
Explain Plan
---------------------------------------------------------------------------
ID | Operation   |                    Rows |              ActualRows | Cost
 1 | RETURN      |                         |  4965 of 4575 (108.52%) |  611
 2 |  TBSCAN TBL | 4575 of 50000 (  9.15%) | 4965 of 50000 (  9.93%) |  611

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

Durch ändern der Spaltenreihenfolge können beide Abfragen den Index nuzten:

Explain Plan
--------------------------------------------------------------------------
ID | Operation        |                 Rows |           ActualRows | Cost
 1 | RETURN           |                      |     3 of 4 ( 75.00%) |   13
 2 |  FETCH TBL       |     4 of 4 (100.00%) |     3 of 4 ( 75.00%) |   13
 3 |   IXSCAN TBL_IDX | 4 of 50000 (   .01%) | 3 of 50000 (   .01%) |    6

Predicate Information 
 3 - START (Q1.A = +00123.)
     START (Q1.B = +00001.)
      STOP (Q1.A = +00123.)
      STOP (Q1.B = +00001.)
Explain Plan
----------------------------------------------------------------------------------
ID | Operation          |                    Rows |              ActualRows | Cost
 1 | RETURN             |                         |  4965 of 4965 (100.00%) |  519
 2 |  FETCH TBL         |  4965 of 4965 (100.00%) |  4965 of 4965 (100.00%) |  519
 3 |   RIDSCN           |  4965 of 4965 (100.00%) |  4965 of 4965 (100.00%) |   52
 4 |    SORT (UNIQUE)   |  4965 of 4965 (100.00%) |  4965 of 4965 (100.00%) |   52
 5 |     IXSCAN TBL_IDX | 4965 of 50000 (  9.93%) | 4965 of 50000 (  9.93%) |   50

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

Question 4 — LIKE

Beispiel C.17.

CREATE INDEX tbl_idx ON tbl (text);
SELECT id, text
  FROM tbl
 WHERE text LIKE '%TERM%';
DROP INDEX tbl_idx;

Die Abfrage liest die gesamte Tabelle (TBSCAN).

Explain Plan
------------------------------------------------------------------------
ID | Operation   |                    Rows |           ActualRows | Cost
 1 | RETURN      |                         |  0 of 1629 (   .00%) |  615
 2 |  TBSCAN TBL | 1629 of 50000 (  3.26%) | 0 of 50000 (   .00%) |  615

Predicate Information
 2 - SARG (Q1.TEXT LIKE '%TERM%')

Question 5 — Range Condition

Der Unterschied ist im Ausführungsplan schwer zu erkennen – auf den ersten Blick sind beide gut:

Beispiel C.18.

CREATE INDEX tbl_idx ON tbl (date_column, state);
SELECT id, date_column, state
  FROM tbl
 WHERE date_column >= current_date -  5 YEARS
   AND state = 'X';
DROP INDEX tbl_idx;
Explain Plan
--------------------------------------------------------------------------------
ID | Operation          |                   Rows |             ActualRows | Cost
 1 | RETURN             |                        |   365 of 371 ( 98.38%) |   97
 2 |  FETCH TBL         |   371 of 371 (100.00%) |   365 of 371 ( 98.38%) |   97
 3 |   RIDSCN           |   371 of 371 (100.00%) |   365 of 371 ( 98.38%) |   68
 4 |    SORT (UNIQUE)   |   371 of 371 (100.00%) |   365 of 371 ( 98.38%) |   68
 5 |     IXSCAN TBL_IDX | 371 of 50000 (   .74%) | 365 of 50000 (   .73%) |   68

Predicate Information
 2 - SARG (Q1.STATE = 'X')
     SARG ((CURRENT DATE - 5 YEARS) /* SPREG_EXPR VALUE='10/13/2010'*/ <= Q1.DATE_COLUMN)
 5 - START ((CURRENT DATE - 5 YEARS) /* SPREG_EXPR VALUE='10/13/2010'*/ <= Q1.DATE_COLUMN)
     START (Q1.STATE = 'X')
      SARG (Q1.STATE = 'X')

Der wichtige Unterschied ist, dass der erste Ausführungsplan bei Operation 5 (IXSAN) ein Filterprädikat verwendet (SARG). Der nächste Ausführungsplan hat jedoch nur START und STOP-Bedinungen bei dieser Operation:

Beispiel C.19.

CREATE INDEX tbl_idx ON tbl (state, date_column);
SELECT id, date_column, state
  FROM tbl
 WHERE date_column >= current_date -  5 YEARS
   AND state = 'X';
DROP INDEX tbl_idx;
Explain Plan
--------------------------------------------------------------------------------
ID | Operation          |                   Rows |             ActualRows | Cost
 1 | RETURN             |                        |   365 of 360 (101.39%) |   47
 2 |  FETCH TBL         |   360 of 360 (100.00%) |   365 of 360 (101.39%) |   47
 3 |   RIDSCN           |   360 of 360 (100.00%) |   365 of 360 (101.39%) |   19
 4 |    SORT (UNIQUE)   |   360 of 360 (100.00%) |   365 of 360 (101.39%) |   19
 5 |     IXSCAN TBL_IDX | 360 of 50000 (   .72%) | 365 of 50000 (   .73%) |   19

Predicate Information
 2 - SARG (Q1.STATE = 'X')
     SARG ((CURRENT DATE - 5 YEARS) /* SPREG_EXPR VALUE='10/13/2010'*/ <= Q1.DATE_COLUMN)
 5 - START (Q1.STATE = 'X')
     START ((CURRENT DATE - 5 YEARS) /* SPREG_EXPR VALUE='10/13/2010'*/ <= Q1.DATE_COLUMN)
      STOP (Q1.STATE = 'X')

Question 5 — Index Only Scan

Beispiel C.20.

CREATE INDEX tbl_idx ON tbl (a, date_column);
EXPLAIN PLAN FOR
 SELECT date_column, count(*)
   FROM tbl
  WHERE a = 123
  GROUP BY date_column;
EXPLAIN PLAN FOR
 SELECT date_column, count(*)
   FROM tbl
  WHERE a = 123
    AND b = 1
  GROUP BY date_column;
DROP INDEX tbl_idx;

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). 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             |                       |   13
 2 |  GRPBY (COMPLETE)  |    40 of 40 (100.00%) |   13
 3 |   IXSCAN TBL_IDX   | 40 of 50000 (   .08%) |   13

Predicate Information
 3 - START (Q1.A = +00123.)
      STOP (Q1.A = +00123.)
Explain Plan
---------------------------------------------------------
ID | Operation             |                  Rows | Cost
 1 | RETURN                |                       |   20
 2 |  GRPBY (COMPLETE)     |      4 of 4 (100.00%) |   20
 3 |   TBSCAN              |      4 of 4 (100.00%) |   20
 4 |    SORT               |      4 of 4 (100.00%) |   20
 5 |     FETCH TBL         |     4 of 40 ( 10.00%) |   20
 6 |      RIDSCN           |    40 of 40 (100.00%) |   13
 7 |       SORT (UNIQUE)   |    40 of 40 (100.00%) |   13
 8 |        IXSCAN TBL_IDX | 40 of 50000 (   .08%) |   13

Predicate Information
 5 - SARG (Q1.B = +00001.)
     SARG (Q1.A = +00123.)
 8 - START (Q1.A = +00123.)
      STOP (Q1.A = +00123.)

Über den Autor

Photo of Markus Winand
Markus Winand stimmt Entwickler auf SQL-Performance ein. Er hat das Buch SQL Performance Explained veröffentlicht und bietet inhouse Schulungen sowie Tuning-Leistungen auf http://winand.at/ an.