Highload++ Siberia 2019-ren urratsei jarraituz - 8 zeregin Oracle-n

Hi!

Ekainaren 24tik 25ean, Highload++ Siberia 2019 konferentzia ospatu zen Novosibirsken. Gure mutilak ere han izan ziren. txostena “Oracle edukiontzien datu-baseak (CDB/PDB) eta haien erabilera praktikoa softwarea garatzeko”, testu bertsio bat argitaratuko dugu pixka bat geroago. Polita izan zen, eskerrik asko olegbunin antolakuntzarentzat, baita etorri ziren guztientzat ere.

Post honetan, zurekin partekatu nahi dugu gure standean izan ditugun arazoak, zure Oracle ezagutza probatu dezazun. Ebakiaren azpian 8 arazo, erantzun aukerak eta azalpena daude.

Zein da hurrengo script-a exekutatzearen ondorioz ikusiko dugun sekuentzia-balio maximoa?

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
• Ez, errore bat egongo da

ErantzunOracle-ren dokumentazioaren arabera (8.1.6tik aipatua):
SQL adierazpen bakar baten barruan, Oracle-k sekuentzia behin bakarrik handituko du errenkada bakoitzeko. Adierazpen batek NEXTVAL-i erreferentzia bat baino gehiago badu sekuentzia baterako, Oracle-k sekuentzia behin handitzen du eta balio bera itzultzen du NEXTVAL-en agerraldi guztietarako. Adierazpen batek CURRVAL eta NEXTVAL erreferentziak baditu, Oracle-k sekuentzia handitzen du eta balio bera itzultzen du CURRVAL eta NEXTVAL-en adierazpenaren barruan duten ordena edozein dela ere.

Horrela, bada, gehienezko balioa lerro kopuruari dagokiona izango da, hau da, 5.

Zenbat errenkada egongo dira taulan honako script hau exekutatzeko?

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

ErantzunOracle-ren dokumentazioaren arabera (11.2tik aipatua):

Edozein SQL instrukzioa exekutatu aurretik, Oracle-k gordetze-puntu inplizitu bat markatzen du (ez dago eskuragarri). Ondoren, adierazpenak huts egiten badu, Oracle-k automatikoki itzuliko du eta dagokion errore-kodea itzultzen du SQLCODE-ra SQLCA-n. Adibidez, INSERT instrukzio batek errore bat eragiten badu indize esklusibo batean balio bikoiztua txertatzen saiatzean, adierazpena atzera egiten da.

HP bezeroari deitzea ere adierazpen bakar gisa hartzen eta prozesatzen da. Horrela, HP-ren lehen deia arrakastaz amaitzen da, hiru erregistro sartuta; bigarren HP deia errore batekin amaitzen da eta txertatzea lortu duen laugarren erregistroa atzera egiten du; hirugarren deialdiak huts egiten du, eta hiru erregistro daude taulan.

Zenbat errenkada egongo dira taulan honako script hau exekutatzeko?

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

ErantzunOracle-ren dokumentazioaren arabera (11.2tik aipatua):

Egiaztapen-murriztapen batek taulako errenkada bakoitzak bete behar duen baldintza bat zehazten uzten dizu. Murriztapena betetzeko, taulako errenkada bakoitzak baldintza EGIA edo ezezaguna egin behar du (nulu baten ondorioz). Oracle-k errenkada jakin baterako egiaztapen-murriztapen-baldintza bat ebaluatzen duenean, baldintzako edozein zutabe-izenek errenkada horretako zutabe-balioak aipatzen dituzte.

Horrela, null balioak egiaztapena gaindituko du, eta bloke anonimoa arrakastaz exekutatuko da 3 balioa txertatzen saiatu arte. Horren ondoren, erroreak kudeatzeko blokeak salbuespena garbituko du, ez da atzera bueltarik gertatuko eta lau errenkada geratuko dira taulan 1, null, 2 eta null balioekin berriro.

Zein balio bikotek hartuko dute espazio berdina blokean?

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 eta X
• B eta Y
• C eta K
• C eta Z
• K eta Z
• I eta J
• J eta X
• Zerrendatutako guztiak

ErantzunHona hemen Oracle-n hainbat datu-mota gordetzeari buruzko dokumentazioaren zatiak (12.1.0.2).

CHAR datu mota
CHAR datu-motak luzera finkoko karaktere-kate bat zehazten du datu-baseko karaktere multzoan. Datu-basearen karaktere multzoa zehazten duzu datu-basea sortzen duzunean. Oracle-k bermatzen du CHAR zutabean gordetako balio guztiek hautatutako luzeraren semantikan tamainaren arabera zehaztutako luzera dutela. Zutabearen luzera baino laburragoa den balio bat txertatzen baduzu, orduan Oracle-k hutsik jartzen du balioa zutabearen luzera.

VARCHAR2 Datu mota
VARCHAR2 datu-motak luzera aldakorreko karaktere-kate bat zehazten du datu-baseko karaktere multzoan. Datu-basearen karaktere multzoa zehazten duzu datu-basea sortzen duzunean. Oracle-k karaktere-balio bat VARCHAR2 zutabe batean gordetzen du zuk zehazten duzun bezala, hutsik bete gabe, baldin eta balioak zutabearen luzera gainditzen ez badu.

NUMBER Datu mota
ZENBAKIA datu-motak zero eta zenbaki finko positiboak eta negatiboak gordetzen ditu 1.0 x 10-130-tik 1.0 x 10126ra bitarteko balio absolutuak dituzten baina barne 1.0 x 10126. Adierazpen aritmetiko bat zehazten baduzu, zeinaren balioak balio absolutua handiagoa edo berdina duen. 1 x 22, orduan Oracle-k errore bat itzultzen du. ZENBAKIA balio bakoitzak XNUMX eta XNUMX byte behar ditu. Hori kontuan hartuta, ZENBAKIA(p) datu-balio jakin baterako bytetan dagoen zutabe-tamaina, non p balio jakin baten doitasuna den, honako formula hau erabiliz kalkula daiteke: BORROBILA((luzera (p)+s)/2))+1 non s zero berdina den zenbakia positiboa bada, eta s berdina 1 den zenbakia negatiboa bada.

Horrez gain, har dezagun Null balioak gordetzeari buruzko dokumentazioko pasarte bat.

Nulua zutabe batean baliorik ez egotea da. Nuluek datuak falta, ezezagunak edo aplikaezinak adierazten dituzte. Nuluak datu-basean gordetzen dira datu-balioak dituzten zutabeen artean sartzen badira. Kasu hauetan, byte 1 behar dute zutabearen luzera (zero) gordetzeko. Errenkadako nuluek ez dute biltegiratzerik behar errenkadako goiburu berri batek aurreko errenkadako gainerako zutabeak nuluak direla adierazten duelako. Adibidez, taula baten azken hiru zutabeak nuluak badira, ez da zutabe hauentzako daturik gordetzen.

Datu horietatik abiatuta, arrazoibidea eraikitzen dugu. Datu-baseak AL32UTF8 kodeketa erabiltzen duela suposatzen dugu. Kodetze honetan, errusiar letrak 2 byte hartuko ditu.

1) A eta X, a 'Y' eremuaren balioak byte 1 hartzen du, x 'D' eremuaren balioak 2 byte hartzen ditu
2) B eta Y, 'Vasya' b-ko balioa 10 karaktere arteko zuriunez beteko da eta 14 byte hartuko ditu, 'Vasya'-k d-n 8 byte.
3) C eta K. Bi eremuek NULL balioa dute, horien ondoren eremu esanguratsuak daude, beraz, byte 1 hartzen dute.
4) C eta Z. Bi eremuek NULL balioa dute, baina Z eremua taulako azkena da, beraz, ez du lekurik hartzen (0 byte). C eremuak byte 1 hartzen du.
5) K eta Z. Aurreko kasuaren antzera. K eremuko balioak byte 1 hartzen du, Z – 0-n.
6) I eta J. Dokumentazioaren arabera, bi balioek 2 byte hartuko dituzte. Luzera dokumentaziotik ateratako formula erabiliz kalkulatuko dugu: biribila( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J eta X. J eremuko balioak 2 byte hartuko ditu, X eremuko balioak 2 byte hartuko ditu.

Guztira, aukera zuzenak hauek dira: C eta K, I eta J, J eta X.

Zein izango da gutxi gorabehera T_I indizearen multzokatze-faktorea?

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

• Hamar inguru
• Ehunka inguru
• Milaka inguru
• Hamarnaka mila inguru

ErantzunOracle-ren dokumentazioaren arabera (12.1tik aipatua):

B-zuhaitz indize baterako, indizeen multzokatze faktoreak errenkaden talde fisikoa neurtzen du indize-balio batekin.

Indizeen multzokatze-faktoreak optimizatzaileari laguntzen dio indize-eskaneatze bat edo taula osoa eskaneatzea eraginkorragoa den kontsulta jakin batzuetarako). Multzo-faktore baxu batek indizearen eskaneatu eraginkorra adierazten du.

Taula bateko bloke-kopurutik hurbil dagoen multzokatze-faktore batek errenkadak indize-gakoaren bidez taula-blokeetan fisikoki ordenatuta daudela adierazten du. Datu-baseak taula osoa eskaneatzen badu, datu-baseak errenkadak berreskuratu ohi ditu indizearen gakoaren arabera ordenatuta dauden diskoan gordetzen diren heinean. Errenkada kopurutik hurbil dagoen multzokatze-faktore batek errenkadak ausaz sakabanatuta daudela adierazten du datu-baseen blokeetan indize-gakoarekin lotuta. Datu-baseak taula osoa eskaneatzen badu, datu-baseak ez ditu errenkadak berreskuratuko indize-gako honen arabera ordenatutako ordenan.

Kasu honetan, datuak ezin hobeto ordenatuta daude, beraz, multzokatze-faktorea taulan okupatutako bloke kopuruaren berdina edo hurbila izango da. 8 kilobyte-ko bloke-tamaina estandar baterako, mila zenbaki-balio estu inguru bloke batean sartuko direla espero dezakezu, beraz, bloke-kopurua eta, ondorioz, multzokatze-faktorea izango da. hamarren inguru.

N-ren zein baliotan exekutatu beharko da ondoko script hau ezarpen estandarrak dituen datu-base arrunt batean?

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

ErantzunOracle-ren dokumentazioaren arabera (11.2tik aipatua):

Datu-base logikoen mugak

Item
Muga mota
Muga Balioa

Indizeak
Indexatutako zutabearen guztizko tamaina
Datu-basearen bloke-tamainaren % 75 kenduta gainkostu batzuk

Beraz, indexatutako zutabeen guztizko tamainak ez du 6Kb-tik gorakoa izan behar. Ondoren gertatzen dena hautatutako oinarrizko kodeketaren araberakoa da. AL32UTF8 kodetzeko, karaktere batek 4 byte okupa ditzake gehienez, beraz, kasurik txarrenean, 6 karaktere inguru sartuko dira 1500 kilobytetan. Hori dela eta, Oracle-k ez du indizea sortzea N = 400-n (kasurik txarrenaren gakoaren luzera 1600 karaktere * 4 byte + rowid luzera denean), berriz. N = 200 (edo gutxiago) indizea sortzea arazorik gabe funtzionatuko du.

APPEND aholkua duen INSERT operadorea datuak modu zuzenean kargatzeko diseinatuta dago. Zer gertatzen da abiarazlea zintzilik dagoen mahaian aplikatzen bada?

• Datuak modu zuzenean kargatuko dira, abiarazleak espero bezala funtzionatuko du
• Datuak modu zuzenean kargatuko dira, baina abiarazlea ez da exekutatuko
• Datuak ohiko moduan kargatuko dira, abiarazleak behar bezala funtzionatuko du
• Datuak ohiko moduan kargatuko dira, baina trigger-a ez da exekutatuko
• Datuak ez dira kargatuko, errore bat erregistratuko da

ErantzunFuntsean, hau logika kontu bat da. Erantzun zuzena aurkitzeko, honako arrazoiketa-eredu hau proposatuko nuke:

1. Zuzeneko moduan txertatzea datu-bloke bat zuzenean eratuz egiten da, SQL motorra saihestuz, abiadura handia bermatzen duena. Horrela, abiarazlearen exekuzioa ziurtatzea oso zaila da, ezinezkoa ez bada, eta horrek ez du ezertarako balio, oraindik ere txertaketa errotik motelduko baitu.
2. Abiarazlea ez exekutatu ezean, taulako datuak berdinak badira, datu-basearen egoera osoa (beste taula batzuk) datu horiek txertatu diren moduaren araberakoa izango da. Horrek datuen osotasuna suntsitu egingo du, eta ezin da produkzioan soluzio gisa aplikatu.
3. Eskatutako eragiketa burutzeko ezintasuna errore gisa hartzen da oro har. Baina hemen gogoratu behar dugu APPEND iradokizun bat dela, eta aholkuen logika orokorra da ahal dela kontuan hartzen direla, baina hala ez bada, operadorea iradokizuna kontuan hartu gabe exekutatzen da.

Beraz, espero den erantzuna da datuak normal (SQL) moduan kargatuko dira, abiarazlea piztuko da.

Oracle-ren dokumentazioaren arabera (8.04tik aipatua):

Murrizketak urratzeak adierazpena seriean exekutatu egingo du, ohiko txertatze-bidea erabiliz, abisurik edo errore-mezurik gabe. Salbuespena da transakzio batean taula berera behin baino gehiagotan sartzeko adierazpenak murriztea, eta horrek errore-mezuak sor ditzake.
Adibidez, abiarazleak edo erreferentziazko osotasuna taulan badaude, APPEND aholkuari ez ikusi egingo zaio karga zuzeneko INSERT (seriala edo paraleloa) erabiltzen saiatzen zarenean, baita PARALLEL iradokizuna edo klausula ere, halakorik balego.

Zer gertatuko da honako script hau exekutatzen denean?

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

• Arrakastaz osatzea
• Hutsegite sintaxi errore baten ondorioz
• Errorea: transakzio autonomoa ez da baliozkoa
• Gehieneko deien habia gainditzearekin erlazionatutako errorea
• Atzerriko gako haustearen errorea
• Sarrailekin erlazionatutako errorea

ErantzunTaula eta trigger nahiko zuzen sortu dira eta eragiketa honek ez luke arazorik ekarri behar. Eragile batean transakzio autonomoak ere onartzen dira, bestela ezin izango litzateke erregistratzea, adibidez.

Lehenengo errenkada txertatu ondoren, abiarazlearen jaurtiketa arrakastatsu batek bigarren errenkada txertatuko luke, abiarazlea berriro piztuko da, hirugarren errenkada bat sartuz, eta abar adierazpenak huts egin duen arte, deien habiaratze maximoa gainditzeagatik. Hala ere, beste puntu sotil bat sartzen da jokoan. Abiarazlea exekutatzen den unean, txertatutako lehen erregistrorako konpromisoa ez da amaitu. Hori dela eta, transakzio autonomo batean exekutatzen den abiarazle batek taulan txertatzen saiatzen da gako arrotz bat oraindik konprometitu ez den erregistro bati erreferentzia egiten dion errenkada bat. Honen ondorioz, itxaronaldia sortzen da (transakzio autonomoak transakzio nagusiak konprometitu arte itxarongo du datuak txertatu ditzakeen ikusteko) eta, aldi berean, transakzio nagusiak abiaraztearen ondoren transakzio autonomoak lanean jarraitzeko itxaroten du. Blokeo bat gertatzen da eta, ondorioz, transakzio autonomoa bertan behera uzten da blokeoekin lotutako arrazoiengatik.

Erregistratutako erabiltzaileek soilik parte hartu dezakete inkestan. Hasi saioa, mesedez.

Zaila izan zen?

• Bi hatz bezala, berehala erabaki nuen dena zuzen.

• Benetan ez, oker nengoen galdera pare batean.

• Erdia ondo konpondu nuen.

• Bi aldiz asmatu dut erantzuna!

• Iruzkinetan idatziko dut

14 erabiltzailek eman dute botoa. 10 erabiltzaile abstenitu ziren.

Iturria: www.habr.com