1. Indextypen 2. Indexverwendung Inhalt

Inhalt
1. Indextypen
Ø
Ø
Ø
Ø
B*Baum-Index
Reversed Key Index
Bitmap Index
Funktionsbasierter Index
2. Indexverwendung
Ø
Ø
Ø
Vergleich von B*Baum und Bitmap
Steuerung der Indexverwendung
Richtlinien für die Indizierung
Dr. Frank Haney
1
Indextypen
Logisch
Ø Einspaltig
Ø Zusammengesetzt (bis zu 32 Spalten)
Ø Eindeutig (Eintrag verweist eindeutig auf eine Zeile)
Ø Nicht eindeutig
Ø Funktionsbasiert
Ø Domain-Indizes
Physisch
Ø Nicht partitioniert
Ø Partitioniert
Ø B*Baum
• Normal
• Reversed Key
Ø Bitmap
Dr. Frank Haney
2
Syntax
Erstellen:
CREATE [UNIQUE|BITMAP] INDEX schema.index
ON schema.table({column|column_expression} [ASC|DESC], ...)
[NOSORT|REVERSE]
[ONLINE]
[COMPUTE STATISTICS]
[LOGGING|NOLOGGING]
[PARALLEL|NOPARALLEL]
[INDEXTYPE IS indextype];
Reorganisieren:
ALTER INDEX index [ENABLE|DISABLE] [UNUSABLE]
[REBUILD [REVERSE|NOREVERSE]]
[DEALLOCATE UNUSED]
[COALESCE]
[ONLINE];
Dr. Frank Haney
3
B*Baum-Indizes
Indexstruktur
Wurzel (root)
Zweig 1
Blatt 1 (leaf)
Zweig 2
Blatt 2
Blatt 3
Blatt 4
Blatt 5
Doppelte Verkettung
Indexeintrag
Indexzeilenheader
Dr. Frank Haney
Spaltenlänge
Spaltenwert
ROWID
4
Eigenschaften von B*Baum-Indizes
Eigenschaften der Leafeinträge:
Ø Schlüsselwerte werden für mehrfach vorkommende Werte wiederholt.
Ø Es gibt keinen Indexeintrag, der in allen indizierten Spalten NULL enthält.
Ø Es werden eingeschränkte ROWIDs verwendet, da alle Indexeinträge auf ein
Segment zeigen. (Hexadezimal: Block.Zeile.Datendatei)
Indizes wachsen:
Ø Horizontal durch Einfügen neuer Einträge in die Indexblöcke und durch
Aufspalten der Blöcke, wenn sie gefüllt sind.
ØVertikal, wenn der Root-Block voll ist und eine neue Ebene angelegt werden
muß.
Reverse Key Indizes:
Ø Kehren die Bytefolge der Schlüsselwerte um. Die Reihenfolge der Spalten
bleibt erhalten.
Ø Verteilen die Indexeinträge über den Baum und vermeiden eventuelle I/OEngpässe sowie zu häufiges Aufteilen von Blöcken.
Ø Gut bei monoton wachsenden Schlüsselwerten (Sequenz).
Ø Gut bei Abfragen mit = Operator, schlecht bei Bereichsabfragen (BETWEEN).
Dr. Frank Haney
5
Bitmap-Indizes
Initialisierungsparameter:
CREATE_BITMAP_AREA_SIZE
BITMAP_MERGE_AREA_SIZE
Dr. Frank Haney
Schlüssel
Start-ROWID
End-ROWID
Bitmap
Blau
10.0.3
12.8.3
10001000001110
Grün
10.0.3
12.8.3
01100001000000
Rot
10.0.3
12.8.3
00000000110001
Gelb
10.0.3
12.8.3
00010110000000
6
Vergleich von von B*Baum- und Bitmap-Indizes
B*Baum-Index
Bitmap-Index
Günstig für Spalten hoher
Kardinalität
Günstig für Spalten niedriger
Kardinalität
Updates an Schlüsseln relativ
wenig aufwendig
Updates an Schlüsseln sehr
aufwendig
Ineffizient bei Abfragen mit ORPrädikaten
Effizient bei Abfragen mit ORPrädikaten
Zeilensperre
Sperren auf Bitmap-SegmentEbene
Mehr Speicherplatz
Weniger Speicherplatz
Günstig für OLTP
Günstig für DSS
Dr. Frank Haney
7
Bitmap Join-Index
Vertrieb
Kunden
CREATE
ON
FROM
WHERE
Dr. Frank Haney
BITMAP INDEX cust_sales_bji
sales(c.cust_city)
sales s, customers c
c.cust_id = s.cust_id;
8
Bitmap Join-Index
Ø Es ist kein Join mit der CUSTOMERS-Tabelle
erforderlich.
Ø Zur Auswertung der folgenden Abfrage werden nur der
Index und die SALES-Tabelle verwendet:
SELECT SUM(s.amount_sold)
FROM
sales s, customers c
WHERE s.cust_id =
c.cust_id
AND
c.cust_city = 'Sully';
4
5
Dr. Frank Haney
9
Bitmap Join-Index – Vor- und Nachteile
ØVorteile
• Gute Performance bei Join-Abfragen; Platz sparend
• Besonders nützlich für Tabellen mit großen Dimensionen
in Star-Schemas
ØNachteile
• Mehr Indizes sind erforderlich: Bis zu ein Index pro
Dimensionstabellenspalte statt eines Index pro
Dimensionstabelle wird benötigt.
• Höherer Wartungsaufwand: Das Erstellen oder
Aktualisieren eines Bitmap Join-Index erfordert einen Join.
Dr. Frank Haney
10
Funktionsbasierte Indizes
CREATE INDEX FBI_UPPER_LASTNAME
ON CUSTOMERS(upper(cust_last_name));
ALTER SESSION
SET QUERY_REWRITE_ENABLED = TRUE;
SELECT *
FROM
customers
WHERE UPPER(cust_last_name) = 'SMITH';
Dr. Frank Haney
11
Funktionsbasierte Indizes – Verwendung
Vorteile:
Ø Sind ein Mittel gegen die Unwirksamkeit von Indizes, wenn die
indizierte Spalte in Ausdrücken referenziert wird.
Ø Arbeiten mit SQL- und benutzerdefinierten Funktionen.
Ø Können rechenintensive Werte speichern und so beim SELECT und
DELETE effizienter sein.
Voraussetzungen:
Ø Der Parameter QUERY_REWRITE_ENABLED muß auf TRUE oder
FORCE gesetzt sein.
• TRUE: Der CBO entscheidet, ob der Zugriff über den
funktionsbasierten Index kostengünstiger ist.
• FORCE: Die Verwendung des funktionsbasierten Index wird
erzwungen:
Ø Der Parameter QUERY_REWRITE_INTEGRITY entscheidet darüber,
ob benutzerdefinierte Funktionen für das Query Rewrite
zugelassen sind.
Dr. Frank Haney
12
Richtlinien für die Indizierung I
Wann indizieren?
Ø Primärschlüssel- und Uniquespalten haben automatisch Index.
Ø Fremdschlüsselspalten indizieren, wenn sie häufig als Joinattribut
verwendet werden.
Ø Attribute indizieren, die häufig in der WHERE-Klausel referenziert
werden.
Ø WHERE-Klausel spezifisch für die Indexverwendung schreiben.
Ø Weisen eine hohe Selektivität auf (Attribute, die eine geringe
Selektivität aufweisen, nur indizieren, wenn ein Histogramm
verwendet wird!).
Ø Bei viel DML auf der Spalte Performanceverlust durch Indizierung
abwägen. Eventuell Indexverwendung für DML unterdrücken.
Ø Die Indexverwendung kann z.B. über die Hints INDEX,
INDEX_COMBINE, AND_EQUAL, NO_INDEX und FULL
gesteuert werden.
Dr. Frank Haney
13
Richtlinien für die Indizierung II
Zusammengesetzte Indizes:
Ø erhöhen die Selektivität und führen eventuell zu weniger I/O.
Ø sollten verwendet werden, wenn die indizierten Spalten in der
WHERE-Klausel häufig durch AND verknüpft referenziert
werden.
Ø arbeiten dann meist performanter als separate Indizes auf den Spalten.
Reihenfolge der Spalten:
Ø In der WHERE-Klausel referenzierte zuerst (Ausnahme: Index Skip
Scans), geordnet nach Häufigkeit der Verwendung.
Ø Bei gleicher Häufigkeit werden die Spalten nach der Selektivität
geordnet.
Ø Wenn eine Spalte in der Tabelle sortiert vorliegt, dann sollte sie die
erste im Index sein.
Ø Auf Clustering-Faktor achten
Dr. Frank Haney
14
Überwachung der Indexverwendung
Ø So starten Sie die Überwachung der Indexverwendung:
ALTER INDEX mitarbeiter_pk MONITORING USAGE;
Ø So beenden Sie die Überwachung der Indexverwendung:
ALTER INDEX mitarbeiter_pk NOMONITORING USAGE;
Ø V$OBJECT_USAGE enthält Informationen über die
Verwendung eines Index.
Dr. Frank Haney
15
Optimierung der Indizierung
SQL Access Advisor:
ØBestimmt, welche Indizes erforderlich sind
ØEmpfiehlt die Verwendung eines kombinierten Index
Folgende Aufrufmöglichkeiten:
ØSeite "Advisor Central" in Oracle Enterprise Manager
ØAusführung durch die APIs des DBMS_ADVISOR-Package
Verwendet beispielsweise folgende Arbeitslasten:
ØAktuelle Inhalte des SQL-Cache
ØBenutzerdefinierte Gruppe von SQL-Anweisungen
ØSQL Tuning Set
ØHypothetische Arbeitslast
Erzeugt eine Reihe von Empfehlungen und stellt ein
Implementierungsskript bereit
Dr. Frank Haney
16