Die Kosten eines Index (Oracle) beim Schreiben

Abstract

Oracle Performance Tuning Guide sagt:

  • jeder zusätzliche Index verteuert die INSERT-Operation im Schnitt um den Faktor 3, im Vergleich zu einer INSERT-Operation ohne jegliche Index Beteiligung.
  • bei Spalten deren Werte eine hohe Kardinalität und damit große „selectibility“ besitzen eignet sich ein B*-Baum Index vorzugsweise vor einem Bitmap-Index
  • bei Spalten deren Werte eine geringe Kardinalität und damit geringe „selectibility“ besitzen eignet sich ein Bitmap-Index vorzugweise vor einem B*-Baum-Index
  • Index Organized Tables (IOT) sind schneller, da sie beim Lesen und Schreiben weniger physische I/O benötigen (man muss nicht in einen Index und dann nochmal in ein anderes physisches Segment den Datensatz schreiben)

In diesem Artikel untersuche ich wie sich die unterschiedlichen Index-Typen bei INSERT-Operationen praktisch bemerkbar machen.

Die verwendete Hardware und Software

Getestet wird auf einem Laptop von Lenovo T520 mit Windows 10 aus dem Jahre 2015.

Windows Windows 10 Home (64-Bit-Betriebssystem)
Oracle Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
CPU Intel(R) Core(TM) i7-5600U @2.60Ghz
Speicher 12GB (DDR3 1600 MHz 4GB + DDR 1600 MHz 8GB) Hynix/Hyndai
HDD SSD / SAMSUNG MZ7LN256HCHP-000L7 – 238.47 GBs

Die Testumgebung ist kein Server-System dennoch werden alle Tests auf der gleichen Maschine ausgeführt. Folgende Aussagen kann man daher nicht treffen, z.B. „für 1.000.000 INSERTs braucht eine jede Oracle Datenbank 2 Sekunden“, denn die Geschwindigkeit ist hier von vielen weiteren Faktoren abhängig wie z.B. CPU, I/O-Geschwindigkeit, Filesystem-Blocksize um nur ein paar zu nennen. Es lassen sich aber vergleichende Aussagen über unterschiedlichen Konstellationen des gleichen Systems treffen, wie z.B. „1.000.000 INSERTs in eine Tabelle mit PK-Spalte dauert doppelt so lange wie 1.000.000 INSERTs (gleichen Daten) in eine Tabelle ohne PK-Spalte“. Verhältnisse können abgeleitet werden.

Aus einer Black-Box-Sicht auf die Oracle Datenbank unterstellt diese Betrachtung dann, dass ähnliche Verhältnisse auch auf anderen Systemen zu beobachten sind.

Der Testablauf

Es werden 10 strukturell kongruente Tabellen erstellt (gleiche Spalten, gleiche Typen). Jede Tabelle erhält andere Indizies und Primary Keys oder ist als Index-Organized-Table realisiert.

Per Java und JDBC werden in einer Schleife  1.000.000 INSERTs auf jede Tabelle abgesetzt und die Zeit vom ersten bis zum letzen INSERT je Tabelle gemessen. Die INSERTs erfolgen im Batch-Modus mit Batch Size 1.000. So ergeben sich 10 Einzeltests, die zusammen das Test-Set bilden. Das Ergebnis jedes Einzeltest (Dauer in Millisekunden) wird erfasst.

Dieses Test-Set wird mehrere Male wiederholt. Es wird dann das arithmetische Mittel zu jedem Test über alles Test-Sets ermittelt.

Die Tabellen

Tabelle Spalte Type PK Index-Name Indextyp Bemerkung
TAB0 ID decimal(10) Kein Primärschlüssel, kein Index
TAB0 A varchar(5)
TAB0 B varchar(5)
TAB0 C varchar(5)
TAB0 D decimal(10)
TAB1 ID decimal(10) PK1 SYS_UNIQUE_1 B-Baum 1 Primärschlüssel und obligatorischer Index
TAB1 A varchar(5)
TAB1 B varchar(5)
TAB1 C varchar(5)
TAB1 D decimal(10)
TAB2 ID decimal(10) PK1 SYS_UNIQUE_2 B-Baum 1 Primärschlüssel und Obligatorischer Index + 1 zusätzliches Index über 1 Spalten
TAB2 A varchar(5) IDX_TAB2 B-Baum
TAB2 B varchar(5)
TAB2 C varchar(5)
TAB2 D decimal(10)
TAB3 ID decimal(10) PK1 SYS_UNIQUE_3 B-Baum 1 Primärschlüssel und Obligatorischer Index + 1 zusätzliches Index über 2 Spalten
TAB3 A varchar(5) IDX_TAB3 B-Baum
TAB3 B varchar(5) IDX_TAB3 B-Baum
TAB3 C varchar(5)
TAB3 D decimal(10)
TAB4 ID decimal(10) PK1 SYS_UNIQUE_4 B-Baum 1 Primärschlüssel und Obligatorischer Index + 1 zusätzliches Index über 3 Spalten
TAB4 A varchar(5) IDX_TAB4 B-Baum
TAB4 B varchar(5) IDX_TAB4 B-Baum
TAB4 C varchar(5) IDX_TAB4 B-Baum
TAB4 D decimal(10)
TAB5_IOT ID decimal(10) PK1 Indexorganized Table
TAB5_IOT A varchar(5)
TAB5_IOT B varchar(5)
TAB5_IOT C varchar(5)
TAB5_IOT D decimal(10)
TAB6 ID decimal(10) PK1 SYS_UNIQUE_5 B-Baum 1 Primärschlüssel und Obligatorischer Index + 1 zusätzliches Index über 1 Spalten
TAB6 A varchar(5) IDX_TAB6 Bitmap
TAB6 B varchar(5)
TAB6 C varchar(5)
TAB6 D decimal(10)
TAB7 ID decimal(10) PK1 SYS_UNIQUE_6 B-Baum 1 Primärschlüssel und Obligatorischer Index + 1 zusätzliches Index über 2 Spalten
TAB7 A varchar(5) IDX_TAB7 Bitmap
TAB7 B varchar(5) IDX_TAB7 Bitmap
TAB7 C varchar(5)
TAB7 D decimal(10)
TAB8 ID decimal(10) PK1 SYS_UNIQUE_7 Bitmap 1 Primärschlüssel und Obligatorischer Index + 1 zusätzliches Index über 3 Spalten
TAB8 A varchar(5) IDX_TAB8 Bitmap
TAB8 B varchar(5) IDX_TAB8 Bitmap
TAB8 C varchar(5) IDX_TAB8 Bitmap
TAB8 D decimal(10)

Das Programm und die Daten

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

import oracle.jdbc.driver.OracleDriver;

public class DataPump {

	private static final int BATCH_SIZE = 1000;

	public static void main(String[] args) {

		OracleDriver driver = new OracleDriver();
		Properties prop = new Properties();
		prop.put("user", "yourUserHere");
		prop.put("password", "yourPasswordHere");

		testIndizies(driver, prop);

	}

	private static void testIndizies(OracleDriver driver, Properties prop) {

		PreparedStatement[] ps = new PreparedStatement[10];
		Connection connect = null;
		try {

			connect = driver.connect("jdbc:oracle:thin:@//localhost:1521/orcl", prop);
			connect.setAutoCommit(false);

			ps[0] = connect.prepareStatement("INSERT INTO TAB0 (ID, A, B, C, D) VALUES (?, ?, ?, ?, ?)");
			ps[1] = connect.prepareStatement("INSERT INTO TAB1 (ID, A, B, C, D) VALUES (?, ?, ?, ?, ?)");
			ps[2] = connect.prepareStatement("INSERT INTO TAB2 (ID, A, B, C, D) VALUES (?, ?, ?, ?, ?)");
			ps[3] = connect.prepareStatement("INSERT INTO TAB3 (ID, A, B, C, D) VALUES (?, ?, ?, ?, ?)");
			ps[4] = connect.prepareStatement("INSERT INTO TAB4 (ID, A, B, C, D) VALUES (?, ?, ?, ?, ?)");
			ps[5] = connect.prepareStatement("INSERT INTO TAB5_IOT (ID, A, B, C, D) VALUES (?, ?, ?, ?, ?)");
			ps[6] = connect.prepareStatement("INSERT INTO TAB6 (ID, A, B, C, D) VALUES (?, ?, ?, ?, ?)");
			ps[7] = connect.prepareStatement("INSERT INTO TAB7 (ID, A, B, C, D) VALUES (?, ?, ?, ?, ?)");
			ps[8] = connect.prepareStatement("INSERT INTO TAB8 (ID, A, B, C, D) VALUES (?, ?, ?, ?, ?)");

			performTest(ps);

		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private static void performTest(PreparedStatement[] ps) throws SQLException {

		for (int i = 0; i < ps.length; i++) {
			populateTable(ps[i]);
		}

	}

	private static void populateTable(PreparedStatement ps) throws SQLException {

		long tsStart = System.currentTimeMillis();

		for (int i = 1; i <= 1000000; i++) {
			ps.setInt(1, i);
			StringBuilder sb = new StringBuilder(from(i, "")).reverse();
			ps.setString(2, sb.toString());
			ps.setString(3, sb.toString());
			ps.setString(4, sb.toString());
			ps.setInt(5, i);
			ps.addBatch();
			if (i % BATCH_SIZE == 0) {
				ps.executeBatch();
				ps.clearBatch();
			}
		}
		ps.getConnection().commit();

		long tsEnd = System.currentTimeMillis();
		long elapsed = tsEnd - tsStart;

		System.out.println("Elapsed: " + elapsed + " ms");
	}

	/**
	 * Will create a new String of the REGEXP-format /(A-Z){1,5}/ based on the parameter i.
	 * This string is free of collisions between 0 and 7 million for the int parameter.
	 * So the returned string is highly selectable in oracle's term.
	 * 
	 * @param i
	 * @param in
	 * @return
	 */
	private static String from(int i, String in) {
		int div = i / 26;
		int rest = i % 26;
		String next = Character.toString((char) (65 + rest));
		if (div == 0) {
			return next + in;
		} else {
			return from(div, next + in);
		}
	}
}

Die Ergebnisse

Run1:
Elapsed: 3104
Elapsed: 6578
Elapsed: 26823
Elapsed: 30760
Elapsed: 30722
Elapsed: 10197

Run2:
Elapsed: 2004 ms
Elapsed: 5612 ms
Elapsed: 27410 ms
Elapsed: 30798 ms
Elapsed: 30758 ms
Elapsed: 9070 ms

Run3:
Elapsed: 1981 ms
Elapsed: 2964 ms
Elapsed: 27398 ms
Elapsed: 30920 ms
Elapsed: 30793 ms
Elapsed: 8892 ms
Elapsed: 55593 ms
Elapsed: 60551 ms
Elapsed: 62323 ms

Index B-Baum Bitmap IOT
3162
PK 6694
PK, (A) 27392 55427
PK, (A,B) 30670 60851
PK, (A,B,C) 30537 61800
9054

Folgende Aussagen lassen sich treffen:

  1. Ohne Index brauchen 1 Million INSERTs ca. 2,5 Sekunden
  2. Mit einem Unique-Index (Primary Key) bauchen 1 Million INSERTs ca. 5,5 Sekunden
  3. Mit zusätzlichem Index über eine Spalte:
    1. B*-Baum -> 27 Sekunden
    2. Bitmap -> 55 Sekunden
  4. Mit zusätzlichem Index über zwei Spalten:
    1. B*-Baum -> 30 Sekunden
    2. Bitmap -> 60 Sekunden
  5. Mit zusätzlichem Index über drei Spalten:
    1. B*-Baum -> 30 Sekunden
    2. Bitmap -> 60 Sekunden
  6. Eine Index Organized Table braucht für die Aufnahme von 1 Millionen INSERTs ca. 9 Sekunden.

Auswertung

  1. Ohne Index geht es am schnellsten, da die Daten einfach in die Segmente geschrieben werden können, ohne das sie nochmal gegen andere Daten verglichen werden müssen.
  2. Mit einem Unique Index erhöht sich der zeitliche Aufwand um den Faktor 2! Das ist erwartet worden, da das ausbalanzieren eines B*Baumes sehr viel Zeit in Anspruch nimmt, wenn die Index-Werte monoton steigend eingeliefert werden.

Folgende Vergleiche finden gegen die Variante mit einem Primärschlüssel statt, da dieses Modell der Realität am nächsten ist.

  1. Ein zusätzlicher B*-Baum-Index erhöht hier den Aufwand um den Faktor 5!
  2. Ein zusätzlicher Bitmap-Index erhöht hier den Aufwand um den Faktor 10!
  3. Index Organized Tables erhöhen den Gesamtaufwand im Vergleich zur einfachen Tabelle mit einem PK um den Faktor 1,5!

Fazit

Es macht sehr viel Sinn über den Einsatz von Index-Organized-Tables nachzudenken, da der Aufwand gering und der Nutzen sehr hoch ist. Allerdings ist die Spaltenreihenfolge, wie bei den anderen Indizies auch – von entscheidender Bedeutung. Wenn die Reihenfolge nicht dem Read-Use-Case entspricht, ist auch die IOT Sinn-befreit.

Aus Performance – Sicht scheint es irrelevant zu sein wie viele Spalten zu einem Index gehören, denn die Performance Einbußen sind marginal mit jeder zusätzlichen Spalten und daher vernachlässigbar.

Anders verhält es sich bei der Anzahl der Indizies. Jeder zusätzlich eigenständiger Index erhöht den Aufwand. Oracle spricht von Faktor 3. Hier im Test konnten die Faktoren 5 und 10 nachgewiesen werden.

Man muss allerdings sagen, dass in diesem Test ein Index auf varchar(5) definiert wurde, mit distinkten Daten. Das entspricht nicht den Idealanforderungen für einen Bitmap-Index – ganz im Gegenteil, der seine Stärken bei vielen gleichen Daten haben soll. Der B*Baum Index hat bei distinkten varchar(5) Werten seine größten Vorteile! Ferner ist zu erwarten, das Indizies, die über numerische Felder erstellt werden schneller sind, als über varchar-Felder. Somit hat dieser Test die ungünstigere Variante für zu indizierende Felder gewählt.

Es bleibt die Regel: wer Schreib-Performance will, sollte möglichst keine Indizies auf der Tabelle haben.