🥇A Highload++ Siberia 2019 nyomán — 8 feladat az Oracle-hoz

Helló!

2019. június 24-25-én Novoszibirszkben megrendezésre került a Highload++ Siberia konferencia. A mi csapatunk is ott volt. előadás A "Oracle konténeradatbázisok (CDB/PDB) és ezek gyakorlati alkalmazása a szoftverfejlesztésben" című előadást később közzétesszük szöveges verzióban. Nagyszerű volt, köszönjük! olegbunin a szervezésért, és természetesen mindenkinek, aki eljött.

Ebben a posztban szeretnénk megosztani veletek a falunknál található feladatokat, hogy ellenőrizhessétek tudásotokat Oracle-ból. A továbbiakban – 8 feladat, válaszlehetőségekkel és magyarázattal.

Mi a maximális érték, amit a következő szkript végrehajtása után látni fogunk?

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
Semmilyen, hiba lesz.

VálaszAz Oracle dokumentációja szerint (idézve a 8.1.6-ból):
Egyetlen SQL utasításon belül az Oracle csak egyszer növeli meg a sorozatszámot soronként. Ha egy utasítás több hivatkozást tartalmaz a NEXTVAL-ra, az Oracle egyszer emeli a sorozatszámot, és ugyanazt az értéket adja vissza minden NEXTVAL előfordulásnak. Ha egy utasítás mind CURRVAL, mind NEXTVAL hivatkozásokat tartalmaz, az Oracle növeli a sorozatszámot, és ugyanazt az értéket adja vissza mind CURRVAL, mind NEXTVAL számára, függetlenül azok sorrendjétől az utasításon belül.

Így a maximális érték a sorok számának megfelelően 5 lesz..

Hány sor lesz a táblában a következő szkript végrehajtása után?

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

VálaszAz Oracle dokumentációja alapján (idézve a 11.2-ból):

Mielőtt bármilyen SQL utasítást végrehajtanának, az Oracle implicit mentési pontot jelöl ki (ez nem elérhető számotokra). Ezután, ha az utasítás nem sikerül, az Oracle automatikusan visszaállítja és visszaadja a releváns hiba kódot az SQLCODE-nak az SQLCA-n belül. Például, ha egy INSERT utasítás hibát okoz, mert megpróbál egy duplikált értéket beilleszteni egy egyedi indexbe, az utasítást visszaállítja.

A kliensből történő eljárás hívása is egyes nyilatkozatként van kezelve és feldolgozva. Így az első eljárás hívás sikeresen befejeződik, három rekordot beszúrva; a második eljárás hívás hibával végződik, visszaállítva a negyedik rekordot, amelyet már sikerült beszúrni; a harmadik hívás hibával végződik, és a táblában három rekord marad..

Hány sor lesz a táblában a következő szkript végrehajtása után?

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('Hoppá!');
end;
/

VálaszAz Oracle dokumentációja alapján (idézve a 11.2-ból):

Ellenőrzési feltétel megadása lehetővé teszi, hogy megadja a táblának azt a feltételét, amelynek minden sornak meg kell felelnie. Az ellenőrzés teljesítéséhez a táblában minden sor esetében a feltételt igaznak vagy ismeretlennek (null miatt) kell lennie. Amikor az Oracle egy adott sorra egy ellenőrzési feltételt értékel, akkor a feltételben szereplő oszlopnevek az adott sor oszlopaiban lévő értékekre utalnak.

Így a null érték átmegy a vizsgálaton, és az anonim blokk sikeresen végrehajtható, egészen addig a pillanatig, amíg meg nem próbálják beszúrni a 3-as értéket. Ekkor a hiba kezelő blokk leállítja a kivételt, és nem történik visszagörgetés, a táblában négy sor marad. az értékek 1, null, 2 és újra null.

Milyen értékpárok foglalnak el azonos mennyiségű helyet a blokkban?

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', 'Vaszja', 10, 10, 'D', 'Vaszja');

A és X
B és Y
C és K
C és Z
K és Z
I és J
J és X
Minden felsorolt

VálaszHozzunk példákat a dokumentációból (12.1.0.2) a különböző adattípusok tárolásáról az Oracle-ban.

CHAR adattípus
A CHAR adattípus rögzített hosszúságú karakterláncot specifikál az adatbázis karakterkészletében. Az adatbázis karakterkészletének megadásakor határozza meg azt. Az Oracle biztosítja, hogy minden CHAR oszlopban tárolt érték megfeleljen a kiválasztott hosszúsági szemantikában megadott méretnek. Ha rövidebb értéket ad meg, mint az oszlophossz, akkor az Oracle a kiválasztott hosszúságra kiegészíti az értéket üres karakterekkel.

VARCHAR2 adattípus
A VARCHAR2 adattípus változó hosszúságú karakterláncot specifikál az adatbázis karakterkészletében. Az adatbázis karakterkészletének megadásakor határozza meg azt. Az Oracle pontosan úgy tárol egy karakter értéket a VARCHAR2 oszlopban, ahogy megadta, kipárnázás nélkül, feltéve, hogy az érték nem haladja meg az oszlop hosszát.

NUMBER adattípus
A NUMBER adattípus nullát, valamint pozitív és negatív rögzített számokat tárol, abszolút értékek 1.0 x 10-130 és 1.0 x 10126 közötti értékek között, kivéve az utóbbit. Ha olyan aritmetikai kifejezést ad meg, amelynek értéke nagysága elérheti vagy meghaladhatja az 1.0 x 10126-ot, akkor az Oracle hibát ad vissza. Minden NUMBER érték 1 és 22 byte között igényel. Figyelembe véve ezt, a NUMBER(p) típusú numerikus adattípusra vonatkozó oszlopszélességet az alábbi képlet alapján lehet kiszámítani: ROUND((length(p)+s)/2))+1 ahol s nulla, ha a szám pozitív, és s egy, ha a szám negatív.

Ezen kívül idézzünk egy szakaszt a dokumentációból a null értékek tárolásáról.

A null egy érték hiánya egy oszlopban. A nullok hiányzó, ismeretlen vagy nem alkalmazható adatokat jeleznek. A nullok tárolódnak az adatbázisban, ha adatoszloppal határosak. Ezekben az esetekben 1 byte-ot igényelnek az oszlop hosszának tárolásához (nulla). A sor végén található nullok esetében nincs tárolás szükséges, mivel egy új sorfej jelzi, hogy az előző sor fennmaradó oszlopai nullák. Például, ha a tábla utolsó három oszlopa null, akkor ezekhez az oszlopokhoz nincs tárolva adat.

Ezekből az adatokból következtetéseket vonunk le. Tekintettel arra, hogy az adatbázis AL32UTF8 kódolást használ, ebben a kódolásban az orosz betűk 2 byte-ot fognak foglalni.

1) A és X, az a mező 'Y' értéke 1 byte-ot, az x mező 'D' értéke 2 byte-ot foglal.
2) B és Y, a 'Vasya' b értéke 10 karakterre kiegészül szóközökkel, és 14 bájtot foglal el, a 'Vasya' d – 8 bájtot foglal el.
3) C és K. Mindkét mező NULL értéket kapott, utánuk értékes mezők jönnek, ezért 1 bájtot foglalnak el.
4) C és Z. Mindkét mező NULL értéket kapott, de a Z mező az utolsó a táblázatban, ezért nem foglal helyet (0 bájt). A C mező 1 bájtot foglal el.
5) K és Z. Hasonló az előző esetre. A K mező értéke 1 bájtot foglal el, a Z – 0-t.
6) I és J. A dokumentáció szerint mindkét érték 2 bájtot fog foglalni. A hosszúságot a dokumentációból vett képlet szerint számítjuk: round((1 + 0) / 2) + 1 = 1 + 1 = 2.
7) J és X. A J mező értéke 2 bájtot foglal el, az X mező értéke 2 bájtot foglal el.

Összességében a helyes változatok: C és K, I és J, J és X.

Mekkora lesz körülbelül a clustering factor a T_I indexnél?

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

Tízestermékek
Körülbelül százak
Körülbelül ezrek
Körülbelül tízezer

VálaszAz Oracle dokumentációja szerint (12.1-ből idézve):

A B-fa index clustering factor méri a sorok fizikális csoportosítását egy index értékkel kapcsolatban.

A clustering factor segít az optimalizálónak eldönteni, hogy egy index vizsgálat vagy teljes táblázat vizsgálat hatékonyabb bizonyos lekérdezésekhez. Alacsony clustering factor hatékony index vizsgálatot jelez.

A clustering factor, amely közel van a táblázat blokkjainak számához, azt jelenti, hogy a sorok fizikailag rendezettek a táblázat blokkjaiban az index kulcs szerint. Ha az adatbázis teljes táblázat vizsgálatot végez, akkor tendál a sorok visszakeresésére, ahogyan a lemezen tárolva vannak, rendezve az index kulcs szerint. A clustering factor, amely közel áll a sorok számához, azt jelenti, hogy a sorok véletlenszerűen szórva vannak az adatbázis blokkjain az index kulcs szerint. Ha az adatbázis teljes táblázat vizsgálatot végez, akkor a sorokat nem fogja rendezett sorrendben visszakeresni ezzel az index kulccsal.

Ebben az esetben az adatok tökéletesen rendezettek, így a clustering factor egyenlő vagy közel lesz a táblázatban foglalt blokkok számához. A 8 kilobájtos alap blokk méretével számolva várható, hogy egy blokkba körülbelül ezer szűk number érték fér, így a blokkok száma, és ennek következtében a clustering factor körülbelül tízestermékek.

Milyen N értékek esetén fog a következő szkript sikeresen végrehajtódni egy normál adatbázisban a standard beállításokkal?

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

VálaszAz Oracle dokumentációja alapján (idézve a 11.2-ból):

Logikai adatbázis határok

Elem
Határ típusa
Határérték

Indexek
Az indexelt oszlopok teljes mérete
A táblázat blokk méretének 75%-a mínusz némi túlterhelés

Így tehát az indexelt oszlopok összesített méretének nem szabad meghaladnia a 6KB-ot. A továbbiak az adott adatbázis kódolásától függnek. Az AL32UTF8 kódolás esetén egy karakter legfeljebb 4 bájt helyet foglalhat el, így 6 kilobájt esetén a legrosszabb forgatókönyvben körülbelül 1500 karakter fér el. Ezért az Oracle meg fogja tiltani egy index létrehozását, ha N = 400 (amikor a kulcs hossza a legrosszabb esetben 1600 karakter * 4 bájt + a rowid hossza) annak ellenére, hogy ha N = 200 (és kevesebb) az index létrehozása problémák nélkül végrehajtható.

Az INSERT operátor, APPEND hinttel a közvetlen módban történő adatok betöltésére szolgál. Mi történik, ha egy olyan táblához alkalmazzák, amelyhez trigger tartozik?

Az adatok közvetlen módban lesznek betöltve, a trigger pedig úgy fog működni, ahogy kell.
Az adatok közvetlen módban lesznek betöltve, de a trigger nem fog végrehajtódni.
Az adatok hagyományos módban lesznek betöltve, a trigger pedig úgy fog működni, ahogy kell.
Az adatok hagyományos módban lesznek betöltve, de a trigger nem fog végrehajtódni.
Az adatok nem lesznek betöltve, és hibaüzenet fog jelentkezni.

VálaszAlapvetően ez a kérdés inkább logikai. A helyes válasz megtalálása érdekében a következő érvelési modellt ajánlanám:

A közvetlen módú beszúrás közvetlen adatblokk képzésével történik, a SQL-motoron kívül, ami biztosítja a magas sebességet. Így nagyon nehéz biztosítani a trigger végrehajtását, ha egyáltalán lehetséges, és ennek nincs értelme, mivel ez így is jelentősen lelassítja a beszúrást.
A trigger végrehajtásának elmulasztása azt fogja eredményezni, hogy az azonos adatokat tartalmazó táblák esetén az adatbázis állapota (más táblák esetén) attól fog függeni, hogy ezek az adatok milyen módban lettek beszúrva. Ez nyilvánvalóan rombolni fogja az adatok integritását, és nem alkalmazható megoldásként a production környezetben.
A kért művelet végrehajtásának lehetetlensége általában hibaüzenetként értelmezhető. De itt érdemes megemlíteni, hogy az APPEND hint, és a hint logikája alapvetően az, hogy figyelembe veszik, ha lehetséges, ha nem, a parancs a hint figyelembevétele nélkül hajtódik végre.

Így a várt válasz az, hogy az adatok hagyományos (SQL) módban fognak betöltődni, a trigger pedig működni fog.

Az Oracle dokumentációja szerint (8.04-ből idézve):

A korlátozások megsértése a hagyományos beszúrási útvonal használatával, soros végrehajtásra okozza az utasítás végrehajtását, figyelmeztetések vagy hibaüzenetek nélkül. A kivétel a tranzakción belül többször ugyanazon táblát érintő utasításokra vonatkozó korlátozás, ami hibaüzeneteket okozhat.
Például, ha a táblán trigger vagy referenciális integritás van, akkor az APPEND tipp figyelmen kívül lesz hagyva, amikor közvetlen betöltésű INSERT-et (soros vagy párhuzamos) próbálsz használni, valamint a PARALLEL tipp vagy záradék, ha van ilyen.

Mi fog történni a következő szkript végrehajtásakor?

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

Sikeres végrehajtás
Hiba a szintaktikai hiba miatt
Hiba, amely az autonóm tranzakció érvénytelenségével kapcsolatos
Hiba, amely a hívások maximális mélységének túllépésével kapcsolatos
Hiba, amely a külső kulcs megsértésével kapcsolatos
Hiba, amely a zárolásokkal kapcsolatos

VálaszA tábla és a trigger teljesen helyesen van létrehozva, és ez a művelet nem kellene problémát okozzon. Az autonóm tranzakciók a triggerben is engedélyezettek, különben nem lenne lehetséges például a naplózás.

Az első sor beszúrása után a trigger sikeres aktiválása a második sor beszúrását eredményezné, ami újra aktiválja a triggert, ami harmadik sort beszúr, és így tovább, amíg a statement össze nem omlik a hívások maximális mélységének túllépése miatt. Azonban van egy másik finom részlet. A trigger végrehajtásának pillanatában az első beszúrt rekord még nem kommitálva. Ezért a trigger, amely autonóm tranzakcióban működik, megpróbál beszúrni a táblába egy olyan rekordot, amely külső kulcs révén egy még nem kommitált rekordra hivatkozik. Ez várakozást eredményez (az autonóm tranzakció várja a fő tranzakció kommitálását, hogy tudja, beszúrhat-e adatokat), és egyidejűleg a fő tranzakció várja az autonóm kommitálását, hogy folytathassa a munkát a trigger után. Deadlock lép fel, és ennek következtében az autonóm tranzakciót leölés miatt megszüntetik, amely a zárolásokkal kapcsolatos.

Csak a regisztrált felhasználók vehetnek részt a szavazáson. Kérem, jelentkezzen be., kérem.