🥇Po stopách Highload++ Siberia 2019 — 8 úloh na Oracle

Ahoj!

Ve dnech 24-25 června se v Novosibirsku konala konference Highload++ Siberia 2019. Naši kluci tam také byli. přednáškou Téma "Oracle Container Databases (CDB/PDB) a jejich praktické využití pro vývoj softwaru", textovou verzi zveřejníme trochu později. Bylo to skvělé, děkujeme. olegbunin za organizaci, a také všem, kdo přišli.

V tomto příspěvku bychom se s vámi chtěli podělit o úkoly, které byly na našem stánku, abyste si mohli otestovat své znalosti v Oracle. Pod katem – 8 úkolů, varianty odpovědí a vysvětlení.

Jaká je maximální hodnota sekvence, kterou uvidíme jako výsledek provedení následujícího skriptu?

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
Žádná, dojde k chybě.

OdpověďPodle dokumentace Oracle (citované z 8.1.6):
V rámci jednoho SQL příkazu Oracle inkrementuje sekvenci pouze jednou na řádek. Pokud příkaz obsahuje více odkazů na NEXTVAL pro sekvenci, Oracle sekvenci inkrementuje jednou a vrátí stejnou hodnotu pro všechny výskyty NEXTVAL. Pokud příkaz obsahuje odkazy jak na CURRVAL, tak na NEXTVAL, Oracle sekvenci inkrementuje a vrátí stejnou hodnotu pro CURRVAL i NEXTVAL bez ohledu na jejich pořadí v příkazu.

Tedy, maximální hodnota odpovídá počtu řádků, tedy 5..

Kolik řádků bude v tabulce jako výsledek provedení následujícího skriptu?

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);

OdpověďPodle dokumentace Oracle (citované z 11.2):

Před provedením jakéhokoli SQL příkazu označuje Oracle implicitní bod uložení (který není k dispozici pro vás). Pokud příkaz selže, Oracle jej automaticky vrátí zpět a vrátí příslušný chybový kód do SQLCODE v SQLCA. Například, pokud příkaz INSERT způsobí chybu tím, že se pokusí vložit duplicitní hodnotu do unikátního indexu, příkaz je vrácen zpět.

Volání stored procedury z klienta je také považováno a zpracováváno jako jediné prohlášení. Tedy první volání stored procedury úspěšně končí vložením tří záznamů; druhé volání stored procedury končí chybou a vrací čtvrtý záznam zpět, který se podařilo vložit; třetí volání končí chybou, a v tabulce zůstávají tři záznamy..

Kolik řádků bude v tabulce jako výsledek provedení následujícího skriptu?

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;
/

OdpověďPodle dokumentace Oracle (citované z 11.2):

Omezení typu check umožňuje specifikovat podmínku, kterou musí každ řádek v tabulce splňovat. Aby byla podmínka splněna, musí každý řádek v tabulce učinit podmínku buď PRAVDIVOU, nebo neznámou (kvůli null). Když Oracle vyhodnocuje podmínku omezení typu check pro konkrétní řádek, jakékoli názvy sloupců v podmínce odkazují na hodnoty sloupců v tomto řádku.

Takže hodnota null projde kontrolou a anonymní blok se úspěšně vykoná až do pokusu vložit hodnotu 3. Po tomto bloku zpracování chyb vyzvedne výjimku, k rollbacku nedojde a v tabulce zůstanou čtyři řádky s hodnotami 1, null, 2 a znovu null.

Jaké dvojice hodnot zabírají stejný objem místa v 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', 'Václav', 10, 10, 'D', 'Václav');

A a X
B a Y
C a K
C a Z
K a Z
I a J
J a X
Všechny uvedené

OdpověďUvádíme úryvky z dokumentace (12.1.0.2) o ukládání různých typů dat v Oracle.

CHAR Datový typ
Datový typ CHAR specifikuje řetězec znaků pevné délky v znakové sadě databáze. Znakovou sadu databáze specifikujete při vytváření databáze. Oracle zajišťuje, že všechny hodnoty uložené v sloupci CHAR mají délku určenou velikostí v zvolených pravidlech délky. Pokud vložíte hodnotu, která je kratší než délka sloupce, Oracle tuto hodnotu doplní prázdnými znaky na délku sloupce.

VARCHAR2 Datový typ
Datový typ VARCHAR2 specifikuje řetězec znaků proměnné délky v znakové sadě databáze. Znakovou sadu databáze specifikujete při vytváření databáze. Oracle ukládá znakovou hodnotu v sloupci VARCHAR2 přesně tak, jak ji specifikujete, bez jakéhokoli doplňování prázdnými znaky, pokud hodnota nepřesáhne délku sloupce.

NUMBER Datový typ
Datový typ NUMBER ukládá nulu, stejně jako kladná a záporná čísla s absolutními hodnotami od 1.0 x 10-130 do, ale nikoli včetně, 1.0 x 10^126. Pokud specifikujete aritmetický výraz, jehož hodnota má absolutní hodnotu větší nebo rovnu 1.0 x 10^126, Oracle vrátí chybu. Každá hodnota NUMBER vyžaduje od 1 do 22 bytů. Vzhledem k tomu lze velikost sloupce v bytech pro konkrétní číselnou hodnotu NUMBER(p), kde p je přesnost dané hodnoty, vypočítat pomocí následujícího vzorce: ROUND((length(p)+s)/2))+1 kde s je nula, pokud je číslo kladné, a s je 1, pokud je číslo záporné.

Dále uvedeme úryvek z dokumentace o ukládání hodnot Null.

Null je absence hodnoty ve sloupci. Nully označují chybějící, neznámá nebo nevhodná data. Nully jsou ukládány v databázi, pokud spadají mezi sloupce s hodnotami dat. V těchto případech vyžadují 1 byte pro uložení délky sloupce (nula). Koncové nully v řádku nevyžadují úložiště, protože nový záhlaví řádku signalizuje, že zbývající sloupce v předchozím řádku jsou null. Například, pokud jsou poslední tři sloupce tabulky null, pak pro tyto sloupce nejsou žádná data uložena.

Na základě těchto údajů formulujeme úvahu. Předpokládáme, že v BD se používá kódování AL32UTF8. V tomto kódování budou ruské znaky zabírat 2 bajty.

1) A a X, hodnota pole a ‘Y’ zabírá 1 byte, hodnota pole x ‘D’ – 2 bajty.
2) B a Y, 'Václav' v b bude doplněn mezerami do 10 znaků a zabere 14 bajtů, 'Václav' v d – zabere 8 bajtů.
3) C a K. Obě pole mají hodnotu NULL, za nimi jsou smysluplná pole, proto zabírají po 1 bajtu.
4) C a Z. Obě pole mají hodnotu NULL, ale pole Z je poslední v tabulce, proto místo nezabírá (0 bajtů). Pole C zabírá 1 bajt.
5) K a Z. Podobně jako v předchozím případě. Hodnota v poli K zabírá 1 bajt, v Z – 0.
6) I a J. Podle dokumentace obě hodnoty zabírají po 2 bajtech. Délku počítáme podle vzorce z dokumentace: round((1 + 0)/2) + 1 = 1 + 1 = 2.
7) J a X. Hodnota v poli J zabere 2 bajty, hodnota v poli X také zabere 2 bajty.

Celkově správné varianty: C a K, I a J, J a X.

Jaký bude přibližně clustering factor u indexu 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);

Počet desítek
Počet stovek
Počet tisíc
Počet desítek tisíc

OdpověďPodle dokumentace Oracle (cituje se z 12.1):

Pro B-stromový index měří clustering factor fyzické seskupení řádků vzhledem k hodnotě indexu.

Clustering factor pomáhá optimalizátoru rozhodnout, zda je efektivnější indexové skenování nebo úplné skenování tabulky pro určité dotazy. Nízký clustering factor naznačuje efektivní indexové skenování.

Clustering factor, který je blízký počtu bloků v tabulce, naznačuje, že řádky jsou fyzicky seřazeny v blokách tabulky podle klíče indexu. Pokud databáze provádí úplné skenování tabulky, pak má tendenci získávat řádky tak, jak jsou na disku uloženy seřazené podle klíče indexu. Clustering factor, který je blízký počtu řádků, naznačuje, že řádky jsou náhodně rozptýleny napříč bloky databáze vzhledem k klíči indexu. Pokud databáze provádí úplné skenování tabulky, pak by nevytahovala řádky v žádném seřazeném pořadí podle tohoto klíče indexu.

V tomto případě jsou data ideálně seřazena, takže clustering factor bude roven nebo blízký počtu obsazených bloků v tabulce. Při standardní velikosti bloku 8 kilobajtů lze očekávat, že do jednoho bloku se vejde přibližně tisíc úzkých číselných hodnot, proto počet bloků a tím i clustering factor bude počet desítek.

Při jakých hodnotách N bude následující skript úspěšně proveden ve standardní databázi s běžným nastavením?

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

OdpověďPodle dokumentace Oracle (citované z 11.2):

Logické limity databáze

Položka
Typ limitu
Hodnota limitu

Indexy
Celková velikost indexovaného sloupce
75 % velikosti databázového bloku minus nějaké režijní náklady

Celková velikost indexovaných sloupců by neměla překročit 6 KB. Další závisí na vybraném kódování databáze. Pro kódování AL32UTF8 může jeden znak zabírat maximálně 4 bajty, takže do 6 kilobajtů se v nejhorším případě vejde přibližně 1500 znaků. Oracle proto zakáže vytvoření indexu při N = 400 (kdy délka klíče v nejhorším případě bude 1600 znaků * 4 bajty + délka rowid), zatímco při N = 200 (a méně) vytvoření indexu proběhne bez problémů.

Operátor INSERT s hintonem APPEND je určen k načítání dat v přímém režimu. Co se stane, pokud bude použit na tabulce, na které je trigger?

Data budou načtena v přímém režimu, trigger se aktivuje, jak má.
Data budou načtena v přímém režimu, ale trigger se neprovede.
Data budou načtena v konvenčním režimu, trigger se aktivuje, jak má.
Data budou načtena v konvenčním režimu, ale trigger se neprovede.
Data nebudou načtena, dojde k chybě.

OdpověďV podstatě je to otázka více logiky. Pro nalezení správné odpovědi bych navrhl následující model uvažování:

Vložení v přímém režimu se provádí přímým vytvářením bloku dat, mimo SQL engine, což zajišťuje vysokou rychlost. Tím pádem je zajištění provedení triggeru velmi obtížné, pokud vůbec možné, a nemá to smysl, protože by to stejně zásadně zpomalilo vložení.
Nepoužití triggeru povede k tomu, že při stejných datech v tabulce bude celkový stav databáze (dalších tabulek) závislý na tom, v jakém režimu byla tato data vložena. To zjevně naruší integritu dat a nemůže být aplikováno jako řešení v produkčním prostředí.
Nemožnost provést požadovanou operaci se obecně chápe jako chyba. Ale zde je třeba si připomenout, že APPEND je hint a obecná logika hintů spočívá v tom, že se zohledňují, pokud je to možné, pokud ne, operátor se provádí bez ohledu na hint.

Takže očekávaná odpověď je: data budou načtena v běžném (SQL) režimu, trigger se aktivuje.

Podle dokumentace Oracle (citováno z 8.04):

Porušení omezení způsobí, že se příkaz provede sériově, pomocí konvenčního vkládacího postupu, bez varování nebo chybových zpráv. Výjimkou je omezení na příkazy přistupující k té stejné tabulce více než jednou v transakci, což může způsobit chybové zprávy.
Například pokud se na tabulce nacházejí spouště nebo referenční integrita, bude náznak APPEND ignorován, když se pokusíte použít přímé načítání INSERT (seriálně nebo paralelně), stejně jako jakýkoli náznak nebo klauzule PARALLEL.

Co se stane při provedení následujícího skriptu?

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);

Úspěšné provedení
Selhání kvůli syntaktické chybě
Chyba související s neplatností autonomní transakce
Chyba související s překročením maximální hloubky volání
Chyba související s porušením cizího klíče
Chyba související se zámky

OdpověďTabulka a spouštěč jsou vytvořeny zcela správně a tato operace by neměla způsobit problémy. Autonomní transakce ve spouštěči jsou také povoleny, jinak by například nebylo možné logování.

Po vložení prvního řádku úspěšné spuštění spouštěče by vedlo k vložení druhého řádku, což by znovu vyvolalo spouštěč, vložilo by se třetí řádek a tak dále, dokud by prohlášení nepadlo kvůli překročení maximální hloubky volání. Nicméně, nastává ještě jeden jemný moment. V okamžiku, kdy se spouštěč vykonává, pro první vložený záznam ještě nebyl proveden commit. Proto se spouštěč, pracující v autonomní transakci, snaží vložit do tabulky řádek, který se odkazuje cizím klíčem na ještě nezakompitovaný záznam. To vede k čekání (autonomní transakce čeká na commit hlavní, aby pochopila, zda může vložit data) a současně čeká hlavní transakce na commit autonomní, aby mohla pokračovat po spouštěči. Nastává deadlock a následně – autonomní transakce je přerušena z důvodu souvisejícího se zámky..

Pouze registrovaní uživatelé se mohou účastnit ankety. Přihlaste se, prosím.