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

Ahoj!

V dňoch 24. – 25. júna sa v Novosibirsku konala konferencia Highload++ Siberia 2019. Boli tam aj naši chalani správa “Kontajnerové databázy Oracle (CDB/PDB) a ich praktické využitie pri vývoji softvéru”, textovú verziu zverejníme o niečo neskôr. Bolo to v pohode, ďakujem olegbunín za organizáciu, ako aj všetkým, ktorí prišli.

V tomto príspevku by sme sa s vami chceli podeliť o problémy, ktoré sme mali na našom stánku, aby ste si mohli otestovať svoje znalosti Oracle. Pod strihom je 8 úloh, možnosti odpovedí a vysvetlenie.

Aká je maximálna hodnota sekvencie, ktorú uvidíme ako výsledok vykonania nasledujúceho 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
• Nie, dôjde k chybe

odpoveďPodľa dokumentácie Oracle (citované z 8.1.6):
V rámci jedného príkazu SQL bude Oracle zvyšovať sekvenciu iba raz za riadok. Ak príkaz obsahuje viac ako jeden odkaz na NEXTVAL pre sekvenciu, Oracle zvýši sekvenciu raz a vráti rovnakú hodnotu pre všetky výskyty NEXTVAL. Ak príkaz obsahuje odkazy na CURRVAL aj NEXTVAL, Oracle zvýši postupnosť a vráti rovnakú hodnotu pre CURRVAL aj NEXTVAL bez ohľadu na ich poradie v rámci príkazu.

To znamená, že maximálna hodnota bude zodpovedať počtu riadkov, teda 5.

Koľko riadkov bude v tabuľke v dôsledku spustenia nasledujúceho 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);

• 0
• 3
• 4
• 5
• 6
• 9

odpoveďPodľa dokumentácie Oracle (citované z 11.2):

Pred vykonaním akéhokoľvek príkazu SQL Oracle označí implicitný bod uloženia (nie je vám k dispozícii). Potom, ak príkaz zlyhá, Oracle ho automaticky vráti späť a vráti príslušný chybový kód do SQLCODE v SQLCA. Ak napríklad príkaz INSERT spôsobí chybu pri pokuse o vloženie duplicitnej hodnoty do jedinečného indexu, príkaz sa vráti späť.

Volanie do HP od klienta sa tiež považuje a spracováva ako jeden výpis. Prvý hovor HP sa teda úspešne dokončí po vložení troch záznamov; druhé volanie HP skončí chybou a vráti späť štvrtý záznam, ktorý sa mu podarilo vložiť; tretí hovor zlyhá, a v tabuľke sú tri záznamy.

Koľko riadkov bude v tabuľke v dôsledku spustenia nasledujúceho 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;
/

• 1
• 2
• 3
• 4
• 5
• 6
• 7

odpoveďPodľa dokumentácie Oracle (citované z 11.2):

Kontrolné obmedzenie vám umožňuje zadať podmienku, ktorú musí spĺňať každý riadok v tabuľke. Aby sa splnilo obmedzenie, každý riadok v tabuľke musí mať podmienku buď TRUE alebo neznámu (kvôli nule). Keď Oracle vyhodnotí podmienku kontroly pre konkrétny riadok, všetky názvy stĺpcov v podmienke odkazujú na hodnoty stĺpcov v danom riadku.

Hodnota null teda prejde kontrolou a anonymný blok bude úspešne vykonaný až do pokusu o vloženie hodnoty 3. Potom blok spracovania chýb vymaže výnimku, nedôjde k návratu a v tabuľke zostanú štyri riadky s hodnotami 1, null, 2 a znova null.

Ktoré dvojice hodnôt zaberú v bloku rovnaké množstvo miesta?

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 a X
• B a Y
• C a K
• C a Z
• K a Z
• Ja a J
• J a X
• Všetky uvedené

odpoveďTu sú výňatky z dokumentácie (12.1.0.2) o ukladaní rôznych typov údajov v Oracle.

Typ údajov CHAR
Typ údajov CHAR určuje reťazec znakov s pevnou dĺžkou v znakovej sade databázy. Znakovú sadu databázy zadávate pri vytváraní databázy. Oracle zaisťuje, že všetky hodnoty uložené v stĺpci CHAR majú dĺžku určenú veľkosťou vo vybratej sémantike dĺžky. Ak vložíte hodnotu, ktorá je kratšia ako dĺžka stĺpca, Oracle túto hodnotu doplní do dĺžky stĺpca.

Typ údajov VARCHAR2
Typ údajov VARCHAR2 špecifikuje reťazec znakov s premenlivou dĺžkou v znakovej sade databázy. Znakovú sadu databázy zadávate pri vytváraní databázy. Oracle ukladá hodnotu znaku do stĺpca VARCHAR2 presne tak, ako ju určíte, bez akýchkoľvek prázdnych výplní za predpokladu, že hodnota nepresahuje dĺžku stĺpca.

NUMBER Typ údajov
Dátový typ ČÍSLO ukladá nulu, ako aj kladné a záporné pevné čísla s absolútnymi hodnotami od 1.0 x 10-130 do 1.0 x 10126, ale nie vrátane. Ak zadáte aritmetický výraz, ktorého hodnota má absolútnu hodnotu väčšiu alebo rovnú 1.0 x 10126, potom Oracle vráti chybu. Každá hodnota NUMBER vyžaduje od 1 do 22 bajtov. Ak to vezmeme do úvahy, veľkosť stĺpca v bajtoch pre konkrétnu číselnú hodnotu údajov NUMBER(p), kde p je presnosť danej hodnoty, možno vypočítať pomocou nasledujúceho vzorca: ROUND((dĺžka(p)+s)/2))+1 kde s sa rovná nule, ak je číslo kladné, a s sa rovná 1, ak je číslo záporné.

Okrem toho si zoberme úryvok z dokumentácie o ukladaní hodnôt Null.

Nula je absencia hodnoty v stĺpci. Nuly označujú chýbajúce, neznáme alebo nepoužiteľné údaje. Nulové hodnoty sú uložené v databáze, ak spadajú medzi stĺpce s hodnotami údajov. V týchto prípadoch vyžadujú 1 bajt na uloženie dĺžky stĺpca (nula). Koncové hodnoty null v riadku nevyžadujú žiadny úložný priestor, pretože hlavička nového riadka signalizuje, že zostávajúce stĺpce v predchádzajúcom riadku sú prázdne. Ak sú napríklad posledné tri stĺpce tabuľky prázdne, pre tieto stĺpce sa neuložia žiadne údaje.

Na základe týchto údajov vytvárame úvahy. Predpokladáme, že databáza používa kódovanie AL32UTF8. V tomto kódovaní budú ruské písmená zaberať 2 bajty.

1) A a X, hodnota poľa a 'Y' má 1 bajt, hodnota poľa x 'D' 2 bajty
2) B a Y, 'Vasya' v b hodnota bude doplnená medzerami do 10 znakov a bude trvať 14 bajtov, 'Vasya' v d bude trvať 8 bajtov.
3) C a K. Obe polia majú hodnotu NULL, za nimi sú významné polia, takže zaberajú 1 bajt.
4) C a Z. Obe polia majú hodnotu NULL, ale pole Z je posledné v tabuľke, takže nezaberá miesto (0 bajtov). Pole C zaberá 1 bajt.
5) K a Z. Podobne ako v predchádzajúcom prípade. Hodnota v poli K zaberá 1 bajt, v Z – 0.
6) I a J. Podľa dokumentácie budú obe hodnoty trvať 2 bajty. Dĺžku vypočítame pomocou vzorca prevzatého z dokumentácie: round( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J a X. Hodnota v poli J bude trvať 2 bajty, hodnota v poli X bude trvať 2 bajty.

Celkovo sú správne možnosti: C a K, I a J, J a X.

Aký približne bude zhlukovací faktor 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);

• Asi desiatky
• Asi stovky
• Asi tisícky
• Asi desaťtisíce

odpoveďPodľa dokumentácie Oracle (citované z 12.1):

Pre index B-stromu faktor zoskupovania indexov meria fyzické zoskupenie riadkov vo vzťahu k hodnote indexu.

Faktor klastrovania indexov pomáha optimalizátoru rozhodnúť, či je pre určité dotazy efektívnejšie prehľadávanie indexu alebo úplné prehľadávanie tabuľky). Nízky klastrovací faktor indikuje efektívne indexové skenovanie.

Faktor klastrovania, ktorý je blízky počtu blokov v tabuľke, naznačuje, že riadky sú fyzicky usporiadané v blokoch tabuľky podľa indexového kľúča. Ak databáza vykoná úplné skenovanie tabuľky, potom má databáza tendenciu získavať riadky tak, ako sú uložené na disku zoradené podľa indexového kľúča. Faktor zhlukovania, ktorý je blízky počtu riadkov, naznačuje, že riadky sú náhodne rozptýlené po blokoch databázy vo vzťahu k indexovému kľúču. Ak databáza vykoná úplné skenovanie tabuľky, potom by databáza nenačítala riadky v žiadnom zoradenom poradí podľa tohto indexového kľúča.

V tomto prípade sú dáta ideálne zoradené, takže zhlukovací faktor bude rovný alebo blízky počtu obsadených blokov v tabuľke. Pri štandardnej veľkosti bloku 8 kilobajtov môžete očakávať, že do jedného bloku sa zmestí asi tisíc úzkych číselných hodnôt, takže počet blokov a v dôsledku toho bude zhlukovací faktor o desiatkach.

Pri akých hodnotách N bude nasledujúci skript úspešne vykonaný v bežnej databáze so štandardnými nastaveniami?

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

odpoveďPodľa dokumentácie Oracle (citované z 11.2):

Logické limity databázy

Položka
Typ limitu
Limitná hodnota

Indexy
Celková veľkosť indexovaného stĺpca
75 % veľkosti bloku databázy mínus nejaká réžia

Celková veľkosť indexovaných stĺpcov by teda nemala presiahnuť 6 kB. Čo sa stane ďalej, závisí od zvoleného základného kódovania. Pri kódovaní AL32UTF8 môže jeden znak zaberať maximálne 4 bajty, takže v najhoršom prípade sa do 6 kilobajtov zmestí asi 1500 znakov. Preto Oracle nepovolí vytváranie indexu pri N = 400 (keď je dĺžka kľúča v najhoršom prípade 1600 znakov * 4 bajty + dĺžka rowid), zatiaľ čo pri N = 200 (alebo menej) vytvorenie indexu bude fungovať bez problémov.

Operátor INSERT s nápovedou APPEND je určený na načítanie údajov v priamom režime. Čo sa stane, ak sa aplikuje na stôl, na ktorom visí spúšť?

• Dáta sa načítajú v priamom režime, spúšť bude fungovať podľa očakávania
• Dáta sa načítajú v priamom režime, ale spúšťač sa nevykoná
• Dáta sa načítajú v konvenčnom režime, spúšť bude fungovať tak, ako má
• Dáta sa načítajú v konvenčnom režime, ale spúšťač sa nevykoná
• Údaje sa nenačítajú, zaznamená sa chyba

odpoveďV podstate je to skôr otázka logiky. Na nájdenie správnej odpovede by som navrhol nasledujúci model uvažovania:

1. Vkladanie v priamom režime sa vykonáva priamym vytvorením dátového bloku, obchádzaním SQL motora, čo zaisťuje vysokú rýchlosť. Zabezpečiť vykonanie spúšte je teda veľmi ťažké, ak nie nemožné, a nemá to zmysel, pretože to aj tak radikálne spomalí zasúvanie.
2. Nespustenie spúšťača povedie k tomu, že ak sú údaje v tabuľke rovnaké, stav databázy ako celku (ostatné tabuľky) bude závisieť od režimu, v ktorom boli tieto údaje vložené. To samozrejme zničí integritu údajov a nedá sa použiť ako riešenie vo výrobe.
3. Neschopnosť vykonať požadovanú operáciu sa vo všeobecnosti považuje za chybu. Tu by sme však mali pamätať na to, že APPEND je nápoveda a všeobecná logika rád je taká, že ak je to možné, berú sa do úvahy, ale ak nie, operátor sa vykoná bez zohľadnenia nápovedy.

Takže očakávaná odpoveď je údaje sa načítajú v normálnom (SQL) režime, spustí sa spúšťač.

Podľa dokumentácie Oracle (citované z 8.04):

Porušenie obmedzení spôsobí, že sa príkaz vykoná sériovo s použitím konvenčnej vstupnej cesty bez varovaní alebo chybových hlásení. Výnimkou je obmedzenie príkazov pristupujúcich k rovnakej tabuľke viac ako raz v transakcii, čo môže spôsobiť chybové hlásenia.
Napríklad, ak sú v tabuľke prítomné spúšťače alebo referenčná integrita, pomôcka APPEND bude ignorovaná, keď sa pokúsite použiť INSERT s priamym načítaním (sériové alebo paralelné), ako aj pomôcku alebo klauzulu PARALLEL, ak existuje.

Čo sa stane, keď sa spustí nasledujúci skript?

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

• Úspešné ukončenie
• Zlyhanie v dôsledku chyby syntaxe
• Chyba: Autonómna transakcia nie je platná
• Chyba súvisiaca s prekročením maximálneho počtu vnorení hovorov
• Chyba porušenia cudzieho kľúča
• Chyba súvisiaca so zámkami

odpoveďTabuľka a spúšť sú vytvorené celkom správne a táto operácia by nemala viesť k problémom. Povolené sú aj autonómne transakcie v spúšťači, inak by napríklad nebolo možné protokolovanie.

Po vložení prvého riadku by úspešné spustenie spúšťača spôsobilo vloženie druhého riadku, čo by spôsobilo opätovné spustenie spúšťača, vloženie tretieho riadku a tak ďalej, až kým príkaz zlyhal z dôvodu prekročenia maximálneho vnorenia volaní. Do hry však vstupuje ešte jeden jemný bod. V čase, keď je spúšťač vykonaný, potvrdenie ešte nebolo dokončené pre prvý vložený záznam. Preto sa spúšťač spustený v autonómnej transakcii pokúša vložiť do tabuľky riadok, ktorý odkazuje na cudzí kľúč na záznam, ktorý ešte nebol potvrdený. Výsledkom je čakanie (autonómna transakcia čaká na potvrdenie hlavnej transakcie, aby zistila, či môže vložiť dáta) a zároveň hlavná transakcia čaká, kým autonómna transakcia po spustení bude pokračovať v práci. Nastane uviaznutie a v dôsledku toho sa autonómna transakcia zruší z dôvodov súvisiacich so zámkami.

Do prieskumu sa môžu zapojiť iba registrovaní užívatelia. Prihlásiť saProsím.

Bolo to ťažké?

• Ako dva prsty som okamžite rozhodol všetko správne.

• Nie naozaj, v pár otázkach som sa mýlil.

• Polovicu som vyriešil správne.

• Odpoveď som hádal dvakrát!

• Napíšem do komentárov

Hlasovalo 14 užívateľov. 10 užívateľov sa zdržalo hlasovania.

Zdroj: hab.com