by Markus Winand.

DB2 Example Scripts for “3-Minute Test”


This section contains the create, insert and select statements for the “Test your SQL Know-How in 3 Minutes” test. You may want to test yourself before reading this page.

The create and insert statements are available in the example schema archive.

The execution plans shown are abbreviated for better readability.

Question 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'

The first query performs a full index scan (IXSCAN with no START/STOP (but SARG) in the Predicate Information)

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')

The second query can do an index range scan (START/STOP at the IXSCAN operation).

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')

Note that DB2 LUW properly optimize extract expressions:

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')

Question 2 — Indexed Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column)
SELECT *
  FROM tbl
 WHERE a = 12
 ORDER BY date_column DESC
 FETCH FIRST 1 ROW ONLY

The query uses the index (IXSCAN) and fetches in reverse order (REVERSE). Note that there is no sort operation.

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.)

Question 3 — Column Order

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

The first query can use both indexes efficiently (IXSCAN with START and STOP predicates):

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.)

The second query cannot use the first index—the query performs a full table scan (TBSCAN):

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.)

Reversing the column order in the index allows the second query to use the index efficiently too (IXSCAN with START and STOP predicates):

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.)

Question 4 — LIKE

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

The query can use the index efficiently (IXSCAN with START and STOP predicates):

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

Question 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

Both indexes are used, of course. The difference is that the first query doesn't access the table, so the first query is much faster.

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.)

The second query must be considerably slower because every row needs a table access — also for those that are filtered by the new condition. Even if the index has a low clustering factor, it is still about twice as many blocks to read.

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.)

About the Author

Photo of Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing. His book entitled SQL Performance Explained has become standard reading.

Buy his Book on Amazon

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy on Amazon
(paperback only)

Paperback and PDF also available at Markus’ store.

Hire Markus

The quick and easy way to benefit from his extensive knowledge and experience.
Learn more »

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy | CC-BY-NC-ND 3.0 license