Blog: Bazy danych | Oracle database

Sekwencje w Oracle – można lepiej, szybciej i wygodniej – część pierwsza

Sekwencje w Oracle – można lepiej, szybciej i wygodniej – część pierwsza
  • 2 015 views

No właśnie - co mądrego można wymyślić w mechanizmie nadawania identyfikatorów, który nie zmieniał się od kilkunastu lat? Okazuje się, że można i to nawet sporo. Myślę, że każdy projektant systemów Oracle ma świadomość, że mechanizm nadawania identyfikatorów w bazach Oracle był dość słaby, żeby nie użyć mocniejszych słów. Używam słowa „był”, ponieważ w wersji 12cR1 nastąpiły bardzo istotne zmiany, a i w 18c da się znaleźć ciekawe nowinki.

Udostępnij!

Przed 12c, czyli albo niewygodnie, albo powoli

Zanim ukazała się wersja 12c, za każdym razem musieliśmy wybierać jedno z dwóch rozwiązań.

  • Konsekwentnie, w każdej wstawiającej dane linijce kodu korzystać z sekwencji – moje lata doświadczeń jako programisty i projektanta wskazują, że bardzo trudno utrzymać w tej kwestii dyscyplinę. Dzieje się tak przez zwykłe błędy, brak konsekwencji na etapie wdrażania, kiedy komuś wygodniej wrzucić numerek „z palca”, czy też inne problemy wynikające z faktu, że sekwencje można powiązać z tabelą najwyżej przez nazwę.
  • Triggery – bardzo wygodna metoda. Zakładamy wyzwalacz „before insert for each row”, decydujemy tylko, czy identyfikator może, czy musi być z sekwencji i dalej działa samodzielnie. Niestety, odpalanie jednostki PL/SQL z poziomu każdego insertu powoduje, że musimy zapomnieć o wydajności.

Istniał także trzeci sposób – można było stworzyć i posługiwać się API proceduralnym do wstawiania wierszy. Niestety, nie było to ani wygodne, ani wydajne, ani bezpieczne. Niewygodne przez problemy z obsługą wartości domyślnych dla kolumn, niewydajne, bo, podobnie jak triggery, miksowano SQL z PL/SQL przy każdym insercie, a niebezpieczne, ponieważ nic nie chroniło przed insertem „z boku” poza API.

Aby mieć punkt wyjścia do dalszych rozważań, przeprowadźmy mały eksperyment. Porównajmy czas bezpośredniej obsługi autoinkrementacji kolumn oraz poprzez wyzwalacze dla miliona wstawionych wierszy. (Celowo nie zakładam klucza w tabeli, aby czas kreowania indeksu nie zaburzył znacząco czasu generowania id).

 

12c i wreszcie mamy autoinkrementację

W wersji 12cR1 doczekaliśmy się wreszcie automatycznej inkrementacji.

Podczas tworzenia tabeli, lub później, jako modyfikacje już istniejącej tabeli z danymi możemy dołożyć do kolumny następujące frazy:

Pozwalają one dobrać rozwiązanie do własnych potrzeb, np.

pozwala na wymuszenie obowiązkowej autoinkrementacji

umożliwia wygenerowanie numeru tylko jeśli nie podamy własnej wartości dla klucza

wstawi wartość nawet jeśli  do kolumny klucza wpiszemy NULL

pozwoli prowadzić numerację z parametrami takimi jak w zwykłej sekwencji.

Należy zwrócić uwagę na to, że sama autoinkrementacja, nawet w wydaniu obowiązkowym, nie gwarantuje unikalności. Jest zatem dodatkiem do klucza, a nie opcją, która ma go zastępować.

A jak jest zaimplementowana ta nowa funkcjonalność? Cóż, „pod spodem” polega na zdefiniowaniu systemowej sekwencji i podpięciu jej do kolumny jako wartości domyślnej. Można to prześledzić łatwo w słowniku systemowym.

Załóżmy przykładową tabelkę.

Rozpoznajmy autoinkrementację naszej tabeli.

Znajdźmy sekwencję i kolumnę.

Od wersji 12c możemy także jawnie podpiąć istniejącą, zwyczajną sekwencję do dowolnej kolumny numerycznej jako wartość domyślną i uzyskać w ten sposób autoinkrementację:

A jak wygląda szybkość rozwiązania bazującego na sekwencji w wartości domyślnej?  Porównajmy je z poprzednimi dwoma przypadkami wykonując analogiczny test:

Jak widać po czasach, to rozwiązanie nie jest obciążone czasowo, a jednocześnie pozostaje fantastycznie wygodne.

Wybierając pomiędzy autoinkrementacją, a jawnie przypiętą sekwencją jako wartością domyślną, należy rozważyć zalety obydwu opcji.

Za autoinkrementacją przemawiają:

  • Prostota zarządzania – po dropnięciu tabeli i usunięciu jej z kosza znikają też powiązane sekwencje
  • Łatwość zarządzania sekwencją – zamiast kłopotliwego przewijania sekwencji w przód lub tył (kto robił, wie, jaki to ból) można wykonać poniższe polecenie i już mamy sekwencję ustawioną na dowolną wartość

  • Możliwość wymuszenia generowania wartości wyłącznie z sekwencji

Za wartością domyślną przemawia elastyczność:

  • Możemy mieć wiele kolumn w tabelach numerowanych sekwencjami.
  • Możemy używać tej samej sekwencji do wielu kolumn w tej samej lub różnych tabelach po to by zachować szerszy zakres unikalności kolumn.

W drugim  artykule chciałbym pokazać sekwencje z jeszcze innej strony. W wersji 12c pojawiły się sekwencje lokalne (sesyjne), a w wersji 18c sekwencje kompozytowe. Zwłaszcza te drugie rozwiązują wreszcie pewne istotne problemy związane ze współdzieleniem zasobów w systemach transakcyjnych.