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 execution plans shown are abbreviated for better readability.

Table Setup

CREATE TABLE tbl (
  id          NUMERIC NOT NULL,
  date_column DATE,
  a           NUMERIC,
  b           NUMERIC,
  text        VARCHAR(255),
  state       CHAR(1),
  PRIMARY KEY (id)
);

 INSERT INTO tbl
 SELECT level
      , SYSDATE - level
      , MOD(level,1234)
      , TRUNC(DBMS_RANDOM.VALUE(1, 10))
      , DBMS_RANDOM.STRING('l', 20)
      , DECODE(MOD(level, 5), 'X', 'A')
   FROM dual
CONNECT BY level <= 50000;

BEGIN DBMS_STATS.GATHER_TABLE_STATS(user, 'TBL'); END;;

Question 1 — DATE Anti-Pattern

CREATE INDEX tbl_idx ON tbl (date_column);
SELECT COUNT(*)
  FROM tbl
 WHERE TO_CHAR(date_column, 'YYYY') = '2016';
SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= DATE'2016-01-01'
   AND date_column <  DATE'2017-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 |     361 |
|  1 |  SORT AGGREGATE    |      |      1 |     361 |
|* 2 |   TABLE ACCESS FULL| TBL  |    366 |     361 |
-----------------------------------------------------

-------------------------------------------------------
| Id | Operation         | Name    | A-Rows | Buffers |
-------------------------------------------------------
|  0 | SELECT STATEMENT  |         |      1 |       3 |
|  1 |  SORT AGGREGATE   |         |      1 |       3 |
|* 2 |   INDEX RANGE SCAN| TBL_IDX |    366 |       3 |
-------------------------------------------------------

Question 2 — Indexed Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column);
SELECT *
  FROM (
        SELECT id, date_column
          FROM tbl
         WHERE a = 123
         ORDER BY date_column DESC
       )
 WHERE rownum <= 1;

The query uses the index (INDEX RANGE SCAN) and fetches in reverse order (DECENDING). Note that there is no sort operation.

--------------------------------------------------------------
|Id | Operation                      | Name    | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT               |         |    1 |    4 |
| 1 |  COUNT STOPKEY                 |         |      |      |
| 2 |   VIEW                         |         |    2 |    4 |
| 3 |    TABLE ACCESS BY INDEX ROWID | TBL     |   41 |    4 |
| 4 |     INDEX RANGE SCAN DESCENDING| TBL_IDX |    2 |    2 |
--------------------------------------------------------------

Question 3 — Column Order

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

The second query reads the entire table (TABLE ACCESS FULL). Changing the column order in the index allows both queries to use an INDEX RANGE SCAN.

-------------------------------------------------------------
| Id | Operation                   | Name    | Rows  | Cost |
-------------------------------------------------------------
|  0 | SELECT STATEMENT            |         |     5 |    6 |
|  1 |  TABLE ACCESS BY INDEX ROWID| TBL     |     5 |    6 |
|* 2 |   INDEX RANGE SCAN          | TBL_IDX |     5 |    1 |
-------------------------------------------------------------


------------------------------------------------
| Id | Operation         | Name | Rows  | Cost |
------------------------------------------------
|  0 | SELECT STATEMENT  |      |  5556 |  103 |
|* 1 |  TABLE ACCESS FULL| TBL  |  5556 |  103 |
------------------------------------------------

Question 4 — LIKE

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

The query reads the entire table (TABLE ACCESS FULL).

------------------------------------------------
| Id | Operation         | Name | Rows  | Cost |
------------------------------------------------
|  0 | SELECT STATEMENT  |      |  2500 |  103 |
|* 1 |  TABLE ACCESS FULL| TBL  |  2500 |  103 |
------------------------------------------------

Question 5 — Index Only Scan

CREATE INDEX tbl_idx ON tbl (a, date_column);
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 123
 GROUP BY date_column;
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 123
   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.

------------------------------------------------------
| Id | Operation            | Name    | Rows  | Cost |
------------------------------------------------------
|  0 | SELECT STATEMENT     |         |    41 |    2 |
|  1 |  SORT GROUP BY NOSORT|         |    41 |    2 |
|* 2 |   INDEX RANGE SCAN   | TBL_IDX |    41 |    2 |
------------------------------------------------------

--------------------------------------------------------------
| Id | Operation                    | Name    | Rows  | Cost |
--------------------------------------------------------------
|  0 | SELECT STATEMENT             |         |     5 |   43 |
|  1 |  SORT GROUP BY NOSORT        |         |     5 |   43 |
|* 2 |   TABLE ACCESS BY INDEX ROWID| TBL     |     5 |   43 |
|* 3 |    INDEX RANGE SCAN          | TBL_IDX |    41 |    2 |
--------------------------------------------------------------

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

…to answer your current SQL questions.

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