OLTP a kolumny obliczane

W systemach transakcyjnych kolumny obliczane warto utrwalać, by nie przeliczać wyrażenia przy każdym odczycie.

Rola kolumn obliczanych w systemie OLTP

OLTP, czyli przetwarzanie transakcyjne online, jest sposobem organizacji pracy bazy danych, w którym wykonywane są liczne, krótkie operacje dotyczące pojedynczych rekordów lub niewielkich zestawów danych. Przykładami są zapis zamówienia, zmiana statusu płatności, wystawienie dokumentu, aktualizacja stanu magazynowego albo odczyt danych klienta. W takim środowisku znaczenie ma nie tylko poprawność wyniku, lecz także koszt jego uzyskania przy dużej liczbie równoczesnych operacji.

Kolumna obliczana przechowuje wartość wynikającą z innych kolumn, na przykład sumę ceny i podatku, pełną nazwę utworzoną z dwóch pól tekstowych albo znacznik określający, czy rekord spełnia określony warunek. Wartość może być wyliczana podczas odczytu albo utrwalana w strukturze danych. Utrwalanie ogranicza konieczność ponownego wykonywania tego samego wyrażenia, ale zwiększa koszt zapisu i wymaga zachowania zgodności między wartościami źródłowymi a wynikiem.

Odczyt wyrażenia a utrwalony wynik

Kolumna obliczana wyliczana dopiero podczas zapytania nie zajmuje dodatkowego miejsca na przechowywanie wyniku, a jej wartość zawsze powstaje na podstawie aktualnych danych źródłowych. Każda operacja odczytu może jednak wymagać wykonania wyrażenia. Przy prostym działaniu arytmetycznym koszt bywa mały, lecz przy funkcjach tekstowych, konwersjach typów, rozbudowanych warunkach lub odczycie dużej liczby wierszy obciążenie może stać się zauważalne.

Utrwalona kolumna obliczana przechowuje rezultat wiersza i aktualizuje go, gdy zmieniają się wartości użyte w wyrażeniu. Dzięki temu odczyt gotowego wyniku może być prostszy, a indeksowanie takiej wartości może przyspieszyć wyszukiwanie lub sortowanie, jeżeli dany silnik bazy danych dopuszcza indeksy dla tego rodzaju kolumn. Ceną jest większy rozmiar tabeli, dodatkowa praca przy operacjach INSERT i UPDATE oraz konieczność uwzględnienia wyniku w mechanizmach zapisu, odtwarzania i kontroli spójności.

Jak ocenić opłacalność materializacji

Decyzja o utrwaleniu kolumny obliczanej powinna wynikać z profilu obciążenia, a nie z samego faktu, że wyrażenie jest używane w wielu miejscach. W systemie, w którym dane są często odczytywane, lecz rzadko zmieniane, ponowne obliczanie może być niepotrzebnym kosztem. Odwrotna sytuacja występuje w tabeli intensywnie aktualizowanej: wtedy każda zmiana kolumn źródłowych może wymuszać zapis nowej wartości obliczonej, zwiększając koszt transakcji i ryzyko blokowania innych operacji.

Przed zmianą warto sprawdzić plan wykonania zapytań, częstotliwość odczytów i zapisów, selektywność warunków oraz rozmiar tabeli. Istotna jest także deterministyczność wyrażenia. Wynik zależny wyłącznie od wartości zapisanych w tym samym wierszu łatwiej utrzymać w zgodności niż wynik zależny od bieżącego czasu, ustawień sesji, danych z innej tabeli lub zmiennego środowiska. Nie każde wyrażenie może być więc bezpiecznie utrwalane albo indeksowane. Trzeba również jednoznacznie określić zachowanie dla wartości NULL, konwersji typów, zaokrągleń i przepełnienia zakresu liczbowego.

Spójność danych i diagnostyka działania

Najczęstszy problem przy kolumnach obliczanych nie polega na błędnym wzorze, lecz na niejasnym rozdzieleniu danych źródłowych od wartości pochodnych. Jeżeli aplikacja zaczyna samodzielnie wpisywać do tabeli wynik, który powinien wynikać z innych pól, może dojść do rozbieżności po częściowej aktualizacji, zmianie reguły biznesowej albo pominięciu jednego z miejsc zapisu. Bezpieczniejszy model zakłada, że definicja obliczenia jest utrzymywana w jednym, kontrolowanym miejscu, a aplikacja korzysta z niej w sposób zgodny z możliwościami używanego silnika.

Podczas analizy problemu należy porównać wartości kolumn źródłowych z wynikiem, sprawdzić moment aktualizacji rekordu oraz ustalić, czy zapytanie korzysta z utrwalonej wartości, czy wykonuje wyrażenie ponownie. Pomocne jest przeanalizowanie operacji INSERT i UPDATE, planów zapytań oraz przypadków brzegowych, takich jak brak wartości, ujemne liczby, zmiana precyzji lub modyfikacja reguły obliczenia. W laptopie albo komputerze wykorzystywanym do pracy z aplikacją bazodanową problemy mogą objawiać się jako wolne formularze, długie zapisy lub opóźnione raporty, ale ich przyczyna może znajdować się w projekcie tabeli i sposobie wykonywania zapytań. Przy diagnostyce należy oddzielić wydajność lokalnego sprzętu od kosztu operacji realizowanych przez bazę danych.