Highload++ Siberia 2019 jälgedes – 8 ülesannet Oracle'is

Tere!

24.-25. juunil toimus Novosibirskis Highload++ Siberia 2019. Ka meie poisid olid kohal. aruanne “Oracle konteinerite andmebaasid (CDB/PDB) ja nende praktiline kasutamine tarkvaraarenduses”, avaldame tekstiversiooni veidi hiljem. Lahe oli, aitäh olegbunin organisatsioonile, samuti kõigile, kes kohale tulid.

Selles postituses soovime teiega jagada probleeme, mis meie boksis esinesid, et saaksite oma Oracle'i teadmisi proovile panna. Lõike all on 8 ülesannet, vastusevariandid ja selgitus.

Mis on maksimaalne jada väärtus, mida näeme järgmise skripti täitmise tulemusel?

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
• Ei, tuleb viga

VastusVastavalt Oracle'i dokumentatsioonile (tsiteeritud 8.1.6):
Ühes SQL-lauses suurendab Oracle jada ainult üks kord rea kohta. Kui avaldus sisaldab järjestuse jaoks rohkem kui ühte viidet NEXTVAL-ile, suurendab Oracle jada üks kord ja tagastab sama väärtuse kõigi NEXTVAL-i esinemiste jaoks. Kui lause sisaldab viiteid nii CURRVAL kui ka NEXTVAL, suurendab Oracle jada ja tagastab sama väärtuse nii CURRVAL kui ka NEXTVAL jaoks, olenemata nende järjestusest lauses.

Seega maksimaalne väärtus vastab ridade arvule, see on 5.

Mitu rida on tabelis järgmise skripti käivitamise tulemusena?

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

VastusVastavalt Oracle'i dokumentatsioonile (tsiteeritud 11.2):

Enne mis tahes SQL-lause täitmist märgib Oracle kaudse salvestuspunkti (pole teile saadaval). Seejärel, kui avaldus ebaõnnestub, veeretab Oracle selle automaatselt tagasi ja tagastab SQLCA-s SQLCODE-ile kehtiva veakoodi. Näiteks kui INSERT-lause põhjustab tõrke, üritades sisestada kordumatusse indeksisse duplikaatväärtust, siis avaldis keritakse tagasi.

Ka kliendilt HP-le helistamist käsitletakse ja töödeldakse ühe väljavõttena. Seega lõppeb esimene HP kõne edukalt pärast kolme kirje sisestamist; teine ​​HP kõne lõpeb veaga ja keerab tagasi neljanda kirje, mis tal õnnestus sisestada; kolmas kõne ebaõnnestub, ja tabelis on kolm rekordit.

Mitu rida on tabelis järgmise skripti käivitamise tulemusena?

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

VastusVastavalt Oracle'i dokumentatsioonile (tsiteeritud 11.2):

Kontrollpiirang võimaldab määrata tingimuse, millele tabeli iga rida peab vastama. Piirangu täitmiseks peab iga tabeli rida muutma tingimuse kas TRUE või tundmatu (nulli tõttu). Kui Oracle hindab konkreetse rea kontrollipiirangu tingimust, viitavad tingimuse kõik veerunimed selle rea veeru väärtustele.

Seega läbib väärtus null kontrolli ja anonüümset plokki täidetakse edukalt kuni väärtuse 3 sisestamise katseni. Pärast seda kustutab veatöötlusplokk erandi, tagasipööramist ei toimu ja tabelisse jääb neli rida väärtustega 1, null, 2 ja uuesti null.

Millised väärtuste paarid võtavad plokis sama palju ruumi?

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 ja X
• B ja Y
• C ja K
• C ja Z
• K ja Z
• Mina ja J
• J ja X
• Kõik loetletud

VastusSiin on väljavõtted dokumentatsioonist (12.1.0.2) erinevat tüüpi andmete salvestamise kohta Oracle'is.

CHAR-i andmetüüp
Andmetüüp CHAR määrab andmebaasi märgistiku fikseeritud pikkusega märgistringi. Andmebaasi märgistiku määrate andmebaasi loomisel. Oracle tagab, et kõik CHAR-i veerus salvestatud väärtused on valitud pikkuse semantikas suuruse järgi määratud pikkusega. Kui sisestate veeru pikkusest lühema väärtuse, muudab Oracle väärtuse veeru pikkuseks.

VARCHAR2 andmetüüp
Andmetüüp VARCHAR2 määrab andmebaasi märgistiku muutuva pikkusega märgistringi. Andmebaasi märgistiku määrate andmebaasi loomisel. Oracle salvestab märgi väärtuse veergu VARCHAR2 täpselt nii, nagu te selle määrate, ilma tühja täidiseta, eeldusel, et väärtus ei ületa veeru pikkust.

NUMBER andmetüüp
Andmetüüp NUMBER salvestab nii nulli kui ka positiivseid ja negatiivseid fikseeritud numbreid absoluutväärtustega 1.0 x 10-130 kuni 1.0 x 10126, kuid mitte. Kui määrate aritmeetilise avaldise, mille absoluutväärtus on suurem või võrdne 1.0 x 10126, tagastab Oracle veateate. Iga NUMBER väärtus nõuab 1 kuni 22 baiti. Seda arvesse võttes saab konkreetse arvandmete väärtuse NUMBER(p) veeru suuruse baitides, kus p on antud väärtuse täpsus, arvutada järgmise valemi abil: RING((pikkus(p)+s)/2))+1 kus s võrdub nulliga, kui arv on positiivne, ja s võrdub 1-ga, kui arv on negatiivne.

Lisaks võtame väljavõtte nullväärtuste salvestamise dokumentatsioonist.

Null on väärtuse puudumine veerus. Nullid näitavad puuduvaid, tundmatuid või mitterakendatavaid andmeid. Nullid salvestatakse andmebaasi, kui need jäävad andmeväärtustega veergude vahele. Nendel juhtudel vajavad nad veeru pikkuse (null) salvestamiseks 1 baiti. Rea lõpu nullid ei vaja salvestusruumi, kuna uus rea päis annab märku, et eelmise rea ülejäänud veerud on nullid. Näiteks kui tabeli kolm viimast veergu on tühjad, siis nende veergude kohta andmeid ei salvestata.

Nende andmete põhjal koostame arutluskäigu. Eeldame, et andmebaas kasutab AL32UTF8 kodeeringut. Selles kodeeringus võtavad vene tähed 2 baiti.

1) A ja X, välja a "Y" väärtus võtab 1 baiti, välja x väärtus "D" võtab 2 baiti
2) B ja Y, 'Vasya' in b väärtus on polsterdatud tühikutega kuni 10 tähemärki ja see võtab 14 baiti, 'Vasya' in d võtab 8 baiti.
3) C ja K. Mõlema välja väärtus on NULL, nende järel on olulised väljad, seega võtavad nad enda alla 1 baidi.
4) C ja Z. Mõlema välja väärtus on NULL, kuid väli Z on tabelis viimane, seega ei võta see ruumi (0 baiti). Väljal C on 1 bait.
5) K ja Z. Sarnaselt eelmisele juhtumile. Väärtus väljal K võtab enda alla 1 baidi, väljal Z – 0.
6) I ja J. Dokumentatsiooni kohaselt võtavad mõlemad väärtused 2 baiti. Pikkuse arvutame dokumentatsioonist võetud valemiga: ümmargune( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J ja X. Väärtus väljal J võtab 2 baiti, väärtus väljal X võtab 2 baiti.

Kokku on õiged valikud: C ja K, I ja J, J ja X.

Kui suur on ligikaudu T_I indeksi klastritegur?

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

• Umbes kümneid
• Umbes sadu
• Umbes tuhandeid
• Umbes kümneid tuhandeid

VastusVastavalt Oracle'i dokumentatsioonile (tsiteeritud 12.1):

B-puu indeksi puhul mõõdab indeksi klastritegur ridade füüsilist rühmitamist indeksi väärtuse suhtes.

Indeksite klastritegur aitab optimeerijal otsustada, kas indeksi skannimine või täielik tabelikontroll on teatud päringute jaoks tõhusam). Madal klastritegur näitab tõhusat indeksi skannimist.

Klastritegur, mis on lähedane tabeli plokkide arvule, näitab, et read on tabeliplokkides füüsiliselt järjestatud indeksivõtmega. Kui andmebaas teostab täieliku tabelikontrolli, kipub andmebaas tooma ridu nii, nagu need on kettale salvestatud ja sorteeritud indeksivõtme järgi. Ridade arvule lähedane klastritegur näitab, et read on indeksivõtme suhtes andmebaasiplokkides juhuslikult hajutatud. Kui andmebaas teostab täieliku tabelikontrolli, siis andmebaas ei too selle indeksivõtme alusel ühtegi sorteeritud järjestust ridu.

Sel juhul on andmed ideaaljuhul sorteeritud, nii et klastritegur on võrdne tabelis hõivatud plokkide arvuga või sellele lähedane. Tavalise ploki suuruse 8 kilobaidi korral võib eeldada, et ühte plokki mahub umbes tuhat kitsast arvu väärtust, seega on plokkide arv ja selle tulemusena klastritegur umbes kümneid.

Milliste N väärtuste korral käivitatakse järgmine skript standardsete sätetega tavalises andmebaasis edukalt?

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

VastusVastavalt Oracle'i dokumentatsioonile (tsiteeritud 11.2):

Loogilise andmebaasi piirangud

Kirje
Limiidi tüüp
Piirväärtus

Indexes
Indekseeritud veeru kogumaht
75% andmebaasiploki suurusest miinus mõned üldkulud

Seega ei tohiks indekseeritud veergude kogumaht ületada 6 Kb. Edasine oleneb valitud baaskodeeringust. AL32UTF8 kodeeringu puhul võib üks märk hõivata maksimaalselt 4 baiti, nii et halvimal juhul mahub 6 kilobaiti umbes 1500 tähemärki. Seetõttu keelab Oracle indeksi loomise N = 400 juures (kui halvimal juhul on võtme pikkus 1600 tähemärki * 4 baiti + rea pikkus), samas kui N = 200 (või vähem) indeksi loomine toimib probleemideta.

Operaator INSERT koos vihjega APPEND on mõeldud andmete laadimiseks otserežiimis. Mis juhtub, kui seda rakendatakse lauale, millel päästik ripub?

• Andmed laaditakse otserežiimis, päästik töötab ootuspäraselt
• Andmed laaditakse otserežiimis, kuid päästikut ei käivitata
• Andmed laaditakse tavapärases režiimis, päästik töötab nii nagu peab
• Andmed laaditakse tavapärases režiimis, kuid päästikut ei käivitata
• Andmeid ei laeta, salvestatakse viga

VastusPõhimõtteliselt on see rohkem loogika küsimus. Õige vastuse leidmiseks pakun välja järgmise arutlusmudeli:

1. Otseses režiimis sisestamine toimub andmeploki otsese moodustamisega, SQL-mootorist mööda minnes, mis tagab suure kiiruse. Seega on päästiku täitmise tagamine väga keeruline, kui mitte võimatu, ja sellel pole mõtet, kuna see aeglustab sisestamist ikkagi radikaalselt.
2. Päästiku käivitamata jätmine toob kaasa asjaolu, et kui tabelis olevad andmed on samad, sõltub andmebaasi kui terviku (teiste tabelite) olek sellest, millises režiimis need andmed sisestati. Ilmselgelt hävitab see andmete terviklikkuse ja seda ei saa tootmises lahendusena kasutada.
3. Suutmatust nõutud toimingut sooritada käsitletakse üldjuhul veana. Siinkohal tuleks aga meeles pidada, et APPEND on vihje ja vihjete üldine loogika on see, et võimalusel võetakse neid arvesse, aga kui mitte, siis täidetakse operaator vihjet arvestamata.

Nii et oodatud vastus on andmed laaditakse tavarežiimis (SQL) ja päästik käivitub.

Vastavalt Oracle'i dokumentatsioonile (tsiteeritud 8.04):

Piirangute rikkumise korral käivitatakse lause seeriaviisiliselt, kasutades tavalist sisestusteed, ilma hoiatuste või veateadeteta. Erandiks on piirang väljavõtetele juurdepääsule samale tabelile tehingu jooksul rohkem kui üks kord, mis võib põhjustada veateateid.
Näiteks kui tabelis on trigerid või viiteterviklikkus, ignoreeritakse vihjet APPEND, kui proovite kasutada otselaadimise INSERT-i (jada- või paralleelset), samuti PARALLEELI vihjet või klauslit, kui see on olemas.

Mis juhtub järgmise skripti käivitamisel?

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

• Edukas lõpetamine
• Tõrge süntaksivea tõttu
• Viga: autonoomne tehing ei kehti
• Viga, mis on seotud kõnede pesastamise maksimummäära ületamisega
• Võõrvõtme rikkumise viga
• Lukkudega seotud viga

VastusTabel ja päästik on loodud üsna õigesti ja see toiming ei tohiks probleeme tekitada. Lubatud on ka autonoomsed tehingud trigeris, muidu poleks näiteks logimine võimalik.

Pärast esimese rea sisestamist põhjustaks edukas päästiku käivitamine teise rea sisestamise, põhjustades päästiku uuesti käivitumise, kolmanda rea ​​sisestamise ja nii edasi, kuni avaldus ebaõnnestus, kuna kõnede maksimaalne pesastus on ületatud. Siiski tuleb mängu veel üks peen nüanss. Päästiku käivitamise ajal ei ole esimese sisestatud kirje kinnitamine veel lõppenud. Seetõttu proovib autonoomses tehingus töötav päästik sisestada tabelisse rea, mis viitab välisvõtmele kirjele, mida pole veel kinnitatud. Selle tulemuseks on ootamine (autonoomne tehing ootab põhitehingu sidumist, et näha, kas see suudab andmeid sisestada) ja samal ajal ootab põhitehing autonoomse tehingu jätkamist pärast päästikut. Tekib ummikseisu ja selle tulemusena autonoomne tehing tühistatakse lukkudega seotud põhjustel.

Küsitluses saavad osaleda ainult registreerunud kasutajad. Logi sissepalun.

Kas oli raske?

• Nagu kaks sõrme, otsustasin kohe kõik õigesti.

• Tegelikult mitte, ma eksisin paaris küsimuses.

• Poole lahendasin õigesti.

• Ma arvasin vastust kaks korda!

• Kirjutan kommentaaridesse

14 kasutajat hääletas. 10 kasutajat jäi erapooletuks.

Allikas: www.habr.com