🥇 śladami Highload++ Siberia 2019 — 8 zadań z Oracle

Cześć!

24-25 czerwca w Nowosybirsku odbyła się konferencja Highload++ Syberia 2019. Nasi ludzie również tam byli. referat «Kontenerowe bazy Oracle (CDB/PDB) i ich praktyczne zastosowanie w rozwoju oprogramowania», tekstowa wersja pojawi się nieco później. To było super, dziękujemy. olegbunin za organizację oraz wszystkim, którzy przyszli.

W tym poście chcielibyśmy podzielić się z Wami zadaniami, które były na naszym stoisku, abyście mogli sprawdzić swoją wiedzę na temat Oracle. Poniżej – 8 zadań, opcje odpowiedzi i wyjaśnienie.

Jaką maksymalną wartość sekwencji zobaczymy w wyniku wykonania następującego skryptu?

create sequence s start with 1;
 
select s.currval, s.nextval, s.currval, s.nextval, s.currval
from dual
connect by level <= 5;

1
5
10
25
Żadną, wystąpi błąd.

OdpowiedźZgodnie z dokumentacją Oracle (cytowane z 8.1.6):
W ramach pojedynczego zapytania SQL, Oracle zwiększy sekwencję tylko raz na każdy wiersz. Jeśli zapytanie zawiera więcej niż jedno odniesienie do NEXTVAL w sekwencji, Oracle zwiększa sekwencję tylko raz i zwraca tę samą wartość dla wszystkich wystąpień NEXTVAL. Jeśli zapytanie zawiera odniesienia zarówno do CURRVAL, jak i NEXTVAL, Oracle zwiększa sekwencję i zwraca tę samą wartość dla CURRVAL i NEXTVAL, niezależnie od ich kolejności w zapytaniu.

W ten sposób, maksymalna wartość będzie odpowiadać liczbie wierszy, czyli 5..

Ile wierszy znajdzie się w tabeli w wyniku wykonania następującego skryptu?

create table t(i integer check (i < 5));
 
create procedure p(p_from integer, p_to integer) as
begin
    for i in p_from .. p_to loop
        insert into t values (i);
    end loop;
end;
/
 
exec p(1, 3);
exec p(4, 6);
exec p(7, 9);

OdpowiedźZgodnie z dokumentacją Oracle (cytowane z 11.2):

Przed wykonaniem jakiegokolwiek zapytania SQL, Oracle oznacza niejawny punkt zapisu (niedostępny dla Ciebie). Następnie, jeśli zapytanie zakończy się niepowodzeniem, Oracle automatycznie cofa je i zwraca stosowny kod błędu do SQLCODE w SQLCA. Na przykład, jeśli zapytanie INSERT powoduje błąd, próbując wstawić duplikat wartości w unikalnym indeksie, zapytanie jest cofane.

Zgłoszenie procedury składowanej z klienta jest również traktowane i przetwarzane jako pojedyncze zapytanie. W ten sposób pierwsze wywołanie procedury składowanej kończy się pomyślnie, wstawiając trzy rekordy; drugie wywołanie procedury składowanej kończy się błędem i cofa czwarty rekord, który udało się wstawić; trzecie wywołanie kończy się błędem, a w tabeli znajdują się trzy rekordy..

Ile wierszy znajdzie się w tabeli w wyniku wykonania następującego skryptu?

create table t(i integer, constraint i_ch check (i < 3));
 
begin
    insert into t values (1);
    insert into t values (null);
    insert into t values (2);
    insert into t values (null);
    insert into t values (3);
    insert into t values (null);
    insert into t values (4);
    insert into t values (null);
    insert into t values (5);
exception
    when others then
        dbms_output.put_line('Ups!');
end;
/

OdpowiedźZgodnie z dokumentacją Oracle (cytowane z 11.2):

Ograniczenie sprawdzające pozwala określić warunek, który każda linia w tabeli musi spełniać. Aby spełnić to ograniczenie, każda linia w tabeli musi spełniać warunek jako PRAWDA lub nieznany (z powodu wartości null). Kiedy Oracle ocenia warunek ograniczenia sprawdzającego dla określonej linii, jakiekolwiek nazwy kolumn występujące w warunku odnoszą się do wartości kolumn w tej linii.

W ten sposób wartość null przejdzie sprawdzenie, a anonimowy blok będzie wykonywany pomyślnie do momentu próby wstawienia wartości 3. Po tym bloku obsługi błędów błąd zostanie zgaszony, nie będzie rollbacku i w tabeli pozostaną cztery linie z wartościami 1, null, 2 i znowu null.

Jakie pary wartości zajmą tę samą ilość miejsca w bloku?

create table t (
    a char(1 char),
    b char(10 char),
    c char(100 char),
    i number(4),
    j number(14),
    k number(24),
    x varchar2(1 char),
    y varchar2(10 char),
    z varchar2(100 char));
 
insert into t (a, b, i, j, x, y)
    values ('Y', 'Władzio', 10, 10, 'D', 'Władzio');

A i X
B i Y
C i K
C i Z
K i Z
I i J
J i X
Wszystkie wymienione

OdpowiedźPrzedstawiamy fragmenty dokumentacji (12.1.0.2) dotyczące przechowywania różnych typów danych w Oracle.

Typ danych CHAR
Typ danych CHAR określa łańcuch znakowy o stałej długości w zestawie znaków bazy danych. Podczas tworzenia bazy danych określasz zestaw znaków bazy danych. Oracle zapewnia, że wszystkie wartości przechowywane w kolumnie CHAR mają długość określoną przez rozmiar w wybranej semantyce długości. Jeśli wstawisz wartość, która jest krótsza od długości kolumny, Oracle dopełni wartość spacjami do długości kolumny.

Typ danych VARCHAR2
Typ danych VARCHAR2 określa łańcuch znakowy o zmiennej długości w zestawie znaków bazy danych. Podczas tworzenia bazy danych określasz zestaw znaków bazy danych. Oracle przechowuje wartość znakową w kolumnie VARCHAR2 dokładnie tak, jak ją podajesz, bez dopełniania spacjami, pod warunkiem, że wartość nie przekracza długości kolumny.

Typ danych NUMBER
Typ danych NUMBER przechowuje zera, a także pozytywne i ujemne liczby stałe o wartościach bezwzględnych od 1.0 x 10-130 do, ale nie włączając 1.0 x 10126. Jeśli określisz wyrażenie arytmetyczne, którego wartość ma wartość bezwzględną większą lub równą 1.0 x 10126, Oracle zwraca błąd. Każda wartość NUMBER wymaga od 1 do 22 bajtów. Biorąc to pod uwagę, rozmiar kolumny w bajtach dla konkretnej liczbowej wartości danych NUMBER(p), gdzie p jest dokładnością danej wartości, można obliczyć za pomocą następującego wzoru: ROUND((length(p)+s)/2))+1 gdzie s wynosi zero, jeśli liczba jest dodatnia, a s wynosi 1, jeśli liczba jest ujemna.

Ponadto, zaprezentujmy fragment dokumentacji dotyczący przechowywania wartości Null.

Wartość null oznacza brak wartości w kolumnie. Null wskazuje na brakujące, nieznane lub nieodpowiednie dane. Nulls są przechowywane w bazie danych, jeśli znajdują się między kolumnami z wartościami danych. W takich przypadkach wymagają 1 bajta do przechowywania długości kolumny (zero). Ostatnie nulls w wierszu nie wymagają przechowywania, ponieważ nowy nagłówek wiersza sygnalizuje, że pozostałe kolumny w poprzednim wierszu są null. Na przykład, jeśli ostatnie trzy kolumny w tabeli są null, to dla tych kolumn nie są przechowywane żadne dane.

Na podstawie tych danych budujemy rozważania. Zakładamy, że w bazie danych używana jest kodowanie AL32UTF8. W tym kodowaniu rosyjskie litery zajmą 2 bajty.

1) A i X, wartość pola a ‘Y’ zajmuje 1 bajt, wartość pola x ‘D’ – 2 bajty
2) B i Y, ‘Wania’ w b zostanie uzupełnione spacjami do 10 znaków i zajmie 14 bajtów, ‘Wania’ w d – zajmie 8 bajtów.
3) C i K. Oba pola mają wartość NULL, po nich znajdują się znaczące pola, dlatego zajmują po 1 bajcie.
4) C i Z. Oba pola mają wartość NULL, ale pole Z jest ostatnie w tabeli, dlatego nie zajmuje miejsca (0 bajtów). Pole C zajmuje 1 bajt.
5) K i Z. Podobnie jak w poprzednim przypadku. Wartość w polu K zajmuje 1 bajt, w Z – 0.
6) I i J. Zgodnie z dokumentacją, obie wartości zajmą po 2 bajty. Długość obliczamy według wzoru podanego w dokumentacji: round( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J i X. Wartość w polu J zajmie 2 bajty, a wartość w polu X zajmie 2 bajty.

Podsumowując, poprawne pary to: C i K, I i J, J i X.

Jaki będzie przybliżony clustering factor indeksu T_I?

create table t (i integer);
 
insert into t select rownum from dual connect by level <= 10000;
 
create index t_i on t(i);

Rzędy dziesiątek
Rzędy setek
Rzędy tysięcy
Rzędy dziesiątek tysięcy

OdpowiedźZgodnie z dokumentacją Oracle (cytowane z 12.1):

Dla indeksu B-drzewiastego, clustering factor indeksu mierzy fizyczne grupowanie wierszy w stosunku do wartości indeksu.

Clustering factor pomaga optymalizatorowi zdecydować, czy skanowanie indeksu, czy pełne skanowanie tabeli jest bardziej efektywne dla niektórych zapytań. Niski clustering factor oznacza wydajne skanowanie indeksu.

Clustering factor bliski liczbie bloków w tabeli oznacza, że wiersze są fizycznie uporządkowane w blokach tabeli według klucza indeksu. Jeśli baza danych wykonuje pełne skanowanie tabeli, wtedy tendencja jest, aby pobierać wiersze w kolejności, w jakiej są przechowywane na dysku posortowane według klucza indeksu. Clustering factor bliski liczbie wierszy oznacza, że wiersze są przypadkowo rozsiane po blokach bazy danych w stosunku do klucza indeksu. Jeśli baza danych wykonuje pełne skanowanie tabeli, wtedy wiersze nie będą pobierane w żadnej posortowanej kolejności według tego klucza indeksu.

W tym przypadku dane są idealnie posortowane, dlatego clustering factor będzie równy lub bliski liczbie zajętych bloków w tabeli. Dla standardowego rozmiaru bloku wynoszącego 8 kilobajtów można oczekiwać, że w jednym bloku zmieści się około tysiąca ciasnych wartości number, dlatego liczba bloków, a co za tym idzie clustering factor, będzie rzędami dziesiątek.

Jakie wartości N spowodują, że poniższy skrypt zostanie pomyślnie wykonany w standardowej bazie danych z domyślnymi ustawieniami?

create table t (
    a varchar2(N char),
    b varchar2(N char),
    c varchar2(N char),
    d varchar2(N char));
 
create index t_i on t (a, b, c, d);

100
200
400
800
1600
3200
6400

OdpowiedźZgodnie z dokumentacją Oracle (cytowane z 11.2):

Logiczne limity bazy danych

Pozycja
Typ limitu
Wartość limitu

Indeksy
Całkowity rozmiar indeksowanej kolumny
75% rozmiaru bloku bazy danych minus pewne narzuty

Tak więc całkowity rozmiar indeksowanych kolumn nie powinien przekraczać 6KB. Dalsze zależy od wybranej kompresji bazy. Dla kompresji AL32UTF8 jeden znak może zajmować maksymalnie 4 bajty, więc w 6 kilobajtach w najgorszym przypadku zmieści się około 1500 znaków. Dlatego Oracle zabroni tworzenia indeksu przy N = 400 (gdy długość klucza w najgorszym przypadku wyniesie 1600 znaków * 4 bajty + długość rowid), podczas gdy przy N = 200 (i mniej) tworzenie indeksu zadziała bez problemów.

Operator INSERT z hintem APPEND służy do ładowania danych w trybie direct. Co się stanie, jeśli zostanie zastosowany do tabeli, na której jest wyzwalacz?

Dane zostaną załadowane w trybie direct, wyzwalacz zadziała tak, jak powinien.
Dane zostaną załadowane w trybie direct, ale wyzwalacz nie zostanie uruchomiony.
Dane zostaną załadowane w trybie conventional, wyzwalacz zadziała tak, jak powinien.
Dane zostaną załadowane w trybie conventional, ale wyzwalacz nie zostanie uruchomiony.
Dane nie będą załadowane, zostanie zgłoszony błąd.

OdpowiedźW zasadzie to pytanie bardziej dotyczące logiki. Aby znaleźć prawidłową odpowiedź, zaproponowałbym następujący model rozumowania:

Wstawienie w trybie direct realizowane jest poprzez bezpośrednie formowanie bloku danych, omijając silnik SQL, co zapewnia dużą prędkość. Dlatego trudno jest zapewnić uruchomienie wyzwalacza, jeśli w ogóle jest to możliwe, i nie ma sensu, ponieważ i tak znacznie spowolniłby wstawienie.
Nieuruchomienie wyzwalacza spowoduje, że w przypadku identycznych danych w tabeli, stan bazy jako całość (innych tabel) będzie zależał od tego, w jakim trybie zostały te dane wstawione. Oczywiste jest, że zrujnowałoby to integralność danych i nie może być stosowane jako rozwiązanie w produkcji.
Niemożność wykonania żądanej operacji, ogólnie mówiąc, traktuje się jako błąd. Należy jednak pamiętać, że APPEND to hint, a ogólna logika hintów polega na tym, że są one brane pod uwagę, jeśli to możliwe, jeśli nie, to operator jest wykonywany bez uwzględnienia hintu.

Zatem oczekiwana odpowiedź to dane zostaną załadowane w zwykłym (SQL) trybie, wyzwalacz zadziała.

Zgodnie z dokumentacją Oracle (cytowanej z 8.04):

Naruszenia ograniczeń spowodują, że polecenie zostanie wykonane szeregowo, korzystając z konwencjonalnej ścieżki wstawiania, bez ostrzeżeń ani komunikatów o błędach. Wyjątkiem jest ograniczenie dotyczące poleceń uzyskujących dostęp do tej samej tabeli więcej niż raz w transakcji, co może spowodować komunikaty o błędach.
Na przykład, jeśli w tabeli występują wyzwalacze lub integralność referencyjna, wskazówka APPEND zostanie zignorowana podczas próby użycia bezpośredniego wstawiania INSERT (szeregowego lub równoległego), jak również wskazówka lub klauzula PARALLEL, o ile to możliwe.

Co się stanie podczas wykonania następującego skryptu?

create table t(i integer not null primary key, j integer references t);
 
create trigger t_a_i after insert on t for each row
declare
    pragma autonomous_transaction;
begin
    insert into t values (:new.i + 1, :new.i);
    commit;
end;
/
 
insert into t values (1, null);

Wykonanie zakończone sukcesem
Błąd z powodu błędu składniowego
Błąd związany z niedopuszczalnością transakcji autonomicznych
Błąd związany z przekroczeniem maksymalnej głębokości wywołań
Błąd związany z naruszeniem klucza obcego
Błąd związany z blokadami

OdpowiedźTabela i wyzwalacz są tworzone w pełni poprawnie i ta operacja nie powinna prowadzić do problemów. Transakcje autonomiczne w wyzwalaczu są również dozwolone, w przeciwnym razie niemożliwe byłoby na przykład logowanie.

Po wstawieniu pierwszego wiersza skuteczne uruchomienie wyzwalacza spowodowałoby wstawienie drugiego wiersza, co ponownie uruchomiłoby wyzwalacz, wstawiając trzeci wiersz i tak dalej, aż do momentu, gdy zapytanie nie zakończyłoby się z powodu przekroczenia maksymalnej głębokości wywołań. Jednak, pojawia się jeszcze jeden subtelny moment. W momencie wykonywania wyzwalacza dla pierwszego wstawionego rekordu commit jeszcze nie został wykonany. Dlatego wyzwalacz, działający w transakcji autonomicznej, próbowałby wstawić do tabeli wiersz, który odnosi się przez klucz obcy do jeszcze nie zatwierdzonego rekordu. To prowadzi do oczekiwania (transakcja autonomiczna czeka na commit głównej, aby określić, czy można wstawić dane) i jednocześnie główna transakcja czeka na commit autonomicznej, aby kontynuować działanie po wyzwalaczu. Występuje deadlock i w konsekwencji – transakcja autonomiczna jest anulowana z powodu problemów związanych z blokadami..

Tylko zarejestrowani użytkownicy mogą brać udział w ankiecie. Zaloguj się, proszę.