Bitmap-Indexe

 Datenbanken II Holger Brämer, 05IND‐P Sommersemester 2008 Hochschule für Technik, Wirtschaft und Kultur Leipzig (FH) Fachbereich Informatik, Mathematik und Naturwissenschaften Thema: Index‐ und Zugriffstrukturen für Data Warehousing • Materialisierte Sichten • Bitmap‐Indexe • Verbundindexe 1 Inhaltsverzeichnis Materialisierte Sichten ..................................................................................................................................... 3 Bitmap‐Indexe ................................................................................................................................................... 6 Logarithmisch kodierter Bitmap‐Index .......................................................................................................... 9 Mehrkomponenten‐Bitmap‐Index .............................................................................................................. 10 Bereichskodierter Bitmap‐Index .................................................................................................................. 11 Mehrkomponentenbereichskodierter Bitmap‐Index .................................................................................. 12 Intervallkodierter Bitmap‐Index .................................................................................................................. 13 Verbundindexe (Join‐Index) ........................................................................................................................... 14 Bitmap‐Verbundindex ................................................................................................................................. 15 Quellen ............................................................................................................................................................ 16 2 Materialisierte Sichten Materialisierte Sichten vollführen mehrere Rollen, wie die Verbesserung der Anfrage Performance oder dem Bereitstellen von replizierten Daten. Im Data Warehouse nutzt man materialisierten Sichten unter anderem, um aggregierte Daten zu speichern. Daher werden sie oft als Übersichten genutzt, weil sie eine große Menge an Daten vereinen. Sie werden außerdem verwendet, um Joins zwischen verschiedenen Tabellen, mit oder ohne Aggregation, vor zu berechnen. Dies eliminiert den Overhead, der bei teuren Joins oder Aggregationen für eine Klasse an großen oder wichtigen Anfragen entsteht. Dies beschleunigt Anfragen auf sehr großen Datenbanken, da diese oftmals Joins und/oder Aggregationen beinhalten. Diese Operationen sind sowohl zeitlich als auch von der Rechenleistung teuer; diese Ersparnis geht jedoch zu Lasten des Speicherplatzes. Materialisierte Sichten sind reale Tabellen aber für den Endnutzer transparent, d.h. sie werden beim Stellen einer Anfrage nicht immer direkt angesprochen. Bei der Formulierung einer Anfrage kann man sich auf die materialisierte Sicht beziehen oder auf die Tabellen und klassischen Sichten. Ist bei einer materialisierten Sicht das Query Rewrite aktiviert, kann der Anfrage‐Optimierer (engl. „Query Optimizer“) die übergebene Anfrage automatisch so umschreiben, dass diese genutzt wird. Der generierte Plan, der zum Ergebnis der Anfrage führt, nutzt so die materialisierte Sicht, ohne auf die zugrunde liegenden Tabellen zurückzugreifen zu müssen. Dies verkürzt die Antwortzeit für das Ergebnis der Anfrage. Vorteil des Query Rewrite ist es daher, dass die Anfragen statt den referenzierten Tabellen die materialisierten Sichten nutzen und die Optimierung damit die Aufgabe des Query Optimizer bleibt. Wird nun eine materialisierte Sicht gelöscht, kann die Anfrage weiterhin ausgeführt werden, da diese die Tabellen referenziert hat und nicht direkt die materialisierte Sicht. Transparent Query Rewrite Umschreiben der Anfrage Generiere Plan
Nutzer stellt Anfrage Kostenvergleich und Wahl des besten Ausführungsplans Ergebnis der Anfrage Generiere Plan Oracle
3 Es gibt verschiedene Typen von materialisierten Sichten. Man unterscheidet sie allgemein in: •
materialisierte Sichten mit Aggregationen, •
materialisierte Sichten mit ausschließlich Joins und •
verschachtelte materialisierte Sichten sowie Kombinationen aus diesen Typen. Erstellen einer materialisierten Sicht CREATE MATERIALIZED VIEW name BUILD [DEFERRED | IMMIDIATE] REFRESH [FAST | FORCED | COMPLETE] ON [COMMIT | DEMAND] [ENABLE QUERY REWRITE] AS sql_statement Der BUILD Parameter gibt an, ob die materialisierte Sicht erst nach dem ersten Aktualisierung (DEFERRED) oder sofort (IMMIDIATE) erstellt werden soll. Der REFRESH Parameter gibt an wie und wann aktualisiert werden soll. Ist das Query Rewrite aktiviert, so wird die materialisierte Sicht vom Anfrage Optimierer zur Verbesserung des Ausführungsplans herangezogen. Beispiel 1 (mit manueller Aktualisierung und aktiviertem Query Rewrite) CREATE MATERIALIZED VIEW prod_sales_mv BUILD DEFERRED REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE AS SELECT p.prod_name, SUM(s.amount_sold) FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_name; Manuelle Aktualisierung: EXEC DBMS_MVIEW.REFRESH (list=>‘prod_sales_mv‘); 4 Beispiel 2 (mit Materialized View Log) CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW sum_sales BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT s.prod_id, s.time_id, COUNT(*) AS count_grp, SUM(s.amount_sold) AS sum_dollar_sales, COUNT(s.amount_sold) AS count_dollar_sales, SUM(s.quantity_sold) AS sum_quantity_sales, COUNT(s.quantity_sold) AS count_quantity_sales FROM sales s GROUP BY s.prod_id, s.time_id; Die Materialized View Log dient dazu, Änderungen auf den Tabellen zu loggen und so ein schnelles Aktualisieren („REFRESH FAST“) zu ermöglichen, bei dem nur die Änderungen in der materialisierten Sicht übernommen werden. Somit muss sie nicht wie bei „REFRESH COMPLETE“ komplett neu berechnet werden. Löschen einer materialisierten Sicht DROP MATERIALIZED VIEW prod_sales_mv; 5 Bitmap­Indexe Bitmap‐Indexe werden vor allem in Data Warehouse Umgebungen genutzt. Die Umgebungen haben typischerweise große Mengen an Daten und ad hoc Anfragen, dafür ein geringes Level an gleichzeitigen DML Transaktionen. Für solche Einsatzgebiete bieten Bitmap‐Indexe: •
kürzere Antwortzeiten für eine große Klasse von ad hoc Anfragen •
geringere Speicheranforderungen gegenüber anderen Index‐Strukturen •
große Performancegewinne auch auf Hardware mit einer relativ kleinen Anzahl an CPUs oder einem kleinem Speicher •
effiziente Verwaltung während parallelen DML Transkationen und Anfragen Ein kompletter Index auf eine große Tabelle mit einen traditionellen B‐Baum Index kann sündhaft teuer bezüglich Festplattenspeicher sein, da die Indexe mehrere Mal so groß sein können wie die Daten in der Tabelle. Bitmap Indexe nehmen hingegen typischerweise nur einen Bruchteil der Größe der indexierten Daten einer Tabelle ein. Beim Bitmap‐Index werden ein oder mehrere Attribute in Form eines Bitmusters (engl. Bitmap) gespeichert. Dieses Bitmuster entspricht einem Vektor mit einer Länge n, wobei n die Anzahl der unterschiedlichen Werte für das Attribut ist. Für jeden Wert steht ein Bit dieses Vektors. Ist dieses auf „1“ gesetzt, entspricht das Attribut diesem Wert. Ansonsten ist der Wert auf 0 gesetzt. Kardinalität und Erstellen von Bitmap­Indexen Die Vorteile von Bitmap‐Indexen sind für Spalten mit einer kleinen Anzahl an einzigartigen Werten gegenüber der Anzahl an Zeilen der Tabelle am größten. Dieses Verhältnis wird als Kardinalität bezeichnet. Eine Spalte ‘geschlecht’ wie in Tabelle ‘person’ ist optimal für einen Bitmap‐Index, da sie nur zwei verschiedene Werte hat – ‘männlich’ und ‘weiblich’. In dem genannten Beispiel darf die Spalte ‘geschlecht’ nicht NULL sein. pid
name
1
2
3
4
5
6
7
Hans
Werner
Katrin
Peter
Anna
Fritz
Karen
geschlecht
familienstand
männlich
männlich
weiblich
männlich
weiblich
männlich
weiblich
ledig
verheiratet
geschieden
verheiratet
NULL
ledig
verheiratet
Beispiel‐Tabelle ‘person’ 6 Unter Oracle lassen sich Bitmap‐Indexe wie folgt erzeugen: CREATE BITMAP INDEX person_geschlecht_bi
ON person(geschlecht);
CREATE BITMAP INDEX person_familienstand_bi
ON person(familienstand);
Folgende Tabellen veranschaulichen die Bitmap‐Indexe auf ‘geschlecht’ und ‘familienstand’. pid 1 2 3 4 5 6 7 geschlecht pid männlich weiblich 1 0 1 0 0 1 1 0 0 1 1 0 0 1 1 2 3 4 5 6 7 familienstand ledig 1 0 0 0 0 1 0 verheiratet 0 1 0 1 0 0 1 geschieden 0 0 1 0 0 0 0 NULL 0 0 0 0 1 0 0 Beispiel für einen Bitmap‐Index der Tabelle Beispiel für einen Bitmap‐Index der Tabelle ‘person’ für die Spalte ‘geschlecht’ ‘person’ für die Spalte ‘familienstand’ (person_geschelcht_bi) (person_familienstand_bi) Data Warehouse Administratoren erstellen Bitmap‐Indexe aber auch auf Spalten mit höherer Kardinalität. Eine Spalte mit 10.000 verschiedenen Werten in einer Tabelle mit einer Million Einträgen ist ein solcher Kandidat für einen Bitmap‐Index. Dieser ist in dem Falle besser als ein B‐Baum‐
Index, vor allem wenn die Spalte oft in Verbindung mit anderen indexierten Spalten verwendet wird. Bitmap‐Indexe sollte man nutzen, wenn folgende Bedingungen erfüllt sind: •
100 oder mehr Zeilen einer Tabelle haben denselben Wert der zu indexierenden Spalte. In diesem Fall ist ein Bitmap‐Index sehr viel kleiner als ein normaler Index und kann auch viel schneller erstellt werden. Desweiteren muss zusätzlich eine der folgenden Bedingungen erfüllt sein: o Die indexierte Spalte wird zur Einschränkung genutzt (WHERE‐Klausel) oder o Die indexierte Spalte ist ein Fremdschlüssel für eine Dimensionstabelle. In diesem Fall macht ein solcher Index eine Star Transformation eher möglich. 7 Ein Bitmap‐Index sollte nicht auf einer Spalte mit einzigartigen Werten erstellt werden; im Beispiel also nicht auf die Spalte ‘pid’, da diese den Primärschlüssel darstellt. Ein B‐Baum‐
Index bietet in diesem Fall die effektivste Lösung. Bitmap­Indexe und NULL­Werte Gegenüber vielen anderen Indextypen beinhalten Bitmap‐Indexe auch Zeilen mit NULL‐
Werten. Dies kann für einige SQL‐Anfragen nützlich sein, wie bei einer Anfrage mit der Aggregationsfunktion COUNT. SELECT COUNT(*)
FROM person
WHERE familienstand IS NULL;
Diese Anfrage nutzt den Bitmap‐Index auf ‘familenstand’. Auf einem B‐Baum‐Index wäre diese Anfrage nicht machbar, da dieser keine NULL‐Werte speichert. Löschen eines Bitmap­Indexes Bsp.: DROP INDEX person_geschlecht_bi; Verwendung eines Bitmap­Index Gesucht werden die Namen aller männlichen Personen, die Single sind: SELECT name
FROM person
WHERE geschlecht = 'männlich' AND (familienstand = 'ledig' OR
familienstand = 'geschieden')
Das Ergebnis der Anfrage lässt sich sehr schnell berechnen. Dies liegt nicht nur am geringeren Speicherbedarf sondern auch an den binären Operationen AND und OR, die bei Bitmap‐Indexen zum Verknüpfen der Bitvektoren genutzt werden. 1
1
0
1
0
1
0
1
0
0
0
0
1
0
0
0
1
0
0
0
0
1
0
0
0 0
1
0
Das erste und sechste Tupel sind Ergebnis der Anfrage. Hans und Fritz fallen somit unter die Kategorie männliche Single. 8 Logarithmisch kodierter Bitmap­Index Beim logarithmisch kodierten Bitmap‐Index wird jedem Wert eine eindeutige Dezimalzahl zugeordnet, die dann binär kodiert wird. Damit sinkt die Anzahl der benötigten Bitmap‐
Vektoren auf log2n. Im Falle der Spalte ‘geschlecht’ der Tabelle ‘person’ halbiert sich die Anzahl der Bitvektoren, so dass nur noch einer benötigt wird; für die Spalte ‘familienstand’ werden bei logarithmischer Kodierung nur noch zwei Vektoren benötigt. Kodiert man die Monate eines Jahres auf diese Weise, wird beispielsweise dem Monat Oktober die Zahl 9 zugeordnet und binär als 1001 kodiert. Die zwölf Monate können so mit nur vier Bitmap‐Vektoren kodiert werden. 1 9 2 6 8 5 10 0 2 11 Monat Februar Oktober März Juli September Juni November Januar März Dezember B3 0 1 0 0 1 0 1 0 0 1 B2 0 0 0 1 0 1 0 0 0 0 B1 0 0 1 1 0 0 1 0 1 1 B0 1 1 0 0 0 1 0 0 0 1 Tabelle: Logarithmisch kodierter Bitmap‐Index auf „Monat“ Dieses Kodierverfahren ist optimal bezüglich des benötigten Speicherplatzes. Jedoch müssen für jede Art von Anfrage alle Bitmap‐Vektoren geladen werden, weshalb er in der Praxis aufgrund des damit erheblichen Zeitanspruchs keine Anwendung findet. 9 Mehrkomponenten­Bitmap­Index Beim Mehrkomponenten‐Bitmap‐Index wird nicht für jeden unterschiedlichen Wert ein Bitmap‐Vektor angelegt. Am häufigsten wird der <n,m>‐Mehrkomponenten‐Bitmap‐Index verwendet, bei dem verschiedene Werte durch Bitmap‐Vektoren dargestellt werden. Somit kann jeder Wert mit 0
1 durch zwei Bitmap‐Werte mit 1 und 1 dargestellt werden. Mehrkomponenten‐Bitmap‐Index für die zwölf Monate es Jahres: <3,4>‐ Mehrkomponenten‐Bitmap‐Index für die Monate 0 bis 11 Æ 4
1 9 2 6 8 5 10 0 2 11 x Monat Februar Oktober März Juli September Juni November Januar März Dezember B2,1 0 1 0 0 1 0 1 0 0 1 y B1,1 0 0 0 1 0 1 0 0 0 0 z B0,1 1 0 1 0 0 0 0 1 1 0 B3,0 0 0 0 0 0 0 0 0 0 1 B2,0 0 0 1 1 0 0 1 0 1 0 B1,0 1 1 0 0 0 1 0 0 0 0 B0,0 0 0 0 0 1 0 0 1 0 0 Tabelle: <3,4>‐Mehrkomponenten‐Bitmap‐Index auf „Monat“ In diesem Beispiel werden statt zwölf nur sieben Bitmap‐Vektoren benötigt, um alle Werte zu identifizieren. Der Mehrkomponenten‐Bitmap‐Index bietet die wichtigsten Vorteile des Standard‐Bitmap‐
Index. Der Speicherplatzgewinn (von auf Vektoren) wird jedoch mit höheren Prozesskosten bei der Auswertung von Anfragen bezahlt, da für eine Punktanfrage jeweils zwei Bitmap‐Vektoren gelesen werden müssen. 10 Bereichskodierter Bitmap­Index Bereichskodierte Bitmap‐Indexe sind gegenüber den bisher beschriebenen Bitmap‐Indexen besser für Bereichsanfragen geeignet. Es wird wie beim Standard‐Bitmap‐Index für jeden Wert einer Spalte ein Bitmap‐Vektor angelegt. Ist dieser Wert des zu dieser Position gehörigen Tupels kleiner oder gleich dem gegebenen Wert, wird das Bit im Bitmap‐Vektor auf „1“ gesetzt. 1 9 2 6 8 5 10 0 2 11 Monat Februar Oktober März Juli September Juni November Januar März Dezember B11 1 1 1 1 1 1 1 1 1 1 B10 1 1 1 1 1 1 1 1 1 0 B9 1 1 1 1 1 1 0 1 1 0 B8 1 0 1 1 1 1 0 1 1 0 B7 1 0 1 1 0 1 0 1 1 0 B6 1 0 1 1 0 1 0 1 1 0 B5 1 0 1 0 0 1 0 1 1 0 B4 1 0 1 0 0 0 0 1 1 0 B3 1 0 1 0 0 0 0 1 1 0 B2 1 0 1 0 0 0 0 1 1 0 B1 1 0 0 0 0 0 0 1 0 0 B0 0 0 0 0 0 0 0 1 0 0 Tabelle: Bereichskodierter Bitmap‐Index auf „Monat“ Hat man beispielsweise eine Bereichsanfrage, die sich auf die Monate März (3) bis September (8) bezieht, benötigt man nur die zwei Bitvektoren B2 und B8 (Ergebnis: ). Für Bereichsanfragen braucht man damit maximal zwei Bitmap‐Vektoren; bei einseitig begrenzten Bereichsanfragen nur einen. Für Punktanfragen werden hingegen genau zwei Bitmap‐Vektoren benötigt. Aufgrund dieser Kodierung, sind beim größten Bitmap‐Vektor immer alle Bits auf 1 gesetzt, so dass dieser nicht gespeichert werden muss. 11 Mehrkomponentenbereichskodierter Bitmap­Index Dieser Indextyp ist eine Kombination aus dem Mehrkomponenten‐Bitmap‐Index und den bereichskodierten Bitmap‐Index. Ziel dabei ist es die Vorteile beider Indexe zu nutzen; dies wird jedoch mit einem erhöhten Leseaufwand bei allen Anfragetypen bezahlt. 1 9 2 6 8 5 10 0 2 11 x Monat Februar Oktober März Juli September Juni November Januar März Dezember B2,1 1 1 1 1 1 1 1 1 1 1 y B1,1 1 0 1 1 0 1 0 1 1 0 z B0,1 1 0 1 0 0 0 0 1 1 0 B3,0 1 1 1 1 1 1 1 1 1 1 B2,0 1 1 1 1 1 1 1 1 1 0 B1,0 1 1 0 0 1 1 0 1 0 0 B0,0 0 0 0 0 1 0 0 1 0 0 Tabelle: Mehrkomponentenbereichskodierter‐Bitmap‐Index auf „Monat“ Wie man am Beispiel erkennt, werden die Bits, die beim Mehrkomponenten‐Bitmap‐Index links der „1“ standen bei diesem Indextyp auch auf „1“ gesetzt. Wie beim bereichskodierten Bitmap‐Index, braucht der größte Vektor (B2,1 ,B3,0) jeder Gruppe (y,z) nicht abgespeichert werden, da alle Bits auf „1“ gesetzt sind. 12 Intervallkodierter Bitmap­Index Neben dem bereichskodierten Bitmap‐Index soll dieser Indextyp eine optimale Bearbeitung von Bereichsanfragen ermöglichen und weiterhin noch den Speicherplatzbedarf reduzieren. Dabei steht jeder Bitmap‐Vektor für ein vordefiniertes festes Intervall und enthält die Hälfte der zu indexierenden Werte. Ein Bit eines Vektors wird genau dann „1“ gesetzt, wenn der Wert im vorgegebenen Intervall liegt. Intervalle: 0,5 , 1 9 2 6 8 5 10 0 2 11 1,6 , 2,7 , Monat Februar Oktober März Juli September Juni November Januar März Dezember 3,8 , I5 0 1 0 1 1 1 1 0 0 0 4,9 , I4 0 1 0 1 1 1 0 0 0 0 5,10 I3 0 0 0 1 1 1 0 0 0 0 I2 0 0 1 1 0 1 0 0 1 0 I1 1 0 1 1 0 1 0 0 1 0 I0 1 0 1 0 0 1 0 1 1 0 Tabelle: Mehrkomponentenbereichskodierter‐Bitmap‐Index auf „Monat“ Hat man beispielsweise eine Bereichsanfrage, die sich auf die Monate März (3) bis September (8) bezieht, benötigt man nur den Bitvektor I3. Möchte man die Monate Januar bis September, muss man I1 und I3 verknüpfen (Ergebnis: ). 13 Verbundindexe (Join­Index) Der Verbundindex ist ein Index über mehrere Tabellen und soll häufig verwendete Verbundoperationen optimieren, indem der Verbund als Index abgespeichert wird. Erstellen eines solchen Indexes: CREATE INDEX indexname ON faktentab(dimensionstab.spalte)
USING faktentab.spalte = dimensionstab.spalte;
Bsp.: CREATE INDEX joinidx ON Bestellung(Kunde.K_ID)
USING Bestellung.K_ID = Kunde.K_ID;
Bestellung B_ROWID 0x001 0x002 0x003 0x004 0x005 0x006 Kunde
K_ID 101 101 102 102 103 103 A_ID … … … … … … Z_ID … … … … … … K_ROWID
0x100
0x101
0x102
0x103
… … … … … … .. K_ID
101
102
103
104
Nachname Müller Meier Schulze Voigt Vorname
Martin
Franz
Michael
Stefanie
joinidx
0x100: {0x001, 0x002, …} 0x101: {0x003, 0x004, …} 0x102: {…}
Die Liste der Tupel, die beim Join‐Index verbunden werden, wird im Index abgelegt. Dadurch werden teilweise Verbünde/Gruppierungen ohne Zugriff auf die fremde Relation möglich. 14 Bitmap­Verbundindex Der Bitmap‐Verbundindex ist eine Kombination von Bitmap‐Index und Verbundindex. Durch das Speichern des Verbundresultats, kann die Performance extrem verbessert werden, da durch die Vorberechnung in Form von Indexen ein Join vermieden werden kann. Somit sind sie in der Speicherbelegung effizienter als materialisierte Verbundsichten. Sie werden häufig in Star Schema Umgebungen verwendet. SELECT time_id, c_id, amount_sold
FROM sales;
SELECT c_id, c_gender
FROM customers;
TIME_ID
C_ID AMOUNT_SOLD
01-JAN-98
12
2291
01-JAN-98
13
114
01-JAN-98
14
553
01-JAN-98
15
0
C_ID C_GENDER
12
M
13
M
14
F
15
M
…
…
…
…
…
CREATE BITMAP INDEX sales_cust_gender_bjix
ON sales(customer.c_gender)
FROM sales, customers
WHERE sales.c_id = customers.c_id
LOCAL NOLOGGING COMPUTE STATISTICS;
SELECT sales.time_id, customers.c_gender, sales.amount_sold
FROM sales, customers
WHERE sales.c_id = customers.c_id;
TIME_ID
C_GENDER AMOUNT_SOLD
01-JAN-98
M
2291
01-JAN-98
M
114
01-JAN-98
F
553
01-JAN-98
M
0
…
…
…
15 Quellen Oracle® Database Data Warehousing Guide, 10g Release 2 (10.2) [ http://download.oracle.com/docs/cd/B19306_01/server.102/b14223.pdf ] Oracle® Database SQL Reference, 10g Release 2 (10.2) [ http://download.oracle.com/docs/cd/B19306_01/server.102/b14200.pdf ] Datenbanken – Implementierungstechniken (Gunter Saake, Andreas Heuer) [ http://wwwiti.cs.uni‐magdeburg.de/iti_db/biber2/b2‐folien.html ] Index‐ und Speicherungsstrukturen (Kai‐Uwe Sattler, Gunter Saake) [ http://wwwiti.cs.uni‐magdeburg.de/iti_db/lehre/dw/06‐Indexstrukturen‐MD.pdf ] Indexstrukturen für Data Warehouse (Kai‐Uwe Sattler, Gunter Saake) [ http://tu‐ilmenau.de/fakia/fileadmin/template/FakIA/Strukt‐
Fakultaet_IA/ipim/dbis/dwt/dwt‐8.pdf ] Data Warehounsing – Indexierung (Ulf Leser) [ http://www2.informatik.hu‐
berlin.de/Forschung_Lehre/wbi/teaching/sose05/dwh/07_indexierung.pdf ] Bitmap Indizes in Data Warehouses (Stefanie Tornow) [ http://www.minet.uni‐jena.de/dbis/lehre/ss2006/sem_ada/mat/Seminardokumentation‐
Bitmap%20Index_11_7.pdf ] 16