INDEKSY I SORTOWANIE ZEWNTRZNE Przygotowa Lech Banachowski na

INDEKSY I SORTOWANIE ZEWNĘTRZNE Przygotował Lech Banachowski na podstawie: 1. Raghu Ramakrishnan, Johannes Gehrke, Database Management Systems, Mc. Graw. Hill, 2000 (książka i slide’y). 2. Lech Banachowski, Krzysztof Stencel, Systemy zarzadzania bazami danych, Wydawnictwo PJWSTK, 2007. 3. Dokumentacja Oracle.

Organizacje pliku z danymi v v v Plik nieuporządkowany Plik uporządkowany (posortowany) Plik haszowany PJWSTK, L. Banachowski, SZB 2

Wyszukiwanie Najczęściej wykonywaną operacją w bazie danych jest wyszukiwanie danych. W najprostszej postaci: mając konkretną wartość poszukujemy rekordów, w których ta wartość występuje w danym polu. PJWSTK, L. Banachowski, SZB 3

Indeksy v v Plik nieuporządkowany umożliwia wyszukanie rekordu: – mając rid rekordu, lub – przeglądając sekwencyjnie wszystkie rekordy w pliku. Często wyszukiwanie na podstawie wartości jednego lub więcej pól, np. – wyznacz wszystkich studentów specjalizacji “BD”, – wyznacz wszystkich studentów mających < 20 lat. v Indeksy są strukturami danych, których celem jest wspomaganie szybkiego znajdowania odpowiedzi na takiego rodzaju zapytania. v Takie samo znaczenie jak skorowidz (indeks) w książce! PJWSTK, L. Banachowski, SZB 4

Indeksy v Klucz wyszukiwania dla indeksu – v wybrane pola rekordu względem których ma odbywać się wyszukiwanie. Indeks składa się z : – pozycji danych k* określanych względem wartości klucza wyszukiwania k, oraz z – pozycji indeksu, kierujących wyznaczeniem właściwej pozycji danych k* w oparciu o wartość klucza wyszukiwania. PJWSTK, L. Banachowski, SZB 5

Indeksy v v Plik danych – rekordy danych. Plik indeksu – pozycje danych, pozycje indeksu. Indeks wewnętrzny - plik indeksu zawiera w sobie plik danych. Pozycjami danych k* w indeksie są rekordy danych. Indeks zewnętrzny – plik indeksu jest rozłączny z plikiem danych. Pozycje danych k* zawierają wskaźniki do rekordów danych. Postacie: 1. <k, rid rekordu z tą wartością klucza k> albo 2. <k, lista rid rekordów z tą wartością klucza k > – Ad 2. Postać bardziej zwarta; zmienny format rekordu. PJWSTK, L. Banachowski, SZB 6

Klasyfikacja indeksów v Gdy klucz wyszukiwania zawiera klucz główny – indeks główny wpp. indeks niegłówny. v Indeks jednoznaczny - klucz wyszukiwania zawiera klucz jednoznaczny. v Indeks pogrupowany - indeks wewnętrzny i plik danych posortowany według wartości klucza indeksu. PJWSTK, L. Banachowski, SZB 7

Indeks pogrupowany v Pozycje danych (czyli rekordy danych) są zapisane w kolejności uporządkowanej względem wartości klucza indeksu. W rezultacie, rekordy o tej samej wartości klucza lub zbliżonej znajdują się na tej samej stronie lub na kilku powiązanych. PJWSTK, L. Banachowski, SZB 8

Indeksy wewnętrzne i zewnętrzne v Indeks wewnętrzny haszowany = indeks wewnętrzny i plik danych haszowany. v Indeks wewnętrzny -> – Plik danych posortowany i indeks pogrupowany, albo – Plik danych haszowany i indeks haszowany. v Indeks zewnętrzny -> – Plik danych nieuporządkowany , albo – Plik danych posortowany , albo – Plik danych haszowany. PJWSTK, L. Banachowski, SZB 9

Złożone klucze wyszukiwania v Złożone klucze wyszukiwania: kombinacja pól np. <sal, age>. – Zapytanie równościowe: u – 11 12, 10 12 12, 20 13, 75 <age, sal> Zapytanie zakresowe: u v age=20 and sal =75 11, 80 age < 20; age=20 and sal > 10 Porządek leksykograficzny. 10, 12 20, 12 75, 13 name age sal bob 12 10 cal 11 80 joe 12 20 sue 13 75 Rekordy danych posortowane <name> 80, 11 <sal, age> Pozycje danych posortowane <sal, age> PJWSTK, L. Banachowski, SZB 12 13 <age> 10 20 75 80 <sal> Pozycje danych posortowane <sal> 10

Pseudo-wartość NULL v v v Indeksowane są wszystkie wiersze, w których co najmniej jedna składowa klucza wyszukiwania indeksu nie jest NULL. Na przykład, za pomocą indeksu na kolumnie Comm nie można zrealizować wyszukiwania wierszy z nieokreśloną wartością w polu Comm: SELECT Ename FROM Emp WHERE Comm IS NULL; Można … WHERE Comm IS NOLL NULL; Ograniczenie to nie dotyczy indeksów bitmapowych i klastrowych. PJWSTK, L. Banachowski, SZB 11

Wyszukiwanie zakresowe – plik posortowany v "Wyznacz studentów ze średnią > 4. 0" – Gdy dane w pliku posortowanym: u u wyszukiwanie binarne aby znaleźć pierwszego takiego studenta; przejdź plik wypisując pozostałych takich studentów. – Plik indeksu k 1< k 2< … <KN. Page 0 Page 1 Plik indeksu k. N k 1 k 2 Page N * Wyszukiwanie binarne na mniejszym pliku indeksu! Plik danych posortowany

Pozycja indeksu Strona indeksu P 0 K 1 P 1 K 2 K < K …. < K 1 2 m P K m 2 Statyczne drzewo ISAM (indexed sequential access method) Węzły wewnętrzne Liści e Strony nadmiarowe * W liściach są pozycje danych. Strony główne Pm

Drzewo ISAM Korzeń 40 10* 15* 20 33 20* 27* 51 33* 37* 40* 46* 51* 63 55* 63* 97*

Wstawiamy 23*, 48*, 41*, 42*. . . Korzeń Strony indeksu Strony główne 40 10* Strony nadmiarowe 15* 20 33 20* 27* 23* 51 33* 37* 40* 46* 48* 41* 42* 51* 63 55* 63* 97*

B+ drzewo v v v Wstawianie/usuwanie w czasie rzędu log N; drzewo wyważone względem wysokości. (N = # liści) Każdy węzeł (z wyjątkiem ewentualnie korzenia) zawiera d <= m <= 2 d pozycji indeksu. Parametr d - stopień drzewa. Zapytania równościowe i zakresowe. Pozycje indeksu Pozycje danych

B+ drzewo v v Wyszukiwanie zaczyna się w korzeniu a porównania klucza wyszukiwania prowadzą do liścia tak jak dla ISAM. Wyszukiwanie 5*, 15*, >= 24*. . . Korzeń 13 2* 3* 5* 7* 14* 16* 17 24 19* 20* 22* 30 24* 27* 29* 33* 34* 38* 39* * Na podstawie wyniku wyszukiwania 15*, wiemy że 15* nie ma w drzewie

Drzewa B+ w praktyce v v Średni stopień d=100. Średnie zapełnienie: 67%. Typowa pojemność: – – v Wysokość 4: 1334 = 312, 900, 700 rekordów Wysokość 3: 1333 = 2, 352, 637 rekordów Zwykle górne poziomy drzewa w cache: – – – Poziom 1 = 1 strona = 8 KB Poziom 2 = 133 strony = 1 MB Poziom 3 = 17, 689 strony = 133 MB

Wstawienie pozycji danych do B+ drzewa v v Wyznacz odpowiedni liść L. Wstaw pozycję danych do L. – – Jeśli mieści się – to koniec! Wpp, podziel L (na L i nowy węzeł L 2) u u v v Rozdziel równo pozycje, skopiuj na wyższy poziom środkowy klucz (najmniejszy w prawym węźle L 2). Do “ojca” L wstaw pozycję indeksu z tym kluczem i wskaźnikiem wskazującym na L 2. W razie potrzeby powtórz krok podziału rekurencyjnie. Kroki podziału mogą dojść do korzenia i w rezultacie drzewo może zwiększyć swoją wysokość o jeden.

Wstawianie 8* Pozycja wstawiana do “ojca” jest kopiowana. 5 Podział liścia 2* 3* 5* 7* 8* Pozycja wstawiana do “ojca” jest przesuwana w górę. 17 Podział węzła wewnętrznego 5 13 24 30

Drzewo po wstawieniu 8* Korzeń 17 5 2* 3* 24 13 5* 7* 8* 14* 16* 19* 20* 22* 30 24* 27* 29* 33* 34* 38* 39* v Poprzedni korzeń uległ podziałowi. Wysokość drzewa zwiększyła się o 1. v Możliwa redystrybucja pozycji danych w poziomie nie jest w praktyce realizowana.

B+ drzewo jako indeks wewnętrzny i zewnętrzny v v Indeks może być wewnętrzny i wtedy pozycje danych pokrywają się z rekordami danych, tzn. rekordy danych są zapisywane w strukturze B+ drzewa zgodnie z porządkiem klucza wyszukiwania. Z tego powodu jest to wtedy indeks pogrupowany. Albo indeks jest zewnętrzny i wtedy rekordy z danymi są przechowywane poza indeksem w dowolnym porządku (dla plików nieuporządkowanych, posortowanych i haszowanych). PJWSTK, L. Banachowski, SZB 22

Strategia zastępowania ramek dla stron B+ drzewa • LRU nie jest tu dobrą metodą! Korzeń drzewa i najwyższe poziomy dobrze jest trzymać cały czas w pamięci RAM. PJWSTK, L. Banachowski, SZB 23

Dodatkowe operacje na B+ drzewach v Gdy ilość zajętego miejsca w drzewie spada poniżej pewnego progu, możliwe: – REBUILD utworzenie indeksu od nowa – COALESCE połączenie ze sobą sąsiednich stron o zajętości poniżej 50%. v Gdy na samym początku mamy dany duży zbiór rekordów, to wtedy zamiast powtarzania kolejnych operacji INSERT opłaca się zastosować algorytm Bulk Loading, którego działanie polega na posortowaniu pliku rekordów z danymi, a następnie dobudowaniu nad posortowanym ciągiem kolejnych poziomów indeksowych drzewa B+. PJWSTK, L. Banachowski, SZB 24

Dlaczego nie używamy B-drzew? Dla pełności tematu pokazujemy schemat węzła zwykłego B-drzewa (bez plusa). PJWSTK, L. Banachowski, SZB 25

Podsumowanie drzew v Zastosowanie indeksów o strukturze drzewa: – Wyszukiwanie zakresowe. – Wyszukiwanie równościowe. – Sortowanie (np. ORDER BY). v ISAM – struktura statyczna. – – v Modyfikowane są tylko liście. Wymagane są strony nadmiarowe – mogące istotnie pogorszyć działanie algorytmów. B+ drzewo – struktura dynamiczna. – W praktyce wysokość zwykle <=4.

Indeks haszowany v v Ustalona alokacja stron głównych; alokowane dodatkowe strony nadmiarowe w razie potrzeby. h(k) np. = k mod M = “segment” do którego należy pozycja danych k* o kluczu k (M = # segmentów). 0 1 h(klucz) klucz h M-1 Strony główne Strony nadmiarowe Plik: nieuporządkowany, posortowany i haszowany.

Indeks haszowany z uporządkowanymi segmentami Wariant w którym pozycje danych są uporządkowane względem wartości klucza wyszukiwania. PJWSTK, L. Banachowski, SZB 28

Podsumowanie haszowania v Mogą powstać długie łańcuchy nadmiarowych stron co prowadzi do pogorszenia działania: – konieczność okresowego wykonywan ia operacji REBUILD, – istnieją dynamiczne wersje haszowania – polegające na zwiększaniu liczby M segmentów przez ich podział. v Indeksy haszowane dobre przy wyszukiwaniu równościowym. Nie wspomagają wyszukiwania zakresowego ani sortowania.

Podsumowanie indeksu wewnętrznego v v Dla jednej tabeli może być zbudowany tylko jeden indeks wewnętrzny i wiele indeksów zewnętrznych względem różnych kluczy wyszukiwania. W indeksie wewnętrznym zbudowanym na B+ drzewie rekordy z danymi są zapisywane na stronach reprezentujących liście drzewa. W indeksie haszowanym wewnętrznym rekordy z danymi są zapisywane na stronach segmentów. W obu przypadkach z definicji algorytmów wstawiania i usuwania wynika, że rekordy mogą być przesuwane między stronami. To znaczy, rekordy mogą zmieniać stronę a zatem nie może być do nich z zewnątrz bezpośrednich wskaźników używających identyfikatora strony. Za identyfikator rekordu używa się wtedy wartość jego klucza głównego a dostęp do rekordu jest zawsze realizowany poprzez indeks główny. PJWSTK, L. Banachowski, SZB 30

Indeks wewnętrzny – 2 przypadki v v Indeks główny jest indeksem wewnętrznym. Wtedy wyszukiwanie rekordu według wartości klucza wyszukiwania indeksu zewnętrznego wymaga przejścia dwóch indeksów: najpierw zewnętrznego, w którym znajdujemy wartość klucza głównego szukanego rekordu, a następnie indeksu wewnętrznego głównego, w którym w oparciu o wartość klucza głównego znajdujemy szukany rekord. Indeks główny jest indeksem zewnętrznym, w którego pozycjach danych są zapisywane (zmienne) wskaźniki do rekordów. Przy wyszukiwaniu rekordu przechodzimy tak jak poprzednio dwa indeksy: zewnętrzny i główny. Natomiast przy zmianach położenia rekordu w indeksie wewnętrznym wymagane jest znalezienie pozycji danych tego rekordu w indeksie głównym i zapisanie w niej nowego adresu tego rekordu. PJWSTK, L. Banachowski, SZB 31

Struktury danych wielowymiarowe v Siatka (ang. grid file) – podział obszaru wyszukiwań, powiedzmy leżącego na płaszczyźnie, poziomymi i pionowymi liniami na prostokąty. Punkty trafiające do jednego prostokąta są zapisywane w jednym segmencie. Gdy segmenty się przepełnią, można albo stosować strony nadmiarowe albo dzielić prostokąty na mniejsze zwiększając liczbę segmentów. PJWSTK, L. Banachowski, SZB 32

Struktury danych wielowymiarowe v v v Dzielona funkcja haszowana (ang. partitioned hash function): H(v 1, . . . , vn)=h 1(v 1)&h 2(v 2)&. . &hn(vn) Wartością funkcji haszującej dla układu wartości jest konkatenacja tekstowa wartości funkcji haszujących dla poszczególnych składowych. PJWSTK, L. Banachowski, SZB 33

Drzewa wielowymiarowe v v R drzewa są uogólnieniem B drzew do n wymiarów. Pozycją danych w takim drzewie jest para złożona z n-wymiarowej kostki: {(v 1, . . . , vn): ai<=vi<=bi dla 1<=i<=n} oraz adresu pewnego obiektu geometrycznego, który jest jednoznacznie ograniczony tą kostką. Podobnie pozycją indeksu jest taka kostka oraz wskaźnik do węzła niższego poziomu, w którego poddrzewie znajdują się wyłącznie obiekty zawarte w tej kostce i w żadnym innym poddrzewie takie obiekty nie występują. PJWSTK, L. Banachowski, SZB 34

Powtórzenie Plik danych – składowanie danych • nieuporządkowany • uporządkowany (posortowany) • haszowany Indeks – wyszukiwanie danych i jednoznaczność kluczy • wewnętrzny - pozycja danych = rekord danych • zewnętrzny – pozycja danych = wskaźnik do rekordu danych • pogrupowany – wewnętrzny & plik danych uporządkowany według wartości klucza indeksu • B+ drzewo • tablica haszowana (jako indeks wewnętrzny lub zewnętrzny) PJWSTK, L. Banachowski, SZB 35

Zastosowania sortowania w bazach danych v ORDER BY - dane są wymagane w pewnym porządku. v Budowa indeksu - początkowego B+ drzewa dla wczytywanego zbioru rekordów. v Złączanie tabel metodą Sort-merge. v Realizacja DISTINCT, GROUP BY, UNION, EXCEPT - alternatywą haszowanie. Problem: posortować 1 GB danych za pomocą 10 MB RAM.

Sortowanie zewnętrzne (wielofazowe przez scalanie) v v Faza 0 – sortowanie rekordów w ramach stron: Wczytaj stronę, posortuj ją, zapisz na dysku. Faza 1, 2, 3 …, itd: scalaj uporządkowane podpliki w większe uporządkowane podpliki aż cały plik zostanie uporządkowany. Wejście 1 Wyjście Wejście 2 Dysk Bufory w RAM Dysk

Sortowanie wielofazowe przez scalanie v v W każdej fazie odczytujemy i zapisujemy każdą stronę w pliku. N = # stron => # faz = 3, 4 6, 2 9, 4 8, 7 5, 6 3, 1 2 3, 4 2, 6 4, 9 7, 8 5, 6 1, 3 2 Faza 1 4, 7 8, 9 2, 3 4, 6 Całkowity koszt = 2 N log N 2, 3 v Idea: Dziel i rządź: sortuj podpliki i je scalaj. 4, 4 6, 7 8, 9 v Zamiast dwóch buforów można użyć więcej. Praktycznie liczba faz <=3. 1, 3 5, 6 2 Faza 2 v v Plik wejściowy Faza 0 1, 2 3, 5 6 Faza 3 1, 2 2, 3 3, 4 4, 5 6, 6 7, 8 9

Sortowanie za pomocą B+ drzewa v Warunek: Tabela ma indeks na B+ drzewie względem kolumn sortowania. v Idea: Przejść po liściach indeksu. v Czy jest to dobra metoda? v Przypadki: – Indeks pogrupowany Bardzo dobra! – Indeks nie pogrupowany Może być bardzo zła!

Wewnętrzny indeks pogrupowany v Od korzenia przejdź do skrajnie lewego liścia a następnie sekwencyjnie w prawo po liściach. Indeks Pozycje danych Rekordy danych w pozycjach danych! * Zawsze lepsze od sortowania zewnętrznego!

Indeks niepogrupowany v Ogólnie, jedna operacja We/Wy na rekord danych! Indeks Pozycje danych Rekordy danych

Indeksy w Oracle • Indeks oparty na B+ drzewie • Tabela połączona z indeksem opartym na B+ drzewie • Indeks oparty na B+ drzewie z odwróconymi wartościami kluczy • Indeks oparty na klastrze jednej lub więcej tabel (B+ drzewo lub hasz), indeks haszowany • Indeks z pozycjami określonymi za pomocą wyrażeń • Indeks bitmapowy – implementacja dwa B+ drzewa PJWSTK, L. Banachowski, SZB 42

Plik nieuporządkowany - bez indeksu wewnętrznego - indeks zewnętrzny oparty na B+ drzewie v Pozycja danych składa się z wartości indeksowanych kolumn oraz z identyfikatora ROWID wiersza w tabeli - określającego fizyczne położenie danego wiersza na dysku. Umożliwia: – znalezienie wiersza w oparciu o wartość klucza wyszukiwania, – realizację zapytań zakresowych. v v Przy wykonywaniu zapytania system używa indeksu opartego na B+ drzewie tylko wtedy gdy jest zapewniona wystarczająca selektywność wyszukiwania, powiedzmy zwracane zostaje co najwyżej 5 do 10% wszystkich rekordów w pliku. Indeks zewnętrzny oparty na B+ drzewie jest automatycznie tworzony dla każdego klucza głównego i jednoznacznego. PJWSTK, L. Banachowski, SZB 43

Tabela połączona z indeksem głównym opartym na B+ drzewie v v v Pozycjami danych indeksu głównego są rekordy pliku tzn. wiersze tabeli są trzymane w indeksie. Jest zapewniony bardzo szybki dostęp do wierszy przez wartości klucza głównego. Wiersze nie posiadają swoich identyfikatorów ROWID – identyfikacja wierszy przebiega wyłącznie przez wartości klucza głównego; w pozostałych indeksach wynikiem wyszukania jest wartość klucza głównego – a nie ROWID. Odpowiada to koncepcji indeksu pogrupowanego - ale budowanego tylko dla klucza głównego. Budując osobno perspektywę zmaterializowaną można jej nadać inną organizację. PJWSTK, L. Banachowski, SZB 44

Tabela połączona z indeksem opartym na B+ drzewie Załóżmy, że chcemy dokonywać analizy klientów wyszukując klientów mieszkających w określonym mieście. • Miasta(Id_miasta, Nazwa_miasta) • Klienci(Id_miasta, Id_klien_w_miescie, Nazwisko, Hobby, Wiek) Jeśli tabela Klienci jest połączona z indeksem na swoim kluczu głównym, istotnie można przyśpieszyć wykonywanie zapytań w rodzaju: SELECT K. Nazwisko, . Hobby FROM Klienci K INNER JOIN Miasta M ON K. Id_miasta = M. Id_miasta WHERE M. Nazwa_miasta = 'WARSZAWA' AND K. Wiek BETWEEN 18 and 25; PJWSTK, L. Banachowski, SZB 45

Tworzenie tabeli połączonej z indeksem opartym na B+ drzewie CREATE TABLE Klienci( Id_miasta INTEGER, Id_klien_w_miescie INTEGER, Nazwisko VARCHAR 2(80), Hobby VARCHAR(20), Wiek INTEGER, CONSTRAINT Klienci_pk PRIMARY KEY (Id_miasta, Id_klien_w_miescie), CONSTRAINT Klienci_fk FOREIGN KEY(Id_miasta) REFERENCES Miasta ) ORGANIZATION INDEX; PJWSTK, L. Banachowski, SZB 46

Indeks haszowany Przykład zastosowania indeksu haszowanego dla klucza głównego Id_konta tabeli: • Konta(Id_konta, Saldo, Imie, Nazwisko, Adres) Zakładamy, że tabela Konta zawiera bardzo dużo wierszy oraz że często wielu kasjerów w banku równocześnie wykonuje zapytanie: SELECT * FROM Konta k WHERE k. Id_konta = : numer; PJWSTK, L. Banachowski, SZB 47

Tworzenie indeksu haszowanego CREATE CLUSTER Klast_konta(Id_konta INTEGER) SIZE 512 SINGLE TABLE HASHKEYS 100003 HASH IS mod(Id_konta, 100003); - lub HASH IS Id_konta Następnie definiujemy tabelę Konta: CREATE TABLE Konta(Id_konta INTEGER PRIMARY KEY, Saldo NUMBER, Imie VARCHAR 2(20), Nazwisko VARCHAR 2(50), Adres VARCHAR 2(70)) CLUSTER Klast_konta(Id_konta); PJWSTK, L. Banachowski, SZB 48

Tworzenie indeksu haszowanego v v Indeks klastra jest tworzony automatycznie. Parametr SIZE w definicji klastra określa ilość miejsca w bajtach przeznaczoną do zapisania rekordów z tą samą wartością klucza klastra. Domyślną wartością (gdy brak klauzuli SIZE) jest rozmiar strony dyskowej. Jeśli wszystkie wiersze dla danej wartości klucza klastra nie mieszczą się w jednym bloku, są zapisywane na liście nadmiarowych bloków. Poprzez dobór odpowiedniej wartości SIZE można spowodować, że wszystkie rekordy z daną wartością klucza znajdą się na tej samej lub tylko na kilku stronach dyskowych. PJWSTK, L. Banachowski, SZB 49

Sortowanie segmentów haszowanych CREATE CLUSTER call_detail_cluster ( telno NUMBER, call_timestamp NUMBER SORT, call_duration NUMBER SORT ) HASHKEYS 10000 HASH IS telno SIZE 256; CREATE TABLE call_detail ( telno NUMBER, call_timestamp NUMBER SORT, call_duration NUMBER SORT, other_info VARCHAR 2(30) ) CLUSTER call_detail_cluster ( telno, call_timestamp, call_duration ); Wyświetlić rozmowy wykonane z danego numeru w kolejności od najwcześniejszego. SELECT * WHERE telno = 6505551212 ORDER BY call_timestamp; PJWSTK, L. Banachowski, SZB 50

Przykład - słaba selektywność SELECT E. Ename FROM Emp E WHERE E. Job=: job; - ewentualnie z ORDER BY E. Ename 1. Klaster (indeks) haszowany z kluczem Emp. Job lub Emp(Job, Ename) – sortowane segmenty 2. Tabela połączona z indeksem głównym: Emp (Job, Empno, …) lub Emp (Job, Empno, Ename, …) 3. Zwykły indeks na E. Job może być zły ze względu na słabą selektywność 4. Indeks bitmapowy na E. Job PJWSTK, L. Banachowski, SZB 51