WYKONYWANIE ZAPYTAŃ

1. WYKONYWANIE ZAPYTAŃ • Przygotował Lech Banachowski na podstawie: • Raghu Ramakrishnan, Johannes Gehrke, Database Management Systems, McGrawHill, 2000 (książka i slide’y). • Lech Banachowski, Krzysztof Stencel, Systemy zarzadzania bazami danych, Wyd. PJWSTK, 2007.

2. Operatory relacyjne • Selekcja Selekcja podzbioru wierszy (klauzula WHERE). • Projekcja Pominięcie z wyniku niepotrzebnych kolumn (klauzula SELECT). • Złączenie Złączenie relacji (tabel), iloczyn kartezjański • Suma (UNION) Suma relacji (tabel), INERSECT,EXCEPT • Agregacja (SUM, MIN, itd.) i grupowanieGROUP BY. * Relacja = tabela

3. SELECT * FROMEmpE WHEREE.Ename=‘KING’-- lubE.Ename < 'C‘ Proste selekcje • Bez indeksu, nieposortowane: Koszt jest M = #stron w E. • Z indeksem na atrybucie selekcji: Użyj indeksu, wyznacz pozycje danych, przejdź do rekordów. • Najlepiej gdy indeks haszowany, selektywny, główny lub jednoznaczny dla selekcji równościowych oraz indeks pogrupowany dla selekcji zakresowych.

4. Użycie indeksu do selekcji • Gdy indeks niepogrupowany koszt jest proporcjonalny do liczby znalezionych rekordów co może być więcej niż M - liczba stron w E. • Ulepszenie dla niepogrupowanych indeksów: 1. Wyznacz odpowiednie pozycje danych. 2. Posortuj je względemrid. 3. Sprowadzaj rekordy w takim porządku. Każda potrzebna strona zostanie sprowadzona tylko raz.

5. STRATEGIE: TYLKO-INDEKS, PERSPEKTYWA ZMATERIALIZOWANA • W szczególnym przypadku gdy wszystkie elementy klauzul SELECT i WHERE należą do klucza wyszukiwania jednego indeksu – wystarczy przejść tylko ten indeks. • Czasami warto do klucza wyszukiwania dodać jedno lub więcej pól aby umożliwić zastosowanie tej metody np. do indeksu opartego na nazwisku pracownika możemy rozważyć dodanie zarobków i/albo numeru działu. • Zamiast indeksu można użyć perspektywy zmaterializowanej, szczególnie wtedy, gdy rozmiar wyniku jest większy.  • W przeciwieństwie do indeksu stosując perspektywę zmaterializowaną zachowujemy w niej wszystkie pseudo-wartości NULL.

6. Projekcja SELECTDISTINCT E.Job FROMEmp E • Bez DISTINCT – przepisanie. • Z DISTINCT – wymagane jest wyeliminowanie powtórzeń: • posortowanie; • haszowanie i eliminacja powtórzeń w ramach segmentów haszowania; • gdy atrybuty klauzuli SELECT tworzą indeks – wystarczy przejść tylko indeks (strategia tylko indeks).

7. Operatory zbiorowe • Przecięcie i iloczyn kartezjański relacji są specjalnymi przypadkami złączenia (z DISTINCT). • Union Distinct i Except są podobne do siebie. • Posortuj obie relacje (na kombinacji wszystkich atrybutów). • Dokonaj odpowiedniego scalenia wyników. • Alternatywa: Sortuj od razu razem obie relacje. • Zamiast sortowania można użyć haszowania. SELECT Deptno FROM DeptINTERSECTSELECT Deptno FROM Emp; = SELECT DISTINCT a.DeptnoFROM Dept a INNER JOIN Emp b ONa.Deptno=b.Deptno; SELECT DeptnoFROM DeptEXCEPTSELECT Deptno FROM Emp;

8. Operacje agregacji (AVG, MIN itd.) • Bez grupowania: • Na ogół trzeba rozważyć każdy wiersz. • Gdy jest indeks, którego klucz wyszukiwania obejmuje wszystkie atrybuty występujące w klauzulachSELECT i WHERE, wystarczy przejrzeć indeks (strategia tylko-indeks). • Z grupowaniem GROUP BY: • Posortuj względem wartości atrybutów GROUP BY, przejdź po rekordach w każdej grupie licząc wartości funkcji sumarycznych – w tym celu można użyć pogrupowany indeks na B+ drzewie. • Gdy jest indeks, którego klucz wyszukiwania obejmuje wszystkie atrybuty występujące w klauzulach SELECT, WHERE i GROUP BY, wystarczy przejrzeć indeks (strategia tylko-indeks). • Zamiast sortowania można użyć haszowania. • Perspektywa zmaterializowana jak w hurtowni danych.

9. Strategia tylko-indeks • Jej zastosowanie wymaga aby wszystkie potrzebne do wyznaczenia wyniku zapytania wiersze tabeli były indeksowane. • Indeks(Ename, Comm) SELECT e.Ename, e.CommFROM Emp eORDER BY e.Ename; • Indeks(Comm)SELECT Avg(e.Comm)FROM Emp e; SELECT e.EnameFROM Emp eWHERE e.Comm IS NULL; SELECT e.CommFROM Emp eWHERE e.Comm IS NOT NULL;

10. Kolumny INCLUDE (SQL Server) Ułatwia korzystanie z metody „Tylko indeks”. Opcja INCLUDE dodaje dodatkowe kolumny do pozycji danych w indeksie nie połączonym z tabelą. SELECT Imie, Nazwisko, Data_ur FROM Osoby WHERE Nazwisko = 'Kowalski‘ Indeks Osoby(Nazwisko),Kolumny INCLUDE: Imie i Data_ur. Wyszukiwanie po nazwisku, w pozycji danych odczytanie imienia i daty urodzenia bez sięgania do stron z rekordami. CREATE NONCLUSTEREDINDEX Osoby_tylko_indexON Osoby(Nazwisko) INCLUDE (Imie, Data_ur)

11. Złączenia równościowe z jedną kolumną złączenia SELECT E.Ename, D.LocFROM Emp E INNER JOIN Dept DON E.Deptno = D.Deptno; • Bezpośrednie podejście: generuj wszystkie kombinacje wierszy i stosuj selekcjęE.Deptno = D.Deptno. • M =#stron w E, pE =#wierszy na stronie dla E,N=#stron w D, pD =#wierszy na stronie dla D. • w przykładzie:E – Emp, D – Dept. Ogólniekolumny złączenia: Eiw E i Dj w D

12. Algorytm Nested Loops Join foreach roweinEdo foreach rowdinDdo ifei= dj then add <e, d> to result • Dla każdego wiersza zewnętrznej tabeli E, przeglądamy wszystkie wiersze wewnętrznej tabeliD. • Koszt (liczba sprowadzanych stron): M + M*N • Koszt mniejszy gdy M<N. • Metoda stosowana: • gdy M jest niewielkie (np. 2, 3). • do obliczania iloczynu kartezjańskiego (CROSS JOIN).

13. Algorytm Index Nested Loops Join • foreach row e in E do{weź wartość  eikolumny złączenia Ei i poprzez indeks na Dj wyznacz wszystkie wiersze d w D mające tę samą wartość w kolumnie złączenia Dj (dj = ei): • połącz oba takie wiersze <e,d> i • dodaj <e, d> do obliczanego wyniku}

14. Algorytm Index Nested Loops Join Indeks E Ei D Dj • Koszt: M + (M*pE) * (średni koszt wyznaczenia pasujących wierszy w Ddla jednego wiersza w E)

15. Algorytm Index Nested Loops Join • Dla każdego wiersza w E:średni koszt wyszukania pozycji danych w indeksie dla D jest: • ok. 1.2 dla indeksu haszowanego; • ok. 3 dla B+ drzewa. • Mając znalezioną pozycję danych, średni koszt wyznaczenia pasujących wierszy w D zależy od rodzaju indeksu: - indeks główny lub jednoznaczny: +1 • indeks pogrupowany: +1 We/Wy (zwykle) • indeks niepogrupowany: +1 We/Wy dla każdego pasującego wiersza w D • może być więcej niż liczba stron w D!

16. Klaster tabel Można się lepiej przygotować do często występujących złączeń tabel przez umieszczenie ich w jednym klastrze z kluczem będącym kolumną złączenia obu tabel. Wiersze, które są ze sobą złączane, znajdują się wtedy (zazwyczaj) na tej samej stronie dyskowej. Połączenie tabel w klaster powoduje, że złączenie odbywa się tak jakby to była pojedyncza operacja przejścia jednej tabeli. Realizacja naszego przykładowego zapytania zostanie przyśpieszona jeśli obie tabele Emp i Dept umieścimy w jednym klastrze. Koszt metody jest taki jak koszt przejścia pliku rekordów rozmiaru N+M.

17. Algorytm Sort-Merge Join • Posortuj D i E na kolumnach złączenia, następnie scal odpowiadające sobie wiersze w D i E. Przy scalaniu na ogół każda z posortowanych tabeli D i E jest przeglądana raz (liniowo). • Koszt algorytmu Sort Merge Join mierzony liczbą operacji We/Wy jest liniowy = N+Moperacji We-Wy – ze współczynnikiem ok. 5-7.

18. Oryginalne tabele Partycje 1 1 2 2 funkcja haszująca h1 . . . B-1 B-1 B - buforów w RAM Dysk Dysk Partycje E & D Wynik Tablica haszowana dla partycji i relacji E f. h. h2 h2 Bufor wyjściowy Bufor wej. dla partycji i rel. D B – buforów w RAM Dysk Dysk AlgorytmHash-Join Podziel obie tabele E i D na partycje względem wartości funkcji haszujacej h1 na kolumnach złączenia: wiersze E w partycji i wystarczy złączyć z wierszami D w partycji i. Wczytaj partycję i tablicy E dokonując haszowania przy pomocy f.h. h2 (<> h1!). Wczytując elementy partycji i w D, stosuj h2 i uzgadniaj z E.

19. Porównanie Sort-Merge Join i Hash Join • Obie metody mają porównywalny liniowy koszt. • Hash Join lepszy przy większej różnicy rozmiarów; łatwy do zrównoleglenia; zły w pesymistycznym przypadku – nie losowym. • Sort-Merge mniej wrażliwy na losowość danych; wynik posortowany.

20. Złączanie tabel obiektowo-relacyjnych • Przy złączaniu tabel obiektowo-relacyjnych możemy skorzystać z referencji i kolekcji referencji. • Obie operacje zarówno przejście przez referencję jak i przejście przez kolekcję referencji są szybsze niż odpowiednie operacje przejścia przez indeksy rozłączne dla tabel relacyjnych. • Wady referencji i kolekcji referencji: • dodatkowy narzut czasowy i miejsca na dysku związany z reprezentacją i przetwarzaniem referencji i kolekcji, • są wolniejsze niż tabele relacyjne z użyciem klastrów i indeksów połączonych z tabelą.

21. Podsumowanie - realizacja operatorów • Zaleta relacyjnych SZBD – zapytania złożone z kilkubazowych operatorów; implementacje tych operatorów można dokładnie dostroić. • Wiele alternatywnych metod implementacyjnych. • Dla konkretnego zapytania dla każdego występującego w nim operatora trzeba rozważyć dostępne opcje i wybrać najlepszą korzystając z dostępnych statystyk. Jest to zadanie optymalizacji zapytania.

22. Wykonywanie zapytań przez SZBD

23. Optymalizacja zapytań • Budowa drzewa zapytania odpowiadającego wyrażeniu algebry relacji danego zapytania. • Plan:Algorytm wykonania zapytania – w postaci drzewa. • Dla danego zapytania: jakie plany są rozpatrywane? • Jak oszacować koszt planu? • Idealnie: Chcemy znaleźć najlepszy plan.Praktycznie: Staramy się unikać złych planów!

24. Działanie w miejscu • Nie korzystanie z tymczasowych tabel. Przy określonym sposobie dostępu do rekordów każdej tabeli utrzymuje się tylko kursory przebiegające rekordy w plikach (ewentualnie pozycje danych w pliku indeksowym) bez zapisywania pomocniczych tabel. • Metody złączeniaSimple Nested Loops Join i Index Nested Loops Join umożliwiają działanie w miejscu. • Natomiast metody Sort-Merge Join i Hash Join wymagają użycia pomocniczych plików na dysku, więc nie działają w miejscu. • Zastosowanie klastra lub kolekcji referencji zamiast operatora złączenia też umożliwia działanie w miejscu.

25. Przetwarzanie potokowe • Wynik jednego operatora relacyjnego jest przekazywany na wejście drugiego operatora. Oznacza to, że nie jest potrzebna tymczasowa tabela, więc metoda ta umożliwia działanie w miejscu.

26. Drzewo instrukcji: Przykład SELECT E.EnameFROM Emp E INNER JOIN Dept D ON E.Deptno=D.DeptnoWHERE E.Mgr=100 AND D.Loc='Oz'; • Dostęp do tabel: Scan. • Działa w miejscu, przetwarza potokowo. • Cel optymalizacji: Wyznaczyć inne bardziej efektywne plany obliczenia tego samego wyniku. Plan 1: Scan Scan

27. Plan 2(bez użycia indeksu do złączania) • Główna różnica: selekcje wcześniej. • Zamiast SORT-MERGE JOIN mogłoby być HASH JOIN. • Dwie alternatywne metody dostępu do rekordów: • przez scan całej tabeli; • przez wyszukiwanie przez indeks odpowiednio na Emp.Mgr i Dept.Loc. • Nie działa w miejscu.

28. Ename Plan 3 (z metodą dostępu do tabel przez indeksy) Loc=‘Oz’ (Index Nested Loops) Deptno=Deptno • Najlepszybyłby indeks pogrupowany lub selektywny na Emp(Mgr), ewentualnie indeks haszowany. Dostęp do rekordów w Emp przez ten indeks. • INL bez zapisywania wyniku selekcji jako tymczasowej relacji- działa w miejscu. (Hash index; bez temp) Dept Mgr=100 Emp • Kolumna złączenia Deptno jest kluczem głównym dla tabeli Dept. Wystarczy indeks niepogrupowany.

29. D D C C D B A C B A B A Generowanie przez optymalizator planów wykonania zapytania • Generowanie równoważnych drzew wykonania zapytania. Analiza złączeń i ograniczenie do: drzew skierowanych w lewo. • Drzewa skierowane w lewo dają plany umożliwiające wykonanie zapytania “w miejscu” tj. bez tymczasowych plików. • Podstawa: przemienność i łączność operatora złączenia. • Analiza dostępu do poszczególnych tabel z możliwością zastowania indeksów.

30. Emp: Indeks haszowany na Emp.Mgr Indeks główny B+drzewo na Emp.Empno Scan Dept: Indeks B+ drzewo na Dept.Loc Scan Indeks główny haszowany na Dept.Deptno • Faza 1: Generowanierównoważnych drzewwykonania zapytania. • Faza 2: Analiza metod dostępu do tabel Emp i Dept: • Faza 3:Rozpatrujemy każde drzewo z Fazy 1 i każdą metodę dostępu z Fazy 2. Idąc od najniższych poziomów drzewa zapytania wybieramy metodę realizacji każdego operatora relacyjnego np. dla złączenia: NLJ,INLJ, SMJ, HJ, liczymy orientacyjny koszt korzystając ze statystyk zebranych przez system jak liczba wierszy, liczba stron dla plików z danymi i plików indeksów , selektywność wyszukiwania przez indeks. Powstaje możliwy plan wykonania zapytania razem z oszacowanym kosztem. • Faza 4: Spośród wszystkich wygenerowanych planów wykonania zapytania system wybiera plan o najmniejszym koszcie.

31. Podzapytania (w tym perspektywy lokalne inline) • Podzapytania są optymalizowane niezależnie. • Główne zapytanie jest optymalizowane z branym pod uwagę kosztem „wywoływanych” podzapytań. • Alternatywnie, sprowadzane do złączeń i optymalizowane łącznie.

32. Ogólne strategie optymalizacyjne · Wybierz plan wykonania działający “w miejscu” bez pomocniczych relacji (drzewa skierowane w lewo). • Wykonuj jednocześnie ciągi operacji jednoargumentowych takich jak selekcje i rzuty (realizacja potokowa). • Sprowadzaj przetwarzanie do operacji w pamięci RAM np. przez dokonanie jak najwcześniej selekcji, istotnie ograniczającej liczbę wierszy. ·    Gdy złączenia bez operatorów JOIN, staraj się związać selekcje z iloczynem kartezjańskim, w celu zidentyfikowania rodzaju złączenia relacji. ·Wyszukuj wspólne podwyrażenia i wykonuj je tylko raz. ·Przetwórz wstępnie plik (posortuj, wykonaj haszowanie). • Cały czas gromadź statystyki ilościowe dotyczące tabel, kolumn i indeksów, jak liczba stron, liczba różnych wartości w kolumnie. ·Dokonaj analizy możliwych opcji z oszacowaniem ich kosztu. • Rozważ możliwość użycia indeksów w tym bitmapowych. • Stosuj: • Klaster. • Strategię tylko indeks. • Perspektywę zmaterializowaną.