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 Anti-Pattern
--#SET TERMINATOR ;
CREATE INDEX tbl_idx ON tbl (date_column);
--#SET TERMINATOR ;
SELECT COUNT(*)
FROM tbl
WHERE TO_CHAR(date_column, 'YYYY') = 2024;
--#SET TERMINATOR ;
SELECT COUNT(*)
FROM tbl
WHERE date_column >= DATE'2024-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
--#SET TERMINATOR ;
CREATE INDEX tbl_idx ON tbl (a, date_column);
--#SET TERMINATOR ;
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
--#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;
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
--#SET TERMINATOR ;
CREATE INDEX tbl_idx ON tbl (text);
--#SET TERMINATOR ;
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.)