Na czym polegają operacje masowe i
jaka jest korzyść z ich stosowania? Najlepiej będzie pokazać to
na przykładzie. Na co dzień pracujemy z dużymi zbiorami danych, a
więc każda korzyść czasowa w ich przetwarzaniu będzie dla nas
znacząca. Przygotowuję więc całkiem sporą tabelkę będącą
kopią tabeli employees z przykładowego schematu HR. W dalszym
etapie 14-stokrotnie dubluję jej wielkość. W efekcie tabelka ma
ponad milion siedemset tysięcy wierszy, mamy więc na czym pracować.
Chcąc przetwarzać takie dane w PL/SQL
najpierw musimy je wczytać, robimy to przy pomocy pętli kursorowej.
W tym przykładzie ładuję wynik zapytania do tablicy zmiennych typu
wierszowego odpowiadających konstrukcyjnie wierszom tabeli bigstuff
(to właśnie z niej czytamy dane, wiersze kursora będą więc miały
taką samą konstrukcję). Przed i po załadowaniu danych wypisuję
na konsolę aktualny czas.
Po zakończeniu wykonywania skryptu na
konsoli wyświetlone zostały wyniki. Widzimy że ładowanie prawie 2
milionów wierszy trwało 26 sekund.
Teraz nieco zmodyfikuję kod:
Jak widać zamiast pętli kursorowej
(która przy okazji niejawnie otwiera i zamyka kursor) otwieram i
zamykam kursor jawnie, ale zamiast fetchowania pojedynczych wierszy,
fetchuję naraz cały kursor. W dodatku nie ma tu żadnej pętli...
Konstrukcja konstrukcją, ale sprawdźmy efekty:
Czas ładowania spadł z 26 do 5
sekund. Czyli aktualnie czas ładowania to 1/6 poprzedniego czasu
ładowania. Ale co się właściwie stało? Czy wiesz co się dzieje
podczas otwarcia kursora? Wbrew powszechnemu przekonaniu nie są
wtedy czytane dane. Na tym etapie wymyślany jest plan wykonania
zapytania. Możesz to sprawdzić np. jedynie otwierając i zamykając
kursor z zapytaniem na jakiejś wielkiej tabeli. Dane są czytane
dopiero podczas operacji FETCH. Zauważ że sama konstrukcja pętli
kursorowej powoduje FETCH pojedynczych wierszy. Co obrót pętli to
pobranie jednego wiersza. Co z tego wynika? Następuje ciągła
komunikacja pomiędzy bazą a PLSQL. Każdy kolejny wiersz musi
zostać przeczytany i załadowany do tablicy, następnie kolejny
wiersz jest czytany … itd. Długi czas wykonania wynika właśnie z
tej ciągłej komunikacji. Spójrzmy teraz na kod w którym używam
instrukcji BULK COLLECT. Nie ma tam żadnej pętli. Tym razem
pobranie danych działa w ten sposób, że czytany i wrzucany na stos
jest cały wynik zapytania. Następnie jako całość przekazywany do
tablicy. Nie następuje ciągła komunikacja, stąd zysk czasowy.
Za dobrze by jednak było, gdyby nie
wiązały się z tym pewne problemy. Jak można wywnioskować ze
sposobu działania operacji masowych, cały wynik zapytania musi
został załadowany do pamięci na raz. To oznacza że narażamy się
na przepełnienie pamięci w sytuacji gdyby wielkość wyniku
zapytania przerósł dostępną pamięć. Czyż nie? Sprawdźmy to.
Jeszcze czterokrotnie powiększę wielkość tabeli z której czytane
są dane:
W tej chwili tabela ma 7 milionów
wierszy... Przy próbie powtórzenia wcześniejszej operacji
ładowania danych dostaję taki oto komunikat:
Co mogę na to poradzić? Mogę
przetwarzać dane po kawałku. Np. po 500 tysięcy wierszy. Do tego
służy dodatkowa klauzula limit 500000. Muszę tylko zadbać
by załadować cały wynik, dodaję więc również pętlę która
będzie czytała dane po kawałku aż się skończą:
Oczywiście nie może się obyć bez
problemów :) System nie może kolekcjonować wszystkiego naraz do
jednej tablicy ponieważ ponownie doprowadziłoby to do przepełnienia
pamięci. Rozwiązano to w ten sposób, że każda kolejna porcja
nadpisuje poprzednią. W kodzie powyżej wypisywałem na ekran ilość
niepustych elementów w tablicy przy użyciu tab.count. To mi daje
obraz na temat tego ile znajduje się elementów w tablicy po każdym
fetchu z limitem. Wynik możemy obejrzeć poniżej:
Z tego wynika że takie załadowane
dane trzeba od razu przetwarzać, ponieważ przy kolejnym fetchu
zostaną nadpisane.
Spostrzegawczy zauważą że czas
przetwarzania wynosi znowu 26 sekund... Pamiętajcie jednak że teraz
przetwarzamy nie 2 a 7 milionów wierszy ;)
Operacje masowe możemy również
wykorzystywać przy operacjach DML. W końcu kiedy już skończymy
przetwarzać dane, warto je gdzieś wrzucić.
Tworzę pustą tabelę na wyniki:
Do wrzucania danych zastosuję
poprzednio załadowane dane, oczywiście mogą to być jakiekolwiek
inne dane załadowane do tablicy. Najpierw przeprowadzam test z
użyciem tradycyjnej metody tj. iteracji po tablicy i insertów
wiersz po wierszu. Tym razem również przed całą operacją i po
jej zakończeniu wypisuję czas na ekranie.
Całe załadowanie 7 milionów wierszy,
a następnie ich wstawienie do tabeli trwało 7 minut i 13 sekund.
Ponownie modyfikuję kod :
Pojawiła się nowa instrukcja :
FORALL. Działa podobnie jak BULK COLLECT przy ładowaniu danych do
tablicy, tylko w drugą stronę. Cała tablica jest jednocześnie
przekazywana do insertu. Ale jak wygląda zysk czasowy?
Zamiast 7 minut i 14 sekund mamy 2
minuty i 8 sekund. W sumie przy użyciu operacji masowych zyskaliśmy
- przy ładowaniu danych do tablicy 1/6 czasu
- przy wrzucaniu danych do bazy ok 1/3 czasu
Razem daje nam to całkiem przyzwoity
wynik :)
