Kolekcje w Oracle część II: przetwarzanie masowe

Dominik Niewiadomy Anna Kowalczyk-Niewiadomy

W poprzednim numerze ORACLE’owych PLOUG’tek przedstawiliśmy podstawowe informacje dotyczące gromadzenia i przetwarzania danych przy pomocy kolekcji w bazie danych Oracle. Pokazaliśmy sposoby przetwarzania danych za pomocą tablic zagnieżdżonych (ang. nested tables), tablic asocjacyjnych (ang. associative array) oraz tablic o zmiennej długości (ang. varray). W kolejnej odsłonie artykułu skupimy się na przetwarzaniu masowym, ilustrując co to jest „context switching” oraz po co programistom „bulkowanie” (bulk z ang. masowy, hurtowy, zbiorowy) w PL/SQL. Zacznijmy zatem od postaw.

Przełączanie kontekstu przetwarzania (context switching)

Ogólna zasada, a zarazem główna zaleta działania bulkowego przetwarzania jest bardzo prosta i sprowadza się do wymiany informacji między SQLem i PL/SQLem za pomocą paczek danych (batch z ang. paczka, grupa, porcja), a nie pojedynczych rekordów. Ten typ interakcji z bazą danych do PL/SQLa został wprowadzony w Oracle RDBMS w wersji 8i, mimo iż wcześniej był dostępny w PRO*C i jako element pakietu DBMS_SQL. Na wstępie rozważmy przypadek, w którym naszym zadaniem jest przepisanie danych z tabeli CENY_NETTO do tabeli CENY_BRUTTO. Zadanie to można w prosty i szybki sposób wykonać poleceniem:

01> insert into CENY_BRUTTO select cnt_netto * 1.23 from CENY_NETTO.

Dla zilustrowania pewnych cech PL/SQLa można podejść do niego zgoła inaczej, poprzez przeiterowanie po całej tabeli CENY_NETTO:

01> BEGIN
02> FOR rekord IN ( SELECT cnt_netto FROM CENY_NETTO ) LOOP
03> rekord.cnt_netto := rekord.cnt_netto * 1.23;
04> INSERT INTO CENY_BRUTTO VALUES (rekord.cnt_netto);
05> END LOOP;
06> END;

Jak łatwo się domyślić pierwsza metoda powinna być tą, którą odruchowo wykorzystacie w takiej sytuacji, bo jak wiadomo od dawna, w prostocie siła. Natomiast powyższy przykład ma za zadanie zilustrować, w jaki sposób silniki SQL i PL/SQL muszą wymieniać się informacjami, w celu poprawnego wykonania tak zdefiniowanego zadania. Podstawowym ograniczeniem jest fakt, iż SQL i PL/SQL w obrębie jednego ciągu operacji nie mogą pracować równocześnie. Przejście z jednego trybu pracy do drugiego to tzw. przełączanie kontekstu (ang. context switching).

W powyższym kodzie podstawowym kontekstem jest kontekst PL/SQL, a kontekstem pomocniczym jest SQL. Na wstępie podstawowy kontekst wymusza przejście po tabeli, a dokładniej rzecz ujmując, wymusza na pomocniczym kontekście pobranie danych rekord po rekordzie. Tak pobrany rekord jest zwracany przez kontekst pomocniczy do kontekstu głównego w celu dalszego przetwarzania. W momencie ustawienia wartości brutto w rekordzie tymczasowym, znowu następuje zmiana kontekstu i przekazanie wartości z PL/SQLa do SQL, gdzie następuje wstawienie nowego rekordu do tabeli i ponowne przełączenie kontekstu. Jak łatwo zauważyć, na potrzeby przetworzenia pojedynczego rekordu niezbędnych jest kilka cykli zmian kontekstu, co jest dużym obciążeniem dla bazy danych. Zważywszy, że operacja ta jest powtarzana dla każdego rekordu tak długo, aż nie skończą się dane w tabeli CENA_NETTO. Koszt wykonania tego prostego zadania jest zatem znaczący z uwagi na liczną konieczność zmian kontekstów.

Czemu o tym wszystkim napisaliśmy? Rozwiązanie jest proste – podstawowym celem przetwarzania bulkowego jest redukcja ilości przełączeń między kontekstami. Przetwarzanie wsadowe w takim przypadku pozwala na pobranie całego zestawu danych, bądź paczki danych, a nie pojedynczego rekordu w ramach pracy jednego kontekstu. W kolejnym kroku na paczce danych wykonywane są operacje i z powrotem cała paczka jest zwracana do docelowego przetworzenia. Takie podejście skutkuje znaczącą redukcją ilości przełączeń kontekstu, co w większości przypadków skutkuje wymierną poprawą wydajności. Co więcej, to podejście w szczególności z operacjami DMLowymi daje jeszcze większy wzrost wydajności. Wynika to z faktu, iż przetwarzanie bulkowe korzysta z mniejszej ilości zasobów, niż DML przetwarzany na poziomie pojedynczych rekordów.

O pobieraniu masowym (bulk fetch) słów kilka

Nawiązując do wstępu, zaprezentujemy metodę pobierania danych z SQL do PL/SQL bez konieczności przeskakiwania międzykontekstowego podczas przetwarzania poszczególnych rekordów. Pobieranie wsadowe umożliwia zaciągnięcie danych z kursora paczkami bądź całościowo do wcześniej zadeklarowanej tablicy. Poniższy przykład pokazuje sposób, jak wydajnie przeiterować po rekordach z tabeli osoby, wykonać dowolne przetwarzanie, a następnie pokazać ile danych zostało ściągniętych i w jakim czasie ta operacja została wykonana.

01> declare
02> -- deklaracja typu tablicowego
03> TYPE tab_osoby IS table of osoby.nazwisko%TYPE index by pls_integer;
04> -- deklaracja zmiennej typu tablicowego
05> v_tab_osoby tab_osoby;
06> v_start_time PLS_INTEGER := DBMS_UTILITY.GET_TIME;
07> v_end_time PLS_INTEGER;
08>
09> BEGIN
10> SELECT nazwisko BULK COLLECT INTO v_tab_osoby FROM osoby;
11> FOR i IN 1 .. v_tab_osoby .COUNT LOOP
12> NULL; /*dowolne przetwarzanie*/
13> END LOOP;
14> v_end_time := DBMS_UTILITY.GET_TIME - v_start_time;
15> DBMS_OUTPUT.PUT_LINE ('Przetworzono ' || TO_CHAR (v_tab_osoby.COUNT ) || ' rekordów w czasie ' ||TO_CHAR ( ROUND ( v_end_time/100,2 )) ||' sec.');
16> END;

Rezultatem wykonanego skryptu jest:

01> Przetworzono 22937 rekordow w czasie 0,18 sec.

Jak widać przetwarzanie bulkowe wymaga od nas stworzenia bufora na dane pobrane z kontekstu SQLowego. Linie 3 i 5 definiują typ i deklarują zmienną v_tab_osoby, do której dane z zapytania będą zapisywane. Jak widać, typ tab_osoby jest typem tablicy przechowującym typ kolumny naszego źródła danych (%type). Kluczowa dla dalszego przetwarzania jest linia 10, w której za pomocą klauzuli BULK COLLECT INTO przerzucamy cały zestaw danych z kontekstu SQL do kontekstu PL/SQL usuwając tym konieczność późniejszych przeskoków kontekstowych. Dalsze przetwarzanie przebiega analogicznie do przetwarzania tablic tworzonych klasycznie. Poniższy przykład zawiera dokładnie tę samą funkcjonalność, lecz nie korzysta z walorów przetwarzania masowego.

01> declare
02> v_start_time PLS_INTEGER := DBMS_UTILITY.GET_TIME;
03> v_end_time PLS_INTEGER;
04> v_counter PLS_INTEGER := 0;
05> BEGIN
06> FOR osoba IN ( SELECT nazwisko FROM osoby ) LOOP
07> v_counter := v_counter + 1;
08> /*dowolne przetwarzanie*/
09> END LOOP;
10> v_end_time := DBMS_UTILITY.GET_TIME - v_start_time;
11> DBMS_OUTPUT.PUT_LINE ('Przetworzono ' || TO_CHAR (v_counter ) ||' rekordow w czasie ' ||TO_CHAR (ROUND ( v_end_time/100,2 )) ||' sec.');
12> END;

Rezultatem wykonanego skryptu jest:

01> Przetworzono 22937 rekordow w czasie 0,31 sec.

Przykład bez bulk collect przedstawiony powyżej cechuje się innym czasem wykonania, co już w tym momencie pokazuje zysk metody mimo braku dodatkowego obciążenia DMLem.

Limitowane pobieranie masowe (bulk fetch limit)

Jak widać w przykładzie pierwszym dotyczącym bulk collect, wszystkie dane zostały pobrane do kolekcji w ramach jednej iteracji. Jeśli tabela jest duża, bądź jeśli celowo chcemy wprowadzić grupowanie danych w mniejsze paczki, należy skorzystać z klauzuli LIMIT. Klauzula ta pozwala na ograniczenie maksymalnej wielkości paczki do ściśle określonej liczby elementów. Jeśli tabela miałaby 10 milionów rekordów i chcielibyśmy umieścić ją w pamięci za pomocą jednego BULK COLLECTA, mogłoby się to zakończyć dla nas tragicznie. Możliwość limitowania to bardzo ważna cecha z punktu widzenia zarządzania wydajnością, gdyż daje nam szansę zapanowania nad ilością wykorzystywanej pamięci poprzez wielkość kolekcji, w których składowane są dane. Nie da się z góry określić optymalnego limitu danych, ponieważ jest to całkowicie zależne od konkretnego zadania, którego przetwarzanie dotyczy. W zadaniach o niewielkiej liczbie operacji DML limit należy wysterować profilując zużycie pamięci. Natomiast przymuje się, że w zadaniach intensywnie wykorzystujących DML wielkość paczki powinna być w zakresie od 250 do 1000 rekordów. Poniżej przedstawiony został przykład wykorzystania bulk collecta z limitem sterowanym stałą C_BATCH.

01> DECLARE
02> TYPE tab_imion IS TABLE OF osoby.imie%TYPE INDEX BY BINARY_INTEGER;
03> TYPE tab_nazwisk IS TABLE OF osoby.nazwisko%TYPE INDEX BY BINARY_INTEGER;
04> imiona tab_imion;
05> nazwiska tab_nazwisk;
06> v_counter PLS_INTEGER := 0;
07> CURSOR kur IS SELECT imie, nazwisko FROM osoby ORDER BY nazwisko, imie;
08> C_BATCH CONSTANT PLS_INTEGER := 250;
09> BEGIN
10> OPEN kur;
11> LOOP
12> FETCH kur BULK COLLECT INTO imiona,nazwiska LIMIT C_BATCH;
13> IF nazwiska.COUNT > 0 THEN
14> v_counter := v_counter + nazwiska.COUNT;
15> /* dowolne przetwarzanie */
16> END IF;
17> EXIT WHEN kur%NOTFOUND;
18> END LOOP;
19> CLOSE kur;
20> DBMS_OUTPUT.PUT_LINE ('Przetworzono ' || TO_CHAR (v_counter )||' rekordow po: '||c_batch ||'. ');
21> END;

Powyższy przykład prezentuje przetwarzanie grupowe paczkami po 250 elementów. Wielkość paczki została zadeklarowana jako stała C_BATCH w linii 8 i została podana jako opcjonalny parametr LIMIT klauzuli BULK COLLECT w linii 12. Należy zwrócić uwagę na fakt, iż BULK COLLECT nie wspiera tablic/kolekcji rekordów, dlatego należy dla każdej kolumny z zapytania wykonać osobną zmienną na przepisanie wartości (widoczne w liniach 4 i 5). Dodatkowo należy zwrócić uwagę na fakt, iż poszczególne kolumny zapytania zostały pobrane kursorem, paczkami do osobnych kolekcji. Każdorazowe pobranie paczki do kolekcji nadpisuje poprzednią jej wartość, zatem nie ma konieczności czyszczenia kolekcji manualnie. Bardzo ważną cechą takiego przetwarzania jest fakt, iż jeśli kursor nie pobierze pełnego limitu, to spowoduje to spełnienie warunku wyjścia zaprezentowanego w linii 17. Należy zatem pamiętać, iż całe przetwarzanie powinno zostać wykonane przed weryfikacją warunku wyjścia. Jeśli natomiast chciałbyś umieścić warunek wyjścia przed wykonaniem przetwarzania, zastosuj metodę sprawdzającą czy pobrana paczka zawiera rekordy, analogicznie do warunku z linii 13.

Masowe przetwarzanie DML (forall DML)

Prezentowane do tej pory przykłady ograniczały się wyłącznie do masowego pobierania danych. Klauzula FORALL umożliwia masowe wysyłanie paczek danych do SQLa z poziomu PL/SQLa korzystając z operacji DMLowych. Aby wykonać taką operację należy posiadać tablicę, po której można iterować z jednoczesnym przypisaniem operacji DML – takich jak insert, update, delete. Poniżej zaprezentowany zostanie przykład wykorzystania klauzuli FORALL. Na potrzeby prezentacji wykorzystania forall’a należy stworzyć tabelę kopia_osoby, zawierającą dane do modyfikacji:

01> CREATE TABLE kopia_osoby AS SELECT idosoby, nazwisko FROM osoby where rownum<=1000;

Następnie należy wykonać skrypt wykorzystujący masową modyfikację:

01> DECLARE
02> CURSOR kur IS SELECT idosoby, nazwisko FROM osoby where rownum<=1000
03> TYPE tab_ids IS TABLE OF osoby.idosoby%TYPE INDEX BY BINARY_INTEGER;
04> TYPE tab_nazwisk IS TABLE OF osoby.nazwisko%TYPE INDEX BY BINARY_INTEGER;
05> TYPE typ_osoby_rec IS RECORD(ids tab_ids, nazwiska tab_nazwisk);
06> os_rec typ_osoby_rec;
07> v_fetched PLS_INTEGER := 0;
08> v_updated PLS_INTEGER := 0;
09> C_BATCH CONSTANT PLS_INTEGER := 500;
10> BEGIN
11> OPEN kur;
12> LOOP
13> FETCH kur BULK COLLECT INTO os_rec.ids, os_rec.nazwiska LIMIT C_BATCH;
14> IF os_rec.nazwiska.COUNT > 0 THEN
15> v_fetched := v_fetched + os_rec.nazwiska.COUNT;
16> FORALL i IN os_rec.nazwiska.FIRST ..os_rec.nazwiska.LAST
17> UPDATE kopia_osoby SET nazwisko = UPPER(os_rec.nazwiska(i))
18> WHERE idosoby = os_rec.ids(i);
19> v_updated := v_updated + SQL%ROWCOUNT;
20> END IF;
21> EXIT WHEN kur%NOTFOUND;
22> END LOOP;
23> CLOSE kur;
24> DBMS_OUTPUT.PUT_LINE ('Przetworzono: ' || TO_CHAR ( v_fetched ) ||' rekordow. Zmodyfikowano: ' ||TO_CHAR ( v_updated ) ||' rekordow.' );
25> END;

Powyżej zaprezentowany skrypt wykonuje synchronizację paczkami danych z tabeli OSOBY do tabeli KOPIA_OSOBY. W linii 13 uzupełniane są kolekcje danych (identyfikatory i nazwiska) w rekordzie typ_osoby_rec. Następnie za pomocą klauzuli FORALL dane te są uaktualniane w tabeli kopia_osoby. Należy zwrócić uwagę iż FORALL nie jest typową pętlą LOOP. Z klauzulą tą musi być powiązana operacja DMLowa, w tym przypadku w linii 17 UPDATE. Co więcej klauzula FORALL zaczyna się w linii 17, a kończy się w linii 18 i jest jednym wyrażeniem, w przeciwieństwie do standardowej pętli składającej się z wielu wyrażeń zagnieżdżonych. W naszym przypadku iterujemy po wszystkich identyfikatorach rekordu od pierwszego elementu kolekcji nazwiska (os.rec.nazwiska.FIRST) do ostatniego (os.rec.nazwiska.LAST). Dla każdego tak pobranego indeksu wykonujemy operację UPDATE. W linii 19 uaktualniamy faktyczną liczbę rekordów zmienionych w ramach FORALLa. Tak wykonana operacja ponownie pozwala zmniejszyć liczbę przełączeń między kontekstami.

Masowy DML ze zwracaniem wartości (forall DML with returnig)

Kolejnym rozszerzeniem klauzuli FORALL jest możliwość zwracania wartości z masowych DMLi bezpośrednio do kolekcji. Jest to rozszerzenie klasycznego DMLa z opcją returing zaprezentowanego poniżej:

01> DECLARE
02> v_imie osoby.imie%type;
03> v_nazwisko osoby.nazwisko%type;
04> BEGIN
05> UPDATE osoby SET imie = initcap(imie), nazwisko = initcap(nazwisko)
WHERE idosoby = 123 RETURNING imie, nazwisko INTO v_imie, v_nazwisko;
06> END;

Dla zobrazowania przykładu masowego przetwarzania zwracającego dane przyjmijmy hipotetyczny przypadek bloku, który kasuje dane operacją DELETE, z kryterium opartym o warunek tekstowy. Rekordy, które udało się usunąć, wrzucane są do kolekcji bulkową klauzulą RETURNING BULK COLLECT INTO. Tak zapamiętane dane następnie mogą zostać przetworzone w dowolny sposób, w tym przypadku FORALLem zapisywane są w tabeli historycznej. Na potrzeby przetwarzania na wstępie musimy stworzyć historyczną tabelę pomocniczą osoby_hist:

01> create table osoby_hist as select idosoby, nazwisko from osoby where 1=0;

Następnie wykonujemy skrypt:

01> DECLARE
02> TYPE tab_ids IS TABLE OF osoby.idosoby%TYPE INDEX BY BINARY_INTEGER;
03> TYPE tab_nazwisk IS TABLE OF osoby.nazwisko%TYPE INDEX BY BINARY_INTEGER;
04> ids tab_ids;
05> nazwiska tab_nazwisk;
06>
07> BEGIN
08> DELETE FROM kopia_osoby WHERE upper(nazwisko) like 'N%' RETURNING idosoby, nazwisko BULK COLLECT INTO ids, nazwiska;
09> DBMS_OUTPUT.PUT_LINE ('Usunieto: ' || TO_CHAR(SQL%ROWCOUNT)||' nazwisk.' );
10> DBMS_OUTPUT.PUT_LINE ('Liczba nazwisk: '||TO_CHAR(nazwiska.COUNT) );
11> IF ids.COUNT > 0 THEN
12> FORALL i IN ids.FIRST .. ids.LAST
13> INSERT INTO osoby_hist VALUES ( ids(i), nazwiska(i) );
14> DBMS_OUTPUT.PUT_LINE ( 'Dodano: ' ||TO_CHAR(SQL%ROWCOUNT) );
15> END IF;
16> END;

W wyniku wykonania skryptu dostajemy informację:

Usunieto: 286 nazwisk.
Liczba nazwisk w tablicy: 286
Dodano: 286

Na szczególną uwagę zasługuje tu linia 8, w której za pomocą klauzuli RETURNING BULK COLLECT INTO dostajemy paczkę danych dotyczącą aktualnie zmodyfikowanych (w tym przypadku usuniętych rekordów). Taka forma zdefiniowania skryptu wykorzystująca kasowanie i operacje bulkowe pozwala w prosty sposób obejść konieczność tworzenia triggerów zapewniających przenoszenie danych do tabel historycznych.

Obsługa wyjątków w kolekcjach (SAVE EXCEPTIONS)

Wprowadzenie masowego przetwarzania DML (bulk binding forall) w Oracle 8i znacząco rozszerzyło możliwości programisty w zakresie tworzenia optymalnego kodu pod względem wydajnościowym, ze względu na redukcję kosztów związanych ze zmianą kontektsu przetwarzania. Rozwiązanie to było jednak nie do końca wygodne dla programistów, gdyż dowolny problem z pojedynczą akcją DMLową przerywał działanie całego przetwarzania wsadowego. Można powiedzieć, że skrypt albo wykonał się cały, albo nie wykonał się wcale. Rozwiązaniem tego problemu jest klauzula SAVE EXCEPTIONS, która pojawiła się wraz z nadejściem Oracle 9i. Podstawową zaletą tego rozwiązania jest możliwość kontynuowania przetwarzania w przypadku wystąpienia błędów w trakcie przetwarzania kolekcji. System w przypadku błędu powoduje przerwanie pojedynczej akcji DML i zapisuje błąd w specjalnym buforze SQL%BULK_EXCEPTIONS. Poniżej został przedstawiony przykład wykorzystania klauzuli SAVE EXCEPTIONS polegający na zapamiętywaniu usuniętych danych w tabeli osoby_hist. W kodzie celowo wprowadzono duplikaty identyfikatorów w liniach 10-13, aby wsadowe przetwarzanie zwróciło błędy.

01> DECLARE
02> TYPE tab_ids IS TABLE OF osoby.idosoby%TYPE;
03> TYPE tab_nazwisk IS TABLE OF osoby.nazwisko%TYPE;
04> ids tab_ids;
05> nazwiska tab_nazwisk;
06> BEGIN
07> DELETE FROM kopia_osoby WHERE upper(nazwisko) like 'K%' RETURNING idosoby, nazwisko BULK COLLECT INTO ids, nazwiska;
08> IF ids.COUNT > 0 THEN
09> --rozszerzamy tablice o duplikaty w celu spowodowania błędu
10> ids.extend; ids(ids.last):=ids(1);
11> ids.extend; ids(ids.last):=ids(1);
12> nazwiska.extend; nazwiska(nazwiska.last):=nazwiska(1);
13> nazwiska.extend; nazwiska(nazwiska.last):=nazwiska(1);
14> END IF;
15> -- wykonanie operacji wsadowej na tablicy osoby_hist z pomijaniem błędów
16> FORALL i IN ids.FIRST .. ids.LAST SAVE EXCEPTIONS
17> INSERT INTO osoby_hist VALUES ( ids(i), nazwiska(i) );
18> EXCEPTION WHEN OTHERS THEN
19> dbms_output.put_line ('naliczono: '||SQL%BULK_EXCEPTIONS.COUNT);
20> FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
21> dbms_output.put_line (SQL%BULK_EXCEPTIONS(i).error_index);
22> dbms_output.put_line ( SQLERRM(-1 * SQL%BULK_EXCEPTIONS(i).error_code));
23> END LOOP;
24> END;

Powyższy kod wykona się poprawnie, przy czym na standardowe wyjście zwróci indeksy kolekcji ids, przy których wystąpił błąd (linia 21) oraz komunikat błędu (linia 22).

Podsumowanie

Kolekcje, które zostały opisane w pierwszej części artykułu w prosty sposób pozwalają gromadzić i przetwarzać dane. Bardziej zaawansowane opcje pozwalają nie tylko na ich funkcjonalne wykorzystanie, ale także bezpośrednio wpływają na wydajność aplikacji. Jako dobry programista, każdorazowo kiedy tworzysz kod w PL/SQLu i wykorzystujesz w nim elementy SQL musisz mieć świadomość, że tworzysz kod de facto w dwóch językach. Moment przejścia między tymi językami, to właśnie kosztowna zmiana kontekstu („context switching”). Każda taka zmiana – w szczególności wykonywana w pętli, to znaczące obciążenie dla serwera. Dysponując tą wiedzą i narzędziami takimi jak BULK COLLECT (masowe pobieranie danych z opcją limitowania i bez niej), FORALL DML (masowy DML z i bez opcji zwracania kolekcji zmienionych wartości) oraz klauzulą SAVE EXCEPTION (gromadzenie wyjątków i kontynuowanie działania pętli w przypadku błędu) jesteś w stanie tworzyć oprogramowanie wydajne i działające niezawodnie.