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) = 2024;SELECT COUNT(*)
FROM tbl
WHERE date_column >= DATE'2024-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)Lerne mehr darüber
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%')Lerne mehr darüber
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_columnSELECT date_column, count(*)
FROM tbl
WHERE a = 38
AND b = 1
GROUP BY date_columnDie 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.
