by Markus Winand.

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

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

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.

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