🥇Śladami Highload++ Siberia 2019 - 8 zadań na Oracle

Hi!

W dniach 24-25 czerwca w Nowosybirsku odbyła się konferencja Highload++ Siberia 2019. Nasi goście też tam byli raport „Kontenerowe bazy danych Oracle (CDB/PDB) i ich praktyczne zastosowanie przy tworzeniu oprogramowania”, wersję tekstową opublikujemy nieco później. Było spoko, dzięki olegbunina dla organizacji, a także dla wszystkich, którzy przyszli.

W tym poście chcielibyśmy podzielić się z Wami problemami, jakie mieliśmy na naszym stoisku, abyście mogli sprawdzić swoją wiedzę na temat Oracle. Poniżej cięcia znajduje się 8 problemów, opcje odpowiedzi i wyjaśnienia.

Jaka jest maksymalna wartość sekwencji, którą zobaczymy w wyniku wykonania poniższego 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
Nie, wystąpi błąd

odpowiedźWedług dokumentacji Oracle (cytowanej z 8.1.6):
W ramach pojedynczej instrukcji SQL Oracle będzie zwiększać sekwencję tylko raz na wiersz. Jeśli instrukcja zawiera więcej niż jedno odniesienie do NEXTVAL dla sekwencji, Oracle zwiększa sekwencję jeden raz i zwraca tę samą wartość dla wszystkich wystąpień NEXTVAL. Jeśli instrukcja zawiera odniesienia zarówno do CURRVAL, jak i NEXTVAL, Oracle zwiększa sekwencję i zwraca tę samą wartość zarówno dla CURRVAL, jak i NEXTVAL, niezależnie od ich kolejności w instrukcji.

Tak więc, maksymalna wartość będzie odpowiadać liczbie linii, czyli 5.

Ile wierszy będzie w tabeli po uruchomieniu poniższego 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źWedług dokumentacji Oracle (cytowanej z 11.2):

Przed wykonaniem jakiejkolwiek instrukcji SQL Oracle zaznacza ukryty punkt zapisu (niedostępny dla Ciebie). Następnie, jeśli instrukcja się nie powiedzie, Oracle automatycznie ją wycofa i zwróci odpowiedni kod błędu do kodu SQLCODE w ośrodku SQLCA. Na przykład, jeśli instrukcja INSERT spowoduje błąd w wyniku próby wstawienia zduplikowanej wartości do unikalnego indeksu, instrukcja zostanie wycofana.

Wywołanie HP od klienta jest również rozpatrywane i przetwarzane jako pojedyncze oświadczenie. Zatem pierwsze wywołanie HP kończy się pomyślnie po wstawieniu trzech rekordów; drugie wywołanie HP kończy się błędem i wycofuje czwarty rekord, który udało mu się wstawić; trzecie połączenie nie powiedzie się, a w tabeli znajdują się trzy rekordy.

Ile wierszy będzie w tabeli po uruchomieniu poniższego 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('Oops!');
end;
/

odpowiedźWedług dokumentacji Oracle (cytowanej z 11.2):

Ograniczenie sprawdzające pozwala określić warunek, który musi spełniać każdy wiersz w tabeli. Aby spełnić to ograniczenie, każdy wiersz w tabeli musi mieć warunek PRAWDA lub nieznany (ze względu na wartość null). Kiedy Oracle ocenia warunek ograniczenia sprawdzającego dla konkretnego wiersza, wszelkie nazwy kolumn w tym warunku odnoszą się do wartości kolumn w tym wierszu.

Zatem wartość null przejdzie kontrolę, a blok anonimowy zostanie pomyślnie wykonany aż do próby wstawienia wartości 3. Następnie blok obsługi błędów usunie wyjątek, nie nastąpi wycofanie zmian i w tabeli pozostaną cztery wiersze z wartościami 1, null, 2 i ponownie null.

Które pary wartości zajmą tyle samo 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', 'Вася', 10, 10, 'Д', 'Вася');

A i X
B i Y
C i K
C i Z
K. i Z
ja i j
J. i X
Wszystkie wymienione

odpowiedźOto fragmenty dokumentacji (12.1.0.2) dotyczącej przechowywania różnych typów danych w Oracle.

Typ danych CHAR
Typ danych CHAR określa ciąg znaków o stałej długości w zestawie znaków bazy danych. Zestaw znaków bazy danych określasz podczas tworzenia bazy danych. Oracle zapewnia, że wszystkie wartości przechowywane w kolumnie CHAR mają długość określoną przez size w wybranej semantyce długości. Jeśli wstawisz wartość krótszą niż długość kolumny, Oracle dopasuje tę wartość do długości kolumny.

Typ danych VARCHAR2
Typ danych VARCHAR2 określa ciąg znaków o zmiennej długości w zestawie znaków bazy danych. Zestaw znaków bazy danych określasz podczas tworzenia bazy danych. Oracle przechowuje wartość znakową w kolumnie VARCHAR2 dokładnie tak, jak ją określisz, bez żadnych odstępów, pod warunkiem, że wartość nie przekracza długości kolumny.

Typ danych NUMBER
Typ danych NUMBER przechowuje zero oraz dodatnie 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, wówczas Oracle zwróci błąd. Każda wartość NUMBER wymaga od 1 do 22 bajtów. Biorąc to pod uwagę, wielkość kolumny w bajtach dla określonej wartości danych liczbowych LICZBA(p), gdzie p jest precyzją danej wartości, można obliczyć za pomocą następującego wzoru: OKRĄG((długość(p)+s)/2))+1 gdzie s równa się zero, jeśli liczba jest dodatnia, i s równa się 1, jeśli liczba jest ujemna.

Dodatkowo weźmy fragment dokumentacji dotyczący przechowywania wartości Null.

Wartość null oznacza brak wartości w kolumnie. Wartości null oznaczają brakujące, nieznane lub niemające zastosowania dane. Wartości null są przechowywane w bazie danych, jeśli mieszczą się pomiędzy kolumnami z wartościami danych. W takich przypadkach wymagają 1 bajtu do przechowywania długości kolumny (zero). Końcowe wartości null w wierszu nie wymagają przechowywania, ponieważ nowy nagłówek wiersza sygnalizuje, że pozostałe kolumny w poprzednim wierszu mają wartość null. Na przykład, jeśli ostatnie trzy kolumny tabeli mają wartość null, wówczas żadne dane nie są w nich przechowywane.

Na podstawie tych danych budujemy rozumowanie. Zakładamy, że baza danych wykorzystuje kodowanie AL32UTF8. W tym kodowaniu rosyjskie litery będą zajmować 2 bajty.

1) A i X, wartość pola a 'Y' zajmuje 1 bajt, wartość pola x 'D' zajmuje 2 bajty
2) B i Y, „Vasya” w b wartość zostanie uzupełniona spacjami do 10 znaków i zajmie 14 bajtów, „Vasya” w d zajmie 8 bajtów.
3) C i K. Obydwa pola mają wartość NULL, po nich znajdują się pola znaczące, więc zajmują 1 bajt.
4) C i Z. Obydwa pola mają wartość NULL, ale pole Z jest ostatnim w tabeli, więc 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. Według dokumentacji obie wartości zajmą 2 bajty. Długość obliczamy korzystając ze wzoru zaczerpniętego z dokumentacji: round( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J i X. Wartość w polu J zajmie 2 bajty, wartość w polu X zajmie 2 bajty.

W sumie poprawne opcje to: C i K, I i J, J i X.

Jaki będzie w przybliżeniu współczynnik grupowania 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);

Około dziesiątek
Około setek
O tysiącach
Około dziesiątek tysięcy

odpowiedźWedług dokumentacji Oracle (cytowanej z 12.1):

W przypadku indeksu B-drzewa współczynnik grupowania indeksu mierzy fizyczne grupowanie wierszy w odniesieniu do wartości indeksu.

Współczynnik grupowania indeksów pomaga optymalizatorowi zdecydować, czy w przypadku niektórych zapytań bardziej wydajne jest skanowanie indeksu, czy skanowanie pełnej tabeli). Niski współczynnik grupowania wskazuje na wydajne skanowanie indeksu.

Współczynnik grupowania zbliżony do liczby bloków w tabeli wskazuje, że wiersze w blokach tabeli są fizycznie uporządkowane według klucza indeksu. Jeśli baza danych wykonuje skanowanie pełnej tabeli, wówczas baza danych ma tendencję do pobierania wierszy przechowywanych na dysku, posortowanych według klucza indeksu. Współczynnik grupowania zbliżony do liczby wierszy wskazuje, że wiersze są losowo rozproszone w blokach bazy danych w odniesieniu do klucza indeksu. Jeśli baza danych przeskanuje pełną tabelę, nie pobierze wierszy w żadnej kolejności posortowanej według tego klucza indeksu.

W tym przypadku dane są idealnie posortowane, więc współczynnik grupowania będzie równy lub bliski liczbie zajętych bloków w tabeli. Dla standardowego rozmiaru bloku wynoszącego 8 kilobajtów można spodziewać się, że w jednym bloku zmieści się około tysiąca wąskich wartości liczbowych, więc liczba bloków, a co za tym idzie, współczynnik grupowania będzie wynosić około dziesiątek.

Przy jakich wartościach N następujący skrypt zostanie pomyślnie wykonany w zwykłej bazie danych ze standardowymi 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źWedług dokumentacji Oracle (cytowanej z 11.2):

Limity logicznej bazy danych

Pozycja
Rodzaj limitu
Wartość graniczna

Indeksy
Całkowity rozmiar indeksowanej kolumny
75% rozmiaru bloku bazy danych minus pewne obciążenie

Zatem całkowity rozmiar indeksowanych kolumn nie powinien przekraczać 6 KB. To, co stanie się dalej, zależy od wybranego kodowania podstawowego. W przypadku kodowania AL32UTF8 jeden znak może zajmować maksymalnie 4 bajty, więc w najgorszym przypadku na 6 kilobajtach zmieści się około 1500 znaków. Dlatego Oracle nie pozwoli na utworzenie indeksu przy N = 400 (kiedy w najgorszym przypadku długość klucza wynosi 1600 znaków * 4 bajty + długość wiersza), podczas gdy przy N = 200 (lub mniej) utworzenie indeksu będzie działać bez problemów.

Operator INSERT ze wskazówką APPEND przeznaczony jest do ładowania danych w trybie bezpośrednim. Co się stanie, jeśli zostanie przyłożony do stołu, na którym wisi spust?

Dane zostaną załadowane w trybie bezpośrednim, wyzwalacz będzie działał zgodnie z oczekiwaniami
Dane zostaną załadowane w trybie bezpośrednim, ale wyzwalacz nie zostanie wykonany
Dane zostaną załadowane w trybie tradycyjnym, wyzwalacz będzie działał tak, jak powinien
Dane zostaną załadowane w trybie konwencjonalnym, ale wyzwalacz nie zostanie wykonany
Dane nie zostaną załadowane, zostanie zarejestrowany błąd

odpowiedźZasadniczo jest to raczej kwestia logiki. Aby znaleźć poprawną odpowiedź, sugeruję następujący model rozumowania:

Wstawianie w trybie bezpośrednim odbywa się poprzez bezpośrednie utworzenie bloku danych z pominięciem silnika SQL, co zapewnia dużą prędkość. Zatem zapewnienie wykonania spustu jest bardzo trudne, jeśli nie niemożliwe, i nie ma to sensu, ponieważ nadal radykalnie spowolni to wkładanie.
Niewykonanie wyzwalacza spowoduje, że w przypadku, gdy dane w tabeli będą takie same, stan bazy jako całości (pozostałych tabel) będzie zależał od trybu, w jakim te dane zostały wstawione. To oczywiście zniszczy integralność danych i nie może być stosowane jako rozwiązanie w produkcji.
Niemożność wykonania żądanej operacji jest ogólnie traktowana jako błąd. Ale tutaj powinniśmy pamiętać, że APPEND jest wskazówką, a ogólna logika podpowiedzi jest taka, że są one brane pod uwagę, jeśli to możliwe, ale jeśli nie, operator jest wykonywany bez uwzględnienia podpowiedzi.

Zatem oczekiwana odpowiedź brzmi dane zostaną załadowane w trybie normalnym (SQL), wyzwalacz zostanie uruchomiony.

Według dokumentacji Oracle (cytowanej z 8.04):

Naruszenie ograniczeń spowoduje, że instrukcja zostanie wykonana szeregowo, przy użyciu konwencjonalnej ścieżki wstawiania, bez ostrzeżeń i komunikatów o błędach. Wyjątkiem jest ograniczenie instrukcji uzyskujących dostęp do tej samej tabeli więcej niż raz w transakcji, co może powodować komunikaty o błędach.
Na przykład, jeśli w tabeli znajdują się wyzwalacze lub integralność referencyjna, wskazówka DOŁĄCZ zostanie zignorowana przy próbie użycia bezpośredniego ładowania INSERT (szeregowo lub równolegle), a także wskazówka lub klauzula PARALLEL, jeśli taka istnieje.

Co się stanie po wykonaniu poniższego 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);

Pomyślna realizacja
Niepowodzenie z powodu błędu składni
Błąd: transakcja autonomiczna jest nieprawidłowa
Błąd związany z przekroczeniem maksymalnego zagnieżdżenia wywołań
Błąd naruszenia klucza obcego
Błąd związany z blokadami

odpowiedźTabela i wyzwalacz są utworzone całkiem poprawnie i operacja ta nie powinna powodować problemów. Dozwolone są również transakcje autonomiczne w wyzwalaczu, w przeciwnym razie np. logowanie nie byłoby możliwe.

Po wstawieniu pierwszego wiersza pomyślne uruchomienie wyzwalacza spowodowałoby wstawienie drugiego wiersza, powodując ponowne uruchomienie wyzwalacza, wstawienie trzeciego wiersza i tak dalej, aż do niepowodzenia instrukcji z powodu przekroczenia maksymalnego zagnieżdżenia wywołań. Jednak w grę wchodzi jeszcze jeden subtelny punkt. W momencie wykonania wyzwalacza zatwierdzenie pierwszego wstawionego rekordu nie zostało jeszcze zakończone. Dlatego też wyzwalacz działający w transakcji autonomicznej próbuje wstawić do tabeli wiersz odwołujący się do klucza obcego do rekordu, który nie został jeszcze zatwierdzony. Powoduje to oczekiwanie (transakcja autonomiczna czeka na zatwierdzenie transakcji głównej, aby sprawdzić, czy może wstawić dane), a jednocześnie transakcja główna czeka, aż transakcja autonomiczna będzie kontynuować działanie po wyzwoleniu. Następuje zakleszczenie, w efekcie czego transakcja autonomiczna zostaje anulowana z przyczyn związanych z blokadami.

W ankiecie mogą brać udział tylko zarejestrowani użytkownicy. Zaloguj się, Proszę.

Trudno było?

Jak dwa palce, od razu zdecydowałem wszystko poprawnie.
Niezupełnie, pomyliłem się w kilku kwestiach.
Połowę rozwiązałem poprawnie.
Zgadłem odpowiedź dwa razy!
Napiszę w komentarzach

Głosowało 14 użytkowników. 10 użytkowników wstrzymało się od głosu.

Źródło: www.habr.com

Podążając śladami Highload++ Siberia 2019 - 8 zadań na Oracle

Jaka jest maksymalna wartość sekwencji, którą zobaczymy w wyniku wykonania poniższego skryptu?

Ile wierszy będzie w tabeli po uruchomieniu poniższego skryptu?

Ile wierszy będzie w tabeli po uruchomieniu poniższego skryptu?

Które pary wartości zajmą tyle samo miejsca w bloku?

Jaki będzie w przybliżeniu współczynnik grupowania indeksu T_I?

Przy jakich wartościach N następujący skrypt zostanie pomyślnie wykonany w zwykłej bazie danych ze standardowymi ustawieniami?

Operator INSERT ze wskazówką APPEND przeznaczony jest do ładowania danych w trybie bezpośrednim. Co się stanie, jeśli zostanie przyłożony do stołu, na którym wisi spust?

Co się stanie po wykonaniu poniższego skryptu?

Trudno było?

Dodaj komentarz Anuluj odpowiedź