Ukulandela ezinyathelweni ze-Highload++ Siberia 2019 - 8 imisebenzi ku-Oracle

Sawubona!

NgoJuni 24-25, ingqungquthela ye-Highload++ Siberia 2019 yayibanjelwe eNovosibirsk. Nabafana bethu babelapho. umbiko “Imininingwane yolwazi yeziqukathi ze-Oracle (CDB/PDB) kanye nokusetshenziswa kwayo okungokoqobo ekuthuthukisweni kwesoftware”, sizoshicilela inguqulo yombhalo kamuva nje. Bekupholile, ngiyabonga i-olegbunin okwenhlangano, kanye nabo bonke abeza.

Kulokhu okuthunyelwe, sithanda ukwabelana nawe ngezinkinga ebesinazo endaweni yethu ukuze ukwazi ukuhlola ulwazi lwakho lwe-Oracle. Ngezansi kokusikwa kunezinkinga eziyi-8, izinketho zokuphendula kanye nencazelo.

Iliphi inani eliphakeme lokulandelana esizolibona njengomphumela wokwenza umbhalo olandelayo?

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
• Cha, kuzoba nephutha

ImpenduloNgokuvumelana nemibhalo ye-Oracle (ecashunwe ku-8.1.6):
Ngaphakathi kwesitatimende esisodwa se-SQL, i-Oracle izokwengeza ukulandelana kanye kuphela ngomugqa. Uma isitatimende siqukethe ireferensi engaphezu kweyodwa ku-NEXTVAL yokulandelana, i-Oracle inyusa ukulandelana kanye futhi ibuyisela inani elifanayo kuzo zonke izenzakalo ze-NEXTVAL. Uma isitatimende siqukethe izinkomba zakho kokubili CURRVAL kanye ne-NEXTVAL, i-Oracle inyusa ukulandelana futhi ibuyisela inani elifanayo kukho kokubili i-CURRVAL ne-NEXTVAL kungakhathaliseki ukuthi i-oda lakho linjani ngaphakathi kwesitatimende.

Ngakho, inani eliphezulu lizohambisana nenani lemigqa, okungukuthi 5.

Mingaki imigqa ezoba kuthebula njengomphumela wokusebenzisa iskripthi esilandelayo?

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

ImpenduloNgokuvumelana nemibhalo ye-Oracle (ecashunwe ku-11.2):

Ngaphambi kokwenza noma yisiphi isitatimende se-SQL, i-Oracle imaka indawo yokulondoloza engacacile (ayitholakali kuwe). Bese, uma isitatimende sihluleka, i-Oracle isibuyisela emuva ngokuzenzakalelayo bese ibuyisela ikhodi yephutha esebenzayo ku-SQLCODE ku-SQLCA. Isibonelo, uma isitatimende FAKA sidala iphutha ngokuzama ukufaka inani eliyimpinda kunkomba eyingqayizivele, isitatimende sihlehliswa emuva.

Ukushayela i-HP kuklayenti nakho kuyabhekwa futhi kusetshenzwe njengesitatimende esisodwa. Ngakho, ucingo lokuqala lwe-HP luqeda ngempumelelo, lufake amarekhodi amathathu; ucingo lwesibili lwe-HP luphela ngephutha bese ibuyisela emuva irekhodi lesine ekwazile ukulifaka; ucingo lwesithathu luyehluleka, futhi kukhona amarekhodi amathathu etafuleni.

Mingaki imigqa ezoba kuthebula njengomphumela wokusebenzisa iskripthi esilandelayo?

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

ImpenduloNgokuvumelana nemibhalo ye-Oracle (ecashunwe ku-11.2):

Umkhawulo wokuhlola ukuvumela ukuthi ucacise umbandela okufanele umugqa ngamunye kuthebula usinelise. Ukwanelisa umkhawulo, umugqa ngamunye kuthebula kufanele wenze isimo sibe TRUE noma singaziwa (ngenxa yokungabi nalutho). Lapho i-Oracle ihlola umbandela wokunqanda isheke kumugqa othile, noma imaphi amagama ekholomu esimweni abhekisela kumanani ekholomu kulowo mugqa.

Ngakho, i-value null izodlula isheke, futhi ibhulokhi engaziwa izokwenziwa ngempumelelo kuze kube umzamo wokufaka inani 3. Ngemva kwalokhu, ibhulokhi yokusingatha iphutha izosula okuhlukile, akukho ukuhlehliswa okuzokwenzeka, futhi kuzosala imigqa emine etafuleni ngamanani 1, null, 2 kanye null futhi.

Yimaphi amapheya amanani azothatha inani elifanayo lesikhala kubhulokhi?

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 kanye no-X
• B kanye no-Y
• C kanye no-K
• C kanye no-Z
• K kanye no-Z
• Mina kanye no-J
• J kanye no-X
• Konke okubalwe

ImpenduloNazi izingcaphuno ezivela kumadokhumenti (12.1.0.2) okugcina izinhlobo ezihlukahlukene zedatha ku-Oracle.

Uhlobo lwedatha ye-CHAR
Uhlobo lwedatha ye-CHAR lucacisa iyunithi yezinhlamvu yobude obugxilile kusethi yezinhlamvu zesizindalwazi. Ucacisa uhlamvu lwesizindalwazi esethiwe lapho wakha isizindalwazi sakho. I-Oracle iqinisekisa ukuthi wonke amanani agcinwe kukholomu ye-CHAR anobude obushiwo ngosayizi ku-semantics yobude obukhethiwe. Uma ufaka inani elifushane kunobude bekholomu, khona-ke i-Oracle yenza amaphedi angenalutho inani kubude bekholomu.

VARCHAR2 Uhlobo Lwedatha
Uhlobo lwedatha ye-VARCHAR2 lucacisa iyunithi yezinhlamvu yobude obuguquguqukayo kusethi yezinhlamvu zesizindalwazi. Ucacisa uhlamvu lwesizindalwazi esethiwe lapho wakha isizindalwazi sakho. I-Oracle igcina inani lohlamvu kukholomu ethi VARCHAR2 ngendlela oyicacisa ngayo, ngaphandle kokupheda okungenalutho, inqobo nje uma inani lingeqi ubude bekholomu.

NUMBER Uhlobo Lwedatha
NUMBER uhlobo lwedatha lugcina uziro kanye nezinombolo ezigxilile eziphozithivu nezinegethivu ezinamavelu aphelele ukusuka ku-1.0 x 10-130 ukuya kodwa okungabandakanyi u-1.0 x 10126. Uma ucacisa isisho se-arithmetic inani laso elinenani eliphelele elikhulu noma elilingana no- 1.0 x 10126, bese i-Oracle ibuyisela iphutha. Inani ngalinye elingu-NUMBER lidinga amabhayithi angu-1 kuye kwangu-22. Uma kucatshangelwa lokhu, usayizi wekholomu ngamabhayithi yenani elithile ledatha yezinombolo NUMBER(p), lapho u-p ewukunemba yenani elinikeziwe, angabalwa kusetshenziswa ifomula elandelayo: Umjikelezo((ubude(p)+s)/2))+1 lapho u-s elingana noziro uma inombolo ithi phozithivu, futhi u-s elingana no-1 uma inombolo iyinegethivu.

Ngaphezu kwalokho, ake sithathe ingcaphuno evela emibhalweni emayelana nokugcina amanani aNull.

I-null ukungabikho kwenani kukholomu. Ama-nulls abonisa idatha engekho, engaziwa, noma engasebenzi. Ama-nulls agcinwa kusizindalwazi uma ewela phakathi kwamakholomu anamanani edatha. Kulezi zimo, zidinga ibhayithi elingu-1 ukuze kugcinwe ubude bekholomu (zero). Ama-null alandelanayo ngokulandelana awadingi isitoreji ngenxa yokuthi unhlokweni omusha womugqa ubonisa ukuthi amakholomu asele kumugqa odlule awasebenzi. Isibonelo, uma amakholomu amathathu okugcina ethebula engasebenzi, ayikho idatha egcinelwe lawa makholomu.

Ngokusekelwe kule datha, sakha ukucabanga. Sicabanga ukuthi isizindalwazi sisebenzisa umbhalo wekhodi we-AL32UTF8. Kulo mbhalo wekhodi, izinhlamvu zesiRashiya zizothatha amabhayithi angu-2.

1) A no-X, inani lenkambu ethi 'Y' lithatha ibhayithi elingu-1, inani lenkambu x 'D' lithatha amabhayithi angu-2
2) B kanye no-Y, 'Vasya' kokuthi b inani lizohlanganiswa nezikhala ezifika ezinhlamvini ezingu-10 futhi kuzothatha amabhayithi angu-14, 'Vasya' ku-d kuzothatha amabhayithi angu-8.
3) C kanye no-K. Zombili izinkambu zinenani elingu-NULL, ngemva kwazo kunezinkambu ezibalulekile, ngakho zisebenzisa i-byte engu-1.
4) C kanye no-Z. Zombili izinkambu zinenani elithi NULL, kodwa inkambu Z ingeyokugcina etafuleni, ngakho ayithathi isikhala (amabhayithi angu-0). I-Field C ithatha ibhayithi elingu-1.
5) K kanye no-Z. Okufana necala langaphambilini. Inani elisendaweni ka-K lithatha ibhayithi elingu-1, ku-Z – 0.
6) Mina kanye no-J. Ngokusho kwemibhalo, womabili amanani azothatha amabhayithi angu-2. Sibala ubude sisebenzisa ifomula ethathwe emibhalweni: nxazonke( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J kanye no-X. Inani enkambini ka-J lizothatha amabhayithi angu-2, inani elisendaweni engu-X lizothatha amabhayithi angu-2.

Sekukonke, izinketho ezifanele yilezi: C no-K, I no-J, J kanye no-X.

Yini cishe ezoba isici sokuhlanganisa senkomba ye-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);

• Cishe amashumi
• Cishe amakhulu
• Cishe izinkulungwane
• Cishe amashumi ezinkulungwane

ImpenduloNgokuvumelana nemibhalo ye-Oracle (ecashunwe ku-12.1):

Kunkomba yesihlahla esingu-B, isici sokuhlanganisa senkomba sikala ukuqoqwa kwangempela kwemigqa ngokuhlobene nenani lenkomba.

Isici sokuhlanganisa senkomba sisiza isilungiseleli sinqume ukuthi ukuskena kwenkomba noma ukuskena kwethebula eligcwele kusebenza kahle kakhulu emibuzweni ethile). Isici sokuhlanganisa esiphansi sibonisa ukuskena kwenkomba okusebenzayo.

Isici sokuhlanganisa esiseduze nenani lamabhulokhi kuthebula sibonisa ukuthi imigqa ihlelwa ngokoqobo kumabhuloki ethebula ngokhiye wenkomba. Uma isizindalwazi senza ukuskena kwetafula okugcwele, khona-ke isizindalwazi sivame ukubuyisa imigqa njengoba igcinwe kudiski ehlungwe ngokhiye wenkomba. Isici sokuhlanganisa esiseduze nenani lemigqa sibonisa ukuthi imigqa ihlakazeka ngokungahleliwe kuwo wonke amabhulokhi esizindalwazi ngokuhlobene nokhiye wenkomba. Uma isizindalwazi senza ukuskena kwethebula okugcwele, khona-ke isizindalwazi ngeke sikwazi ukubuyisa imigqa nganoma iyiphi indlela ehleliwe ngalo khiye wenkomba.

Kulesi simo, idatha ihlelwa kahle, ngakho-ke into yokuhlanganisa izolingana noma isondele nenani lamabhulokhi asetshenzisiwe kuthebula. Ngosayizi webhulokhi ojwayelekile wamakhilobhayithi ayi-8, ungalindela ukuthi amanani enombolo emincane angaba yinkulungwane azongena kubhulokhi eyodwa, ngakho-ke inani lamabhulokhi, futhi ngenxa yalokho, i-cluster factor izoba. cishe amashumi.

Kumaphi amanani ka-N lapho umbhalo olandelayo uzosetshenziswa ngempumelelo kusizindalwazi esivamile esinezilungiselelo ezijwayelekile?

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

ImpenduloNgokuvumelana nemibhalo ye-Oracle (ecashunwe ku-11.2):

Imikhawulo Yesizindalwazi Esinengqondo

Into
Uhlobo Lomkhawulo
Inani Lomkhawulo

izinkomba
Isamba sikasayizi wekholomu enenkomba
U-75% wosayizi webhulokhi yesizindalwazi susa okunye okungaphezulu

Ngakho, usayizi ophelele wamakholomu anezikhombo akufanele weqe u-6Kb. Okwenzeka ngokulandelayo kuncike ekubhaleni ngekhodi kwesisekelo esikhethiwe. Ngombhalo wekhodi we-AL32UTF8, uhlamvu olulodwa lungakwazi ukusebenzisa umkhawulo wamabhayithi angu-4, ngakho-ke esimweni esibi kakhulu, cishe izinhlamvu ezingu-6 zizongena kumakhilobhayithi angu-1500. Ngakho-ke, i-Oracle ngeke ikuvumele ukudalwa kwenkomba kokuthi N = 400 (lapho ubude bokhiye obubi kakhulu kuyizinhlamvu eziyi-1600 * amabhayithi angu-4 + nobude obulandelanayo), kuyilapho ku-N = 200 (noma ngaphansi) ukudala inkomba kuzosebenza ngaphandle kwezinkinga.

I-opharetha ethi INSERT enehinti ye-APPEND yakhelwe ukulayisha idatha ngemodi eqondile. Kwenzekani uma isetshenziswa etafuleni lapho okulengiswe khona isicupho?

• Idatha izolayishwa ngemodi eqondile, i-trigger izosebenza njengoba kulindelekile
• Idatha izolayishwa ngemodi eqondile, kodwa i-trigger ngeke isetshenziswe
• Idatha izolayishwa kwimodi evamile, i-trigger izosebenza ngendlela efanele
• Idatha izolayishwa ngemodi evamile, kodwa i-trigger ngeke isetshenziswe
• Idatha ngeke ilayishwe, iphutha lizorekhodwa

ImpenduloNgokuyisisekelo, lokhu kungaphezulu kombuzo we-logic. Ukuthola impendulo efanele, ngingaphakamisa imodeli yokucabanga elandelayo:

1. Ukufakwa kwimodi eqondile kwenziwa ngokubunjwa okuqondile kwebhulokhi yedatha, ukudlula injini ye-SQL, okuqinisekisa isivinini esikhulu. Ngakho-ke, ukuqinisekisa ukukhishwa kwe-trigger kunzima kakhulu, uma kungenakwenzeka, futhi akukho phuzu kulokhu, ngoba kusazonciphisa kakhulu ukufakwa.
2. Ukwehluleka ukwenza i-trigger kuzoholela eqinisweni lokuthi, uma idatha etafuleni ifana, isimo se-database sisonke (amanye amatafula) sizoncika kumodi lapho le datha ifakwe khona. Lokhu ngokusobala kuzocekela phansi ubuqotho bedatha futhi ngeke kusetshenziswe njengesixazululo ekukhiqizeni.
3. Ukungakwazi ukwenza umsebenzi oceliwe ngokuvamile kuthathwa njengephutha. Kodwa lapha kufanele sikhumbule ukuthi i-APPEND iwuphawu, futhi umqondo ojwayelekile wamacebo ukuthi ayacatshangelwa uma kungenzeka, kodwa uma kungenjalo, u-opharetha ubulawa ngaphandle kokucabangela isu.

Ngakho impendulo elindelekile ithi idatha izolayishwa ngemodi evamile (SQL), i-trigger izovutha.

Ngokuvumelana nemibhalo ye-Oracle (ecashunwe ku-8.04):

Ukwephulwa kwemikhawulo kuzodala ukuthi isitatimende sisebenze ngokulandelana, kusetshenziswa indlela yokufaka evamile, ngaphandle kwezixwayiso noma imilayezo yamaphutha. Okuhlukile ukukhawulelwa ezitatimendeni ezifinyelela kuthebula elifanayo izikhathi ezingaphezu kwesisodwa ekwenziweni, okungabangela imilayezo yamaphutha.
Isibonelo, uma izingcipho noma ubuqotho obuyireferensi bukhona etafuleni, iseluleko se-APPEND sizozitshwa uma uzama ukusebenzisa ukulayisha okuqondile okuthi INSERT (i-serial noma i-parallel), kanye nehinti noma umushwana othi PARALLEL, uma ukhona.

Kuzokwenzekani uma iskripthi esilandelayo senziwa?

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

• Ukwenza ngempumelelo
• Ukwehluleka ngenxa yephutha le-syntax
• Iphutha: Okwenziwayo Okuzenzakalelayo Akuvumelekile
• Iphutha elihlobene nokweqa ubuningi bokwenza isidleke sekholi
• Iphutha Lokuphulwa Kokhiye Wangaphandle
• Iphutha elihlobene nezingidi

ImpenduloIthebula ne-trigger zidalwe ngendlela efanele futhi lokhu kusebenza akufanele kuholele ezinkingeni. Ukuthengiselana okuzenzakalelayo kusiqalisi nakho kuvunyelwe, ngaphandle kwalokho ukuloga ngeke kwenzeke, ngokwesibonelo.

Ngemva kokufaka umugqa wokuqala, ukudubula kwe-trigger okuphumelelayo kungabangela ukuthi kufakwe umugqa wesibili, kubangele ukuthi i-trigger iqhume futhi, ifake umugqa wesithathu, njalonjalo kuze kube yilapho isitatimende sihluleka ngenxa yokweqa ubuningi bezingcingo. Nokho, kutholakala elinye iphuzu elicashile. Ngesikhathi isicupha sisetshenziswa, ukubophezela bekungakaqedwa kurekhodi lokuqala elifakiwe. Ngakho-ke, i-trigger egijima kumsebenzi ozimele izama ukufaka kuthebula umugqa obhekisela kukhiye wangaphandle kwirekhodi elingakenziwa. Lokhu kubangela ukulinda (umsebenzi ozimele ulinda ukuthi umsebenzi oyinhloko uzibophezele ukuze ubone ukuthi ungayifaka yini idatha) futhi ngesikhathi esifanayo ukuthengiselana okuyinhloko kulindela ukuthengiselana okuzenzakalelayo ukuze kuqhubeke nokusebenza ngemva kwe-trigger. I-deadlock iyenzeka futhi, ngenxa yalokho, ukuthengiselana okuzenzakalelayo kukhanselwe ngenxa yezizathu ezihlobene nezingidi.

Abasebenzisi ababhalisiwe kuphela abangabamba iqhaza kuhlolovo. Ngena ngemvume, wamukelekile.

Kwakunzima?

• Njengeminwe emibili, nganquma ngokushesha konke ngendlela efanele.

• Hhayi ngempela, benginephutha emibuzweni embalwa.

• Ngiyixazulule kahle ingxenye yayo.

• Ngiqagele impendulo kabili!

• Ngizobhala kuma comments

Bangu-14 abasebenzisi abavotile. Abasebenzisi abangu-10 bagobile.

Source: www.habr.com