Indizes

Ein Index ist eine Baumstruktur, die einen direkten Zugriff auf ein Datensatz einer Tabelle ermöglicht. Indizes solten immer in einem separaten Tablespace gespeichert werden, der auf einer separaten Platte liegen sollte.

Arten

Logisch

• Über eine Spalte oder über mehrere Spalten (Concatenated, max. 32)
• Unique oder Nonunique
• Auf Funktionen basiert (B-Tree oder Bitmap)
• Domain (Applikationsspezifisch) für Scalar-, Objekt- oder LOB-Datentypen, max.1 Spalte

Physikalisch

• Partitioniert oder Nonpartitioniert
  Verwendung für große Tabellen,
  speicherbar über mehrere Tablespaces,
  meist verwendet mit partitionierten Tabellen
• B-Tree
  • Normal oder Reverse Key
• Bitmap

Eingehender wird hier auf den B-Tree- und Bitmap-Index eingegangen.

B-Tree

• Günstig bei vielen unterschiedliche Werten (hohe Kardinalität), besonders bei UNIQUE.
• Sehr uneffizient bei SELECT-Abfragen mit OR.
• Günstig für OLTP.
• NOT NULL kann nicht indiziert werden.

Normal:

Der normale B-Tree wird am meisten verwendet und wird nachfolgend eingehender behandelt.

Reverse Key Index:

Beim Erzeugen eines Reverse Key Index wird der Wert in der Key-Spalte 'umgedreht'.

12345 --> 54321
12346 --> 64321
12347 --> 74321

Der Reverse Key Index wird sehr selten verwendet. Meist um bei Sequenzen mit kontinuierlich folgenden Werten ein Verteilen auf unterschiedliche Blöcke zu erreichen.

Beachte: Wenn Applikationen Wertebereiche festlegen, kann der Reverse Key Index nicht verwendet werden.

Bitmap

• Bei geringer Kardinalität (<10.000), also bei wenig unterschiedlichen Werten (z.B. männlich, weiblich).
• Ein Update einer Key-Spalte ist sehr aufwendig.
• Effizient bei SELECT-Abfragen mit OR.
• Günstig für Warehousing.
• Sinnvoll bei DSS
• NOT NULL kann indiziert werden.

Index erstellen

Empfehlungen

• Balance zwischen Abfragegeschwindigkeit und DML Bedürfnissen.
  Während die Abfragegeschwindigkeit sich erhöht, sinkt die Geschwindigkeit bei DML-Befehlen.
• Indizes sollten in separaten Tablespace und Festplatten gespeichert werden.
• Höhrere Geschwindigkeiten wird bei großen Indizes durch NOLOGGING (kein Redo) erreicht.
• Meist haben Indizes mehr Einträge pro Block als eine Tabelle.
  INITRANS sollte daher höher als die zugehörige Tabelle sein.

B-Tree

Normal

create index id_dual on dual (dummy)
  tablespace testus;

Der B-Tree ist der Default Index und braucht daher nicht angegeben zu werden.

CREATE INDEX scott.emp_lname_idx
  ON scott.employees(last_name)
  PCTFREE 30
  STORAGE(INITIAL 200K NEXT 200K
  PCTINCREASE 0
  MAXEXTENTS 50)
  TABLESPACE indx01
;

Reverse Key Index

CREATE UNIQUE INDEX scott.ord_ord_no_idx
  ON scott.ord(ord_no) REVERSE
  PCTFREE 30
  STORAGE(INITIAL 200K NEXT 200K
  PCTINCREASE 0
  MAXEXTENTS 50)
  TABLESPACE indx01
;

Erstellen eines Funktionsindex:

Ein Funktionsindex beinhaltet eine Funktion. Oft ist dies lower(), um Groß- und Kleinschreibung zu ignorieren.

Achtung: Query Rewrite muss vorher gegeben worden sein.

CREATE INDEX scott.funct_ind1
  ON scott.emp(lower(ename))
  PCTFREE 30
  STORAGE(INITIAL 200K NEXT 200K
  PCTINCREASE 0
  MAXEXTENTS 50)
  TABLESPACE indx01
;

Bitmap

Beispiel mit Storage Parametern.

create bitmap index id_dual
  on dual (dummy)
  pctfree 30 storage(initial 200k next 200k pctincrease 0 maxextents 50)
  tablespace testus
;

Diese Werte (initial, next, pctincrease, maxextents) werden in diesem Beispiel ignoriert, da der Tablespace testus als Local Managed definiert wurde.

Ändern der Storage Parameter eines Index

Die zu ändernden Parameter sind die gleichen wie bei Tabellen. Meist wird MAXEXTENTS erhöht.

alter index id_dual storage(next 400k maxextents 100);
FEHLER in Zeile 1:
ORA-25150: ÄNDERN von Extent-Parametern nicht zulässig

Dies geht nicht, da der Tablespace hier Local Managed ist.

Index verschieben

Dies ist nicht sehr zu empfehlen, da es sehr lange dauert.

alter index id_dual rebuild tablespace testus;

Index reorganisieren

REBUILD - Reorganisieren Online

REBUILD ONLINE dient zur staleness Korrektur von Index. REBUILD ONLINE ist sehr zeitaufwendig. Umfangreiche DML-Operationen sind dabei nicht zu empfehlen. DDL-Operationen werden für diese Zeit gesperrt. Restriktionen:

• Indizes von temporäre Tabellen können nicht mit REBUILD reorganisiert werden.
• Partitionierte Indizes müssen einzeln mit REBUILD reorganisiert werden.
• Man kann nicht ungenutzten Speicher freigeben.
• Man kann nicht den Wert von PCTFREE für den gesamten Index ändern.

alter index id_dual rebuild online;
FEHLER in Zeile 1:
ORA-08108: 
Dieser Index-Typ kann nicht online erstellt oder neu erstellt werden.

Zu klein?

COALESCE - Index-Zusammenführung

Um den Index neu zu organisieren kann bei B-Tree-Index COALESCE angewendet werden. Dabei können Abfragen von Usern, die den Index verwendet, ausgeführt werden. Wird laut Dozent kaum verwendet (ausser in der Prüfung). Besser ist hier Rebuild Online.

alter index id_dual coalesce;

Index logisch überprüfen

ANALYZE erzeugt die View index_stats, die dann abgefragt werden kann.

analyze index id_dual validate structure;
select name, blocks, pct_used, distinct_keys lf_rows, del_lf_rows
  from index_stats
;
NAME         BLOCKS  PCT_USED  LF_ROWS  DEL_LF_ROWS
---------------------------------------------------
ID_DUAL          32         1        1            0

Wenn viele gelöschte Leafs (DEL_LF_ROWS >10%) angezeigt werden, sollte neu organisiert werden.

Statistiken über die Nutzung des Index

Ab Oracle9i können Statistiken über die Nutzung eines Index erzeugt werden. Diese werden in V$OBJECT_USAGE angezeigt. Nicht genutzte Indizes können so erkannt und gelöscht werden.

Monitoring starten

alter index hr.dept_id_idx monitoring usage;

Monitoring stoppen

alter index hr.dept_id_idx nomonitoring usage;

Informationen über Indizes

• DBA_INDEXES - Indexname und -typ
• DBA_IND_COLOUMNS - indizierte Spalten
• V$OJECT_USAGE - Statistiken über Indexnutzung

Index löschen

Bevor man große Datenmengen importieren will, sollte man den Index löschen und nach dem Import neu erstellen. Dies geht schneller. Automatisch erstellte Indizes können erst nach dem Deaktivieren der entsprechenden Constraints gelöscht werden.

Löschen Sie Indizes, die selten gebraucht werden und erstellen Sie diese kurz vor Gebrauch.

drop index id_dual;

Views

USER_INDEXES

Infos über Indizes

USER_IND_COLUMNS

Infos über die Spalten

Übung Index

Übungen siehe Seite .