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;

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

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

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

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

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

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;

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

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

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

DROP INDEX tbl_idx;

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

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;

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

Photo of Markus Winand
Markus Winand stimmt Entwickler auf SQL-Performance ein. Er hat das Buch SQL Performance Explained veröffentlicht und bietet inhouse Schulungen sowie Tuning-Leistungen auf http://winand.at/ an.