19OptymalizacjaSQL czesc 3

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.).

Część 3.

•

Podstawowe zasady:

• 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ą

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

• hash join.

(indeks założony na kolumnie w warunku połączeniowym).

•

Wybór algorytmu zależy od:

•

Główny koszt – koszt odczytu rekordów relacji zewnętrznej i

• rozmiaru tabeli,

znalezienia odpowiadających rekordów relacji wewnętrznej.

Relacja zewn

• postaci warunku połączeniowego,

•

Algorytm:

ętrzna

Relacja wewnętrzna

• spodziewanego rozmiaru wyniku połączenia,

• dostępności i rozmiaru obszaru sortowania,

• wartości parametru odczytu wieloblokowego

•

W planie wykonania

(DB_FILE_MULTIBLOCK_READ_COUNT).

relacja zewnętrzna

ponad relacją wewnętrzną:

NESTED LOOPS

Wynik połączenia

relacja_zewnętrzna

relacja_wewnętrzna

Algorytm sort merge (1)

Algorytm sort merge (2)

•

Stosowany, gdy:

•

łączone relacje są niezależne (brak połączenia kluczem obcym), C

•

warunek połączeniowy z operatorami: <, <=, >, >= (ale nie !=) i duże D

rozmiary łączonych relacji (zachowuje się lepiej niż nested loop), 1

•

relacja już są posortowane lub nie ma potrzeby realizacji sortowania sortowanie

(bo np. istnieje odpowiedni indeks).

•

Główny koszt – koszt wczytania obu relacji do pamięci i ich B

posortowania.

•

Brak podziału na relację zewnętrzną i wewnętrzną.

•

Algorytm:

złączenie

1. Posortowanie obu relacji ze względu na wartości kolumn w warunku C

połączeniowym.

2. Połączenie rekordów o tych samych wartościach kolumn w warunku B

połączeniowym.

Algorytm hash join (1)

Algorytm hash join (2)

•

Stosowany, gdy:

•

Algorytm:

• warunek połączeniowy jest warunkiem równościowym, i

• łączone relacje o dużym rozmiarze lub większa część rekordów mniejszej relacji bierze udział w połączeniu.

Funkcja_haszująca=

kolumna_połączeniowa mod 3

•

Główny koszt – zbudowanie tabeli haszowej dla relacji zewnętrznej i Tablica haszowa

odczyt rekordów z relacji wewnętrznej.

Relacja wewnętrzna

Relacja zewnętrzna

•

Relacja zewnętrzna – mniejsza z relacji, najlepiej, jeśli mieści się w A

pamięci.

•

W planie wykonania pierwsza relacja, z której zbudowano tablicę C

haszową:

HASH JOIN

Wynik

relacja_zewnętrzna

2 2

relacja_wewnętrzna

1 1

2 2

3 3

1 1

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;

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

•

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

--------

•

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

--------

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),

• ścieżki dostępu do danych,

• FIRST_ROWS(n) – czas odpowiedzi (pierwszych n krotek).

• kolejności łączonych relacji przy operacji połączenia,

• sposobu realizacji połączenia

•

Sposób dostępu do danych:

•

Wskazówki umieszcza się w komentarzu bezpośrednio po

•

FULL ( tabela) – pełne przeglądnięcie tabeli, klauzulach SELECT, INSERT, UPDATE, DELETE, przy czym

• INDEX ( tabela [indeks]) – dostęp za pomocą indeksu, pierwszym znakiem wskazówki musi być + (plus).

• NO_INDEX( tabela [indeks]) – zakazanie użycia indeksu,

• 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

… FROM …;

przeszukiwania indeksu,

•

Uwaga! Błędnie sformułowana wskazówka nie powoduje błędu wykonania polecenia – jest ignorowana!

Wskazówki (3)

Wskazówki (4)

•

Sposób dostępu do danych (cd):

•

Algorytm łączenia relacji:

• INDEX_FFS ( tabela [indeks]) – dostęp za pomocą szybkiego

• USE_NL( tabela_wewnętrzna ...) - połączenie NESTED LOOPS

przeszukania indeksu,

• USE_HASH ( tabela_wewnętrzna ...) - połączenie HASH JOIN

• NO_INDEX_FFS( tabela [indeks]) – zakazanie użycia szybkiego

• USE_MERGE ( tabela1 tabela2 ...) - połączenie SORT MERGE

przeszukania indeksu,

• NO_USE_NL(...), NO_USE_HASH(...), NO_USE_MERGE(...) – zakaz

• INDEX_SS ( tabela [indeks]) – dostęp za pomocą przeglądnięcia indeksu użycia odpowiedniego algorytmu.

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)

Wskazówki (5)

•

Łą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';