von Markus Winand.

Oracle Skripts 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 create und insert Anweisungen findest du in diesem Download.

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

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

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

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

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

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

Frage 3 — Spaltenreihenfolge

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

Die erste Abfrage kann beide Indizes optimal nutzen:

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

Die zweite Abfrage kann den ersten Index nicht optimal nutzen. Die Datenbank führt stattdessen einen INDEX SKIP SCAN durch. Aus Sicht der Effizienz ist das besser als ein INDEX FULL SCAN aber nicht so gut wie ein optimaler 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)

Dreht man die Spaltenreihenefolge im Index um, kann auch die zweite Abfrage den Index optimal nutzen:

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

Frage 4 — LIKE

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

Der Ausführungsplan zeigt klar, dass ein Index-Range-Scan durchgeführt wird. Da der Suchbegriff 'TERM%' nur ganz am ende ein Wildcard-Zeichen hat, kann der komplette Suchbegriff als Index-Zugriffsprädikat genutzt werden.

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

Frage 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

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

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.

Sein Buch bei Amazon kaufen

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