Unsichtbare Indizes in Oracle 11g
Ralf Durben, 03.03.2009

Im Bereich der Indizes hat gibt es in Oracle 11g ein neues Feature,  welches zwar nicht im Fokus der üblichen Publikationen steht, jedoch  sehr wichtig für die Datenbankadministration bzw. Entwicklung sein kann: Der unsichtbare Index

Ein unsichtbarer Index ist zwar vorhanden, jedoch wird er vom Optimizer  nicht in Betracht gezogen, wird also bei Leseoperationen nicht  verwendet. Da er aber existiert, also die Speicherung der eines normalen Index entspricht, wird er durch DML Kommandos normal gepflegt.

Wozu kann nun ein solcher Index gebraucht werden, der zwar Speicher und  Pflege in Anspruch nimmt, aber nicht für Leseoperationen benutzt wird?  Wenn in einer gegebenen Datenbank die Verwendung eines neuen Index geplant ist, kann man diesen erstellen und dann prüfen, ob dieser neue  Index wirklich nutzbringend ist und keinen Schaden anrichtet. Da neue Indizes auch negativen Einfluss auf Leseoperationen haben können begibt man sich mit dieser, bislang einzigen, Methode auf einen Blindflug, denn der neue Index ist sofort für alle Datenbanksitzungen sichtbar und wird vom Optimizer bei der Erstellung eines Ausführungsplans in Betracht gezogen.

Gerade in Produktivdatenbanken kann ein solcher Blindflug fatale Folgen  haben. Aus diesem Grund benötigte man bislang eigentlich immer ein  Testsystem mit gleicher Datenmenge und Workload. Dieser grosse Aufwand  wird von vielen aber nicht betrieben, sodass es letztlich beim Blindflug bleibt.

Ab Oracle 11g kann man nun den neuen Index so anlegen, dass dieser zwar existiert, aber noch nicht verwendet wird. Dazu wird der Index als unsichtbar angelegt. Mit einem Datenbankparamater auf Datenbanksitzungsebene kann man nun für eine  einzelne Datenbanksitzung die Nutzung der unsichtbaren Indizes aktivieren und so den neuen Index testen, ohne dass die restlichen Datenbankbenutzer bzw. Datenbanksitzungen davon betroffen wären.

Wenn der neue Index für gut befunden wurde kann er anschließend sichtbar gemacht werden und ist damit für alle Datenbanksitzungen nutzbar. Dieses ist eine sehr schnelle Operation!

Mit dieser neuen Funktionalität kann man genau prüfen, ob ein neuer  Index sinnvoll ist oder nicht. Damit kann man einen Blindflug, der  unangenehme Folgen haben kann, vermeiden. Desweiteren kann man die Ausführungspläne viel einfacher vergleichen (mit und ohne Index), indem  man zwei Datenbanksitzungen nutzt (eine mit Verwendung der unsichtbaren Indizes und eine normale Datenbanksitzung).

Die Syntax dieses neuen Features ist sehr einfach. Einen unsichtbaren Index erstellen Sie indem Sie der CREATE INDEX Syntax einfach das Wort INVISIBLE hinzufügen.

CREATE INDEX i_emp_ename ON emp(ename) INVISIBLE;

Existierende Indizes können Sie auch jederzeit unsichtbar machen

ALTER INDEX i_emp_ename INVISIBLE;

Entsprechend wird ein unsichtbarer Index sichtbar durch

ALTER INDEX i_emp_ename VISIBLE;

Letztlich fehlt nur noch das Aktivieren der unsichtbaren Indizes in  einer Datenbanksitzung:

ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;

Dieser Parameter kann auch auf Systemebene gesetzt werden. Das kann dazu genutzt werden, um einen Feldversuch durchzuführen. Sollte es wider Erwarten zu Problemen kommen kann man diesen Parameter wieder auf FALSE setzen (was auch der Default ist). Dieses ist schneller als das  herkömmliche Löschen der problematischen Indizes.

Zu beachten ist allerdings, dass ein unsichtbarer Index wirklich existiert und gepflegt wird. Das hat auch Auswirkungen auf die Tuning  Advisor (SQL Tuning Advisor und SQL Access Advisor). Wenn ein unsichtbarer Index existiert, der von einem dieser beiden Advisor  vorgeschlagen werden würde, erfolgt diese Empfehlung aber nicht, denn aus Sicht der Advisor ist dieser Index vorhanden. Der Grund dafür ist  ganz einfach zu erklären. Wenn ein Advisor einen Index vorschlagen  würde, der aber schon als unsichtbar existiert, dann würde das CREATE INDEX Kommando scheitern, da bekanntlich jeder Index (also die indizierte Spaltenkombination) nur einmal vorkommen darf. Der Advisor  würde also einen Vorschlag machen, der nicht realisierbar ist - und das erfolgt nicht. Auch aus diesem Grund sollte der Zustand ”unsichtbar” nur von vorübergehender Dauer sein.

Mein Rat: Vermeiden Sie Blindflüge beim Anlegen von Indizes. Erstellen sie diese erst einmal als  unsichtbar. Testen Sie die neuen Indizes bevor Sie diese für alle anderen Datenbankbenutzer freischalten. Alles was Sie dazu brauchen ist  Oracle 11g, denn der unsichtbare Index ist Bestandteil der Standard Edition One, Standard Edition und der Enterprise Edition.

Zurück zur Tippübersicht

Zurück zur Tippübersicht