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
CREATE INDEX tbl_idx ON tbl (date_column);SELECT COUNT(*)
FROM tbl
WHERE EXTRACT(YEAR FROM date_column) = 2017;SELECT COUNT(*)
FROM tbl
WHERE date_column >= DATE'2017-01-01'
AND date_column < DATE'2018-01-01';The first execution plan performs a full table scan (TABLE ACCESS FULL). The second execution plan, on the other hand, performs an INDEX RANGE SCAN.
-----------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 |
| 1 | SORT AGGREGATE | | 1 | 7 |
|* 2 | TABLE ACCESS FULL| TBL | 271 | 7 |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(EXTRACT(YEAR FROM DATE_COLUMN)=2017)-------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 |
|* 2 | INDEX RANGE SCAN| TBL_IDX | 271 | 1 |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(DATE_COLUMN>=TO_DATE('2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND DATE_COLUMN< TO_DATE('2018-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))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 (INDEX RANGE SCAN) and fetches in reverse order (DECENDING). The text NOSORT STOPKEY means that there is no sort operation needed and that the execution is aborted once the condition (predicate 2) is not met anymore.
--------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 |
|* 1 | VIEW | | 1 | 4 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 4 |
| 3 | TABLE ACCESS BY INDEX ROWID | TBL | 2 | 4 |
|* 4 | INDEX RANGE SCAN DESCENDING| TBL_IDX | 2 | 2 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1)
2 - filter(ROW_NUMBER() OVER ( ORDER BY DATE_COLUMN DESC )<=1)
4 - access("A"=12)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 execution plan of the first query is fine with both indexes:
-------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL | 1 | 3 |
|* 2 | INDEX RANGE SCAN | TBL_IDX | 1 | 2 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"=38 AND "B"=1)-------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL | 1 | 3 |
|* 2 | INDEX RANGE SCAN | TBL_IDX | 1 | 2 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=1 AND "A"=38)Both indexes can be used with an INDEX RANGE SCAN and without any filter predicates.
The second query performs an INDEX SKIP SCAN. From the efficiency perspective, this is better than an INDEX FULL SCAN but still worse than an INDEX RANGE SCAN.
-------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL | 2 | 4 |
|* 2 | INDEX SKIP SCAN | TBL_IDX | 2 | 2 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=1)
filter("B"=1)The second query can use the index on (b, a) with an INDEX RANGE SCAN, however.
-------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL | 2 | 4 |
|* 2 | INDEX RANGE SCAN | TBL_IDX | 2 | 2 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=1)Reversing the column order in the index doesn’t affect the first query, yet it improves the performance of the second query.
Learn More
Question 4 — LIKE
CREATE INDEX tbl_idx ON tbl (text);SELECT *
FROM tbl
WHERE text LIKE 'TJ%';The execution plan clearly states that it is doing an index range scan. Since the only wild card character is at the very end, the full search term 'TJ' can be used as index access predicate.
-------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL | 1 | 4 |
|* 2 | INDEX RANGE SCAN | TBL_IDX | 1 | 3 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TEXT" LIKE 'TJ%')
filter("TEXT" LIKE 'TJ%')Learn More
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 queries use the index, of course. The difference is that the first query doesn’t access the table, so the first query is much faster.
----------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 2 |
| 1 | SORT GROUP BY NOSORT| | 3 | 2 |
|* 2 | INDEX RANGE SCAN | TBL_IDX | 3 | 2 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"=38)------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 |
| 1 | SORT GROUP BY NOSORT | | 1 | 3 |
|* 2 | TABLE ACCESS BY INDEX ROWID| TBL | 1 | 3 |
|* 3 | INDEX RANGE SCAN | TBL_IDX | 3 | 1 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"=1)
3 - access("A"=38)The database could, theoretically, use a FAST FULL INDEX SCAN for the first query, if selecting a large fraction of the table. The second query, using INDEX RANGE SCAN and TABLE ACCESS BY INDEX ROWID could be faster in that case. However, this case doesn’t apply here because the first query selects a small fraction from the table.
The other border case, if the first query doesn’t return any rows, means that the second query would be as fast as the first.
Besides these border cases, the second query must be considerable 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.
