2011-08-13Oracle Example Scripts for “3-Minuten 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;

EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TBL');

Question 1 — DATE Anti-Pattern

CREATE INDEX tbl_idx ON tbl (date_column);


EXPLAIN PLAN FOR
 SELECT text, date_column
   FROM tbl
  WHERE TO_CHAR(date_column, 'YYYY') = '2011';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR
 SELECT text, date_column
   FROM tbl
  WHERE date_column >= TO_DATE('2011-01-01', 'YYYY-MM-DD')
    AND date_column <  TO_DATE('2012-01-01', 'YYYY-MM-DD');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

DROP INDEX tbl_idx;

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 | Rows | Cost (%CPU)|
-----------------------------------------------------
|  0 | SELECT STATEMENT  |      |  500 |   103   (1)|
|* 1 |  TABLE ACCESS FULL| TBL  |  500 |   103   (1)|
-----------------------------------------------------

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

Question 2 — Indexed Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column);

EXPLAIN PLAN FOR
 SELECT *
   FROM (
         SELECT id, date_column
           FROM tbl
          WHERE a = :a
          ORDER BY date_column DESC
        )
  WHERE rownum <= 1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

DROP INDEX tbl_idx;

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

EXPLAIN PLAN FOR
 SELECT id, a, b
   FROM tbl
  WHERE a = :a
    AND b = :b;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR
 SELECT id, a, b
   FROM tbl
  WHERE b = :a;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

DROP INDEX tbl_idx;

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

EXPLAIN PLAN FOR
 SELECT id, text
   FROM tbl
  WHERE text LIKE '%TERM%';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

DROP INDEX tbl_idx;

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

EXPLAIN PLAN FOR
 SELECT date_column, count(*)
   FROM tbl
  WHERE a = :a
  GROUP BY date_column;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR
 SELECT date_column, count(*)
   FROM tbl
  WHERE a = :a
    AND b = :b
  GROUP BY date_column;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

DROP INDEX tbl_idx;

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.