von Markus Winand.

Oracle Skripte für den „3-Minuten Test“


Dieser Abschnitt enthält die create, insert und select Kommandos für den „3-Minuten Test“. Mach den Test doch selbst, bevor du weiter liest.

Die Ausführungspläne sind zur besseren Lesbarkeit abgekürzt.

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

Frage 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';

Der erste Ausführungsplan liest die ganze Tabelle (TABLE ACCESS FULL). Der zweite Ausführungsplan verwendet den Index (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 |
-------------------------------------------------------

Frage 2 — Indiziertes 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;

Die Abfrage verwendet den Index (INDEX RANGE SCAN) in absteigender Reihenfolge (DESCENDING). Beachte, dass keine Sortieroperation aufscheint.

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

Frage 3 — Spaltenreihenfolge

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

Die zweite Abfrage liest die ganze Tabelle (TABLE ACCESS FULL). Wenn die Spalten im Index umgedreht werden, können beide Abfragen optimal vom Index profitieren (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 |
------------------------------------------------

Frage 4 — LIKE

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

Die Abfrage liest die ganze Tabelle (TABLE ACCESS FULL).

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

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

Die erste Abfrage nutzt den Index, um auf der Spalte A zu suchen, kann aber auch die selektierte Spalte DATE_COLUMN aus dem Index lesen. Die zweite Abfrage muss zusätzlich in die Tabelle sehen (TABLE ACCESS BY INDEX ROWID), um den Filter auf der Spalte B zu prüfen. Obwohl dieser Zugriff das Ergebnis schmälert, wird die Abfrage viel langsamer.

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

Theoretisch könnte Oracle bei der ersten Abfrage auch einen FAST FULL INDEX SCAN verwenden, wenn ein großer Teil der Tabelle abgefragt wird. In diesem Fall wäre es denkbar, dass die zweite Abfrage mit einem INDEX RANGE SCAN und TABLE ACCESS BY INDEX ROWID schneller ist. Dies ist in diesem Fall jedoch dadurch ausgeschlossen, da die erste Abfrage bereits einen verhältnismäßig kleinen Teil der Tabelle abfragt.

Der andere Grenzfall, dass bereits die erste Abfrage kein Ergebnis liefert, würde dazu führen, dass beide Abfragen gleich schnell sind.

Zwischen diesen beiden Extremfällen muss die zweite Abfrage immer deutlich langsamer sein als die Erste, da für jede Zeile auf die Tabelle zugegriffen werden muss. Das trifft insbesondere auch jene Zeilen, die aufgrund des hinzugefügten Filters aus dem Ergebnis fallen. Selbst wenn der Index einen niedrigen Clustering Faktor hat, bedeutet das zumindest eine Verdopplung der gelesenen Datenmenge.

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Kaufen Sie sein Buch bei Amazon

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Holen Sie sich Markus

…für ein Training ins Büro.

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz