Połączenie (1)
" Operacja binarna  zawsze udział biorą dwie tabele, jedna zostaje
nazwana tabelą zewnętrzną, druga tabelą wewnętrzną.
" W przypadku polecenia łączącego więcej niż dwie tabele (np. A , B i
C), połączenie realizowane jest zawsze dla pary tabel (np. A z B,
Optymalizacja poleceń SQL
wynik z C, albo A z C i wynik z B, itd.).
" Podstawowe zasady:
Część 3.
" główna zasada: kolejność łączenia tabel powinna jak najbardziej
ograniczać zbiór rekordów
Metody połączeń,
" optymalizator szuka w zbiorze łączonych tabel takich, których
metody sortowania, wskazówki
połączenie wyprodukuje 1 rekord  jeśli znajdzie, te tabele są
łączone na początku
" w przypadku połączenia zewnętrznego tabela zewnętrzna jest
umieszczana w kolejności za tabelą wewnętrzną
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
1 2
Połączenie (2) Algorytm nested loops
" Realizowane przy użyciu jednego z algorytmów: " Stosowany, gdy:
" nested loops, " w połączeniu bierze udział mała część rekordów relacji,
" sort merge, " istnieje efektywna metoda dostępu do danych relacji wewnętrznej
(indeks założony na kolumnie w warunku połączeniowym).
" hash join.
" Główny koszt  koszt odczytu rekordów relacji zewnętrznej i
" Wybór algorytmu zależy od:
znalezienia odpowiadających rekordów relacji wewnętrznej.
" rozmiaru tabeli,
Relacja zewnętrzna Relacja wewnętrzna
" Algorytm:
" postaci warunku połączeniowego,
2 a
A 3 2 a
" spodziewanego rozmiaru wyniku połączenia,
1 b
B 2 1 b
" dostępności i rozmiaru obszaru sortowania,
2 c
2 c
C 1
3 d
3 d
" wartości parametru odczytu wieloblokowego D 3
" W planie wykonania
1 e
1 e
(DB_FILE_MULTIBLOCK_READ_COUNT).
relacja zewnętrzna
ponad relacją wewnętrzną:
A 3 3 d
Wynik połączenia
NESTED LOOPS
B 2 2 a
relacja_zewnętrzna
B 2 2 c
relacja_wewnętrzna
C 1 1 b
C 1 1 e
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej D 3 3 d
3 4
Algorytm sort merge (1) Algorytm sort merge (2)
A 3 2 a
A 3 2 a
" Stosowany, gdy:
B 2 1 b
B 2 1 b
" łączone relacje są niezależne (brak połączenia kluczem obcym), 2 c
C 1
C 1 2 c
D 3 3 d
D 3 3 d
" warunek połączeniowy z operatorami: <, <=, >, >= (ale nie !=) i duże
1 e
1 e
rozmiary łączonych relacji (zachowuje się lepiej niż nested loop),
" relacja już są posortowane lub nie ma potrzeby realizacji sortowania
sortowanie
(bo np. istnieje odpowiedni indeks).
C 1 1 b
C 1 1 b
" Główny koszt  koszt wczytania obu relacji do pamięci i ich
B 2 1 e
B 2 1 e
posortowania.
A 3 2 a
A 3 2 a
D 3 2 c
D 3 2 c
" Brak podziału na relację zewnętrzną i wewnętrzną.
3 d
3 d
" Algorytm:
złączenie
1. Posortowanie obu relacji ze względu na wartości kolumn w warunku
C 1 1 b
C 1 1 b
połączeniowym.
C 1 1 e
C 1 1 e
2. Połączenie rekordów o tych samych wartościach kolumn w warunku
B 2 2 a
B 2 2 a
B 2 2 c
połączeniowym. B 2 2 c
A 3 3 d
A 3 3 d
D 3 3 d
D 3 3 d
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
5 6
Algorytm hash join (1) Algorytm hash join (2)
" Algorytm:
" Stosowany, gdy:
" warunek połączeniowy jest warunkiem równościowym, i
" łączone relacje o dużym rozmiarze lub większa część rekordów
Funkcja_haszująca=
mniejszej relacji bierze udział w połączeniu.
kolumna_połączeniowa mod 3
" Główny koszt  zbudowanie tabeli haszowej dla relacji zewnętrznej i
Tablica haszowa
Relacja wewnętrzna
odczyt rekordów z relacji wewnętrznej. Relacja zewnętrzna
A 3
A 3
2 a
2 a
0
" Relacja zewnętrzna  mniejsza z relacji, najlepiej, jeśli mieści się w A 3
A 3
D 3
D 3
1 b
1 b
B 2
pamięci. B 2
2 c
2 c
C 1
C 1
1
C 1
C 1
3 d
3 d
" W planie wykonania pierwsza relacja, z której zbudowano tablicę
D 3
D 3
1 e
1 e
haszową: B 2
B 2
2
HASH JOIN Wynik
relacja_zewnętrzna
B 2 2 a
B 2 2 a
relacja_wewnętrzna
C 1 1 b
C 1 1 b
B 2 2 c
B 2 2 c
A 3 3 d
A 3 3 d
D 3 3 d
D 3 3 d
C 1 1 e
C 1 1 e
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
7 8
FH
Operacje sortowania (1) Operacje sortowania (2)
" SORT ORDER BY  gdy w poleceniu wyrażenie ORDER BY. " SORT (HASH) UNIQUE  gdy w poleceniu użyto klauzuli DISTINCT.
SELECT * FROM zespoly
SELECT DISTINCT etat
ORDER BY adres DESC;
FROM pracownicy;
" SORT AGGREGATE  gdy w poleceniu wyliczana funkcji grupowa Uwaga!
na całym zbiorze rekordów.
Nie można zakładać uzyskania posortowanego zbioru rekordów
przy operacjach GROUP BY i DISTINCT.
SELECT MAX(zatrudniony)
FROM pracownicy;
" SORT JOIN  przy wykonywaniu operacji połączenia wg algorytmu
sort merge.
" SORT (HASH) GROUP BY  gdy w poleceniu wyliczana funkcji
SELECT *
grupowa dla kilku grup rekordów.
FROM pracownicy JOIN etaty ON placa_pod between
SELECT etat, AVG(placa_pod) placa_min and placa_max;
FROM pracownicy GROUP BY etat;
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
9 10
Zmienne wiązania w poleceniu SQL (1) Zmienne wiązania w poleceniu SQL (2)
" Pozwalają na  sparametryzowanie polecenia " Umożliwiają wielokrotne użycie tego samego planu wykonania przy
SQL> variable zespol number; kolejnych wywołania polecenia z różnymi wartościami zmiennej
SQL> exec :zespol := 10
wiązania  tzw.  współdzielenie kursora (domyślne działanie)
Procedura PL/SQL została zakończona pomyślnie.
" Przy pierwszym wywołaniu polecenia ze zmienną wiązania
SQL> print :zespol
optymalizator  spogląda na wartość zmiennej celem
ZESPOL
------ wygenerowania optymalnego planu
10
" Problem  kolejne wywołania tego samego polecenia z innymi
SQL> SELECT count(*) FROM PRACOWNICY WHERE id_zesp = :zespol;
wartościami dla zmiennej wiązania mogą przetwarzać dane o innej
COUNT(*)
charakterystyce niż te z pierwszego wywołania
--------
2
" Rozwiązanie  optymalizator obserwuje kolejne wywołania i
SQL> exec :zespol := 20
podejmuje decyzje, czy dla kolejnego wywołania polecenia z inną
Procedura PL/SQL została zakończona pomyślnie.
wartością zmiennej wiązania wygenerować nowy plan
SQL> SELECT count(*) FROM PRACOWNICY WHERE id_zesp = :zespol; " Efekt  być może wiele planów wykonania dla tego samego
COUNT(*)
polecenia
--------
7
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
11 12
Wskazówki (1) Wskazówki (2)
" Wskazówki (ang. hints) umożliwiają określenie bezpośrednio w " Wybór celu optymalizacji:
poleceniu następujących elementów pracy optymalizatora:
" ALL_ROWS  przepustowość,
" celu optymalizacji, " FIRST_ROWS  czas odpowiedzi (wycofywana od Oracle10g),
" FIRST_ROWS(n)  czas odpowiedzi (pierwszych n krotek).
" ścieżki dostępu do danych,
" kolejności łączonych relacji przy operacji połączenia,
" Sposób dostępu do danych:
" sposobu realizacji połączenia
" FULL (tabela)  pełne przeglądnięcie tabeli,
" Wskazówki umieszcza się w komentarzu bezpośrednio po
" INDEX (tabela [indeks])  dostęp za pomocą indeksu,
klauzulach SELECT, INSERT, UPDATE, DELETE, przy czym
" NO_INDEX(tabela [indeks])  zakazanie użycia indeksu,
pierwszym znakiem wskazówki musi być + (plus).
" INDEX_COMBINE (tabela [indeks])  dostęp za pomocą indeksu
SELECT /*+ wskazówka */ & FROM & ;
bitmapowego,
SELECT --+ wskazówka
" INDEX_DESC (tabela [indeks])  dostęp za pomocą odwróconego
przeszukiwania indeksu,
& FROM & ;
" Uwaga! Błędnie sformułowana wskazówka nie powoduje błędu
wykonania polecenia  jest ignorowana!
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
13 14
Wskazówki (3) Wskazówki (4)
" Sposób dostępu do danych (cd): " Algorytm łączenia relacji:
" USE_NL(tabela_wewnętrzna ...) - połączenie NESTED LOOPS
" INDEX_FFS (tabela [indeks])  dostęp za pomocą szybkiego
przeszukania indeksu, " USE_HASH (tabela_wewnętrzna ...) - połączenie HASH JOIN
" USE_MERGE (tabela1 tabela2 ...) - połączenie SORT MERGE
" NO_INDEX_FFS(tabela [indeks])  zakazanie użycia szybkiego
przeszukania indeksu, " NO_USE_NL(...), NO_USE_HASH(...), NO_USE_MERGE(...)  zakaz
użycia odpowiedniego algorytmu.
" INDEX_SS (tabela [indeks])  dostęp za pomocą przeglądnięcia indeksu
z pominięciem kolumn,
" NO_INDEX_SS (tabela [indeks])  zakazanie użycia przeglądnięcia " Inne:
indeksu z pominięciem kolumn,
" USE_CONCAT  wymuszenie zastąpienia zapytania z warunkiem
" INDEX_JOIN (tabela [indeks] & )  wykonanie połączenia indeksów,
złożonym z operatorem OR przez kilka zapytań, połączonych
operatorem UNION_ALL,
" Kolejność łączenia relacji: " NO_EXPAND  zabronienie wykonania powyższej transformacji.
" LEADING(tabela1 tabela2 ...)  określa zbiór tabel, które mają być
" NO_QUERY_TRANSFORMATION  zakazanie wszystkich transformacji
łączone jako pierwsze,
polecenia (przed budową planu wykonania)
" ORDERED  określa, że tabele mają być łączone w takiej kolejności, jak
" DYNAMIC_SAMPLING(tabela poziom_próbkowania)  określenie
zostały wymienione w klauzuli FROM.
poziomu dynamicznego próbkowania (wyższy poziom  większy zakres
próbkowania, zakres: 0-10)
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
15 16
Wskazówki (5)
" A
ączenie wskazówek:
SELECT /*+ LEADING(p e) USE_MERGE(p e z) */ *
FROM pracownicy p NATURAL JOIN zespoly z JOIN ETATY e
ON placa_pod between placa_min and placa_max
WHERE nazwa = 'ALGORYTMY';
(c) Instytut Informatyki Politechniki Poznańskiej
17