🥇Ukulandela umkhondo we-Highload++ yaseSiberia 2019 - 8 imisebenzi kwi-Oracle

Sawubona!

NgoJuni 24-25, inkomfa ye-Highload ++ yaseSiberia 2019 yabanjwa eNovosibirsk abafana bethu nabo ingxelo "Iinqolobane zesikhongozeli se-Oracle (CDB/PDB) kunye nokusetyenziswa kwazo ngokubonakalayo kuphuhliso lwesoftware", siya kupapasha inguqulelo yombhalo kamva kancinane. Bekupholile, enkosi olegbunin yentlangano, kwanabo bonke abezayo.

Kule post, singathanda ukwabelana nawe ngeengxaki ebesinazo kwindawo yethu ukuze ukwazi ukuvavanya ulwazi lwakho lwe-Oracle. Ngezantsi kokusikwa kukho iingxaki ezisi-8, iinketho zokuphendula kunye nengcaciso.

Leliphi ixabiso eliphezulu lolandelelwano esiya kulibona njengesiphumo sokuphumeza esi script silandelayo?

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
Hayi, kuya kubakho impazamo

PhendulaNgokutsho kwamaxwebhu e-Oracle (ecatshulwe kwi-8.1.6):
Ngaphakathi kwengxelo enye yeSQL, iOracle izakwandisa ulandelelwano kube kanye kuphela kumqolo ngamnye. Ukuba isitatimenti siqulethe ngaphezu kwesinye ireferensi kwi-NEXTVAL yolandelelwano, i-Oracle inyusa ulandelelwano kube kanye kwaye ibuyisela ixabiso elifanayo kuzo zonke izehlo ze-NEXTVAL. Ukuba isitatimenti siqulethe iimbekiselo kuzo zombini i-CURRVAL kunye ne-NEXTVAL, i-Oracle inyusa ulandelelwano kwaye ibuyisela ixabiso elifanayo kuzo zombini i-CURRVAL kunye ne-NEXTVAL kungakhathaliseki ukuba i-odolo yabo ngaphakathi kwengxelo.

Ngoko ke, ixabiso eliphezulu liya kuhambelana nenani lemigca, oko kukuthi 5.

Mingaphi imiqolo eyakuba kwitheyibhile njengesiphumo sokusebenzisa okushicilelweyo okulandelayo?

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

PhendulaNgokutsho kwamaxwebhu e-Oracle (ecatshulwe kwi-11.2):

Phambi kokuphumeza nasiphi na isitatimenti se-SQL, i-Oracle iphawula indawo yokugcina efihlakeleyo (ayifumaneki kuwe). Emva koko, ukuba isitatimenti asiphumelelanga, i-Oracle iyayibuyisela ngokuzenzekelayo kwaye ibuyisele ikhowudi yemposiso esebenzayo kwi-SQLCODE kwi-SQLCA. Umzekelo, ukuba isiteyitimenti se-INSERT sibangela impazamo ngokuzama ukufaka ixabiso eliphindwe kabini kwisalathiso esisodwa, ingxelo iphinda ibuyiselwe umva.

Ukufowunela i-HP kumxhasi kwakhona kuqwalaselwa kwaye kusetyenzwe njengengxelo enye. Ngaloo ndlela, umnxeba wokuqala we-HP ugqiba ngempumelelo, emva kokufaka iirekhodi ezintathu; umnxeba wesibini we-HP uphela ngempazamo kwaye ubuyisela irekhodi yesine ekwazile ukuyifaka; umnxeba wesithathu awuphumeleli, kwaye kukho iirekhodi ezintathu kwitheyibhile.

Mingaphi imiqolo eyakuba kwitheyibhile njengesiphumo sokusebenzisa okushicilelweyo okulandelayo?

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

PhendulaNgokutsho kwamaxwebhu e-Oracle (ecatshulwe kwi-11.2):

Isithintelo sokukhangela sikuvumela ukuba uchaze imeko ekufuneka umqolo ngamnye kwitheyibhile uyanelisa. Ukwanelisa umqobo, umqolo ngamnye kwitheyibhile kufuneka wenze imeko ibe TRUE okanye ayaziwa (ngenxa ye-null). Xa i-Oracle ivavanya imeko yothintelo lwetshekhi kumqolo othile, nawaphi na amagama ekholamu kwimeko abhekisa kumaxabiso ekholamu kulo mqolo.

Ngaloo ndlela, i-value null iya kudlula isheke, kwaye ibhloko engaziwa iya kuphunyezwa ngempumelelo kude kube ngumzamo wokufaka ixabiso 3. Emva koku, ibhloko yokusingatha impazamo iya kucima okungafaniyo, akukho kubuyiselwa umva kuya kwenzeka, kwaye kushiyeke imiqolo emine etafileni ngexabiso 1, null, 2 kwaye null kwakhona.

Zeziphi izibini zamaxabiso eziya kuthatha indawo efanayo kwibhloko?

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 kunye no-X
B kunye noY
C kunye noK
C kunye noZ
K kunye noZ
Mna kunye noJ
J kunye no-X
Zonke zidweliswe

PhendulaNazi izicatshulwa ezivela kumaxwebhu (12.1.0.2) ekugcineni iintlobo ezahlukeneyo zedatha kwi-Oracle.

CHAR Uhlobo lweDatha
Uhlobo lwedatha ye-CHAR luchaza umtya woonobumba wobude obusisigxina kwiseti yoonobumba bedatabase. Ukhankanya isiseko somlinganiswa wesiseko xa usenza idatabase yakho. I-Oracle iqinisekisa ukuba onke amaxabiso agcinwe kwikholamu ye-CHAR anobude obuchazwe ngobungakanani kwisemantiki yobude obukhethiweyo. Ukuba ufaka ixabiso elifutshane kunobude bekholamu, ngoko i-Oracle blank-pads ixabiso kubude bomhlathi.

VARCHAR2 Uhlobo lweDatha
Uhlobo lwedatha ye-VARCHAR2 luchaza umtya wobumba-obude obuguqukayo kwiseti yoonobumba bedatabase. Ukhankanya isiseko somlinganiswa wesiseko xa usenza idatabase yakho. I-Oracle igcina ixabiso lomlinganiswa kwi-VARCHAR2 ikholamu kanye njengoko uyichaza, ngaphandle kwe-padding engenanto, ngaphandle kokuba ixabiso alidluli ubude boluhlu.

NUMBER Uhlobo lweDatha
Uhlobo lwedatha NUMBER lugcina u-zero kunye no-positive kunye no-negative amanani asisigxina kunye namaxabiso apheleleyo ukusuka ku-1.0 x 10-130 ukuya kodwa kungabandakanywa 1.0 x 10126. 1.0 x 10126, emva koko i-Oracle ibuyisela imposiso. Ixabiso ngalinye elithi NUMBER lifuna ukusuka kwi-1 ukuya kwi-22 bytes. Ukuthathela ingqalelo oku, ubungakanani bomhlathi kwiibytes zexabiso ledatha yamanani athile NUMBER(p), apho ip ikukuchaneka kwexabiso elinikiweyo, ingabalwa kusetyenziswa le fomula ilandelayo: UMJIKELO((ubude(p)+s)/2))+1 apho u-s elingana no-zero ukuba inani li-positive, kwaye u-s elingana no-1 ukuba inani li-negative.

Ukongeza, makhe sithathe isicatshulwa kuxwebhu malunga nokugcina amaxabiso aNull.

I-null kukungabikho kwexabiso kumhlathi. Ii-nulls zibonisa idatha engekhoyo, engaziwayo, okanye engasebenziyo. Ii-nulls zigcinwe kwisiseko sedatha ukuba ziwela phakathi kweekholamu ezinamaxabiso edatha. Kule meko, zifuna i-byte eyi-1 ukugcina ubude bekholomu (zero). Ama-nulls alandelayo kumqolo afuna ugcino kuba iheader entsha yomqolo ibonisa ukuba iikholamu eziseleyo kumqolo ongaphambili azikho. Umzekelo, ukuba iikholamu ezintathu zokugqibela zetafile azinamsebenzi, ngoko akukho datha igcinelwe le kholamu.

Ngokusekelwe kwezi datha, sakha ukuqiqa. Sicinga ukuba i-database isebenzisa i-AL32UTF8 encoding. Kule khowudi, iileta zesiRashiya ziya kuhlala iibytes ezi-2.

1) A kunye no X, ixabiso lentsimi 'Y' ithatha i-byte eyi-1, ixabiso lendawo x 'D' lithatha iibyte ezimbini.
2) B kunye no-Y, 'Vasya' ku-b ixabiso liza kufakwa kwizithuba ukuya kutsho kwii-10 iibhayithi, 'Vasya' ngo-d iyakuthatha i-14 bytes.
3) C kunye no-K. Zombini iindawo zinexabiso le-NULL, emva kwazo kukho iindawo ezibalulekileyo, ngoko zihlala kwi-byte eyi-1.
4) C no Z. Yomibini imihlaba inexabiso NULL, kodwa indawo Z ngowokugqibela kwitafile, ngoko ayithathi sithuba (0 bytes). Ummandla C uthatha ibhayithi enye.
5) K kunye no-Z. Ngokufanayo kwimeko yangaphambili. Ixabiso kumhlaba we-K lithatha i-byte eyi-1, ku-Z – 0.
6) Mna kunye no-J. Ngokwamaxwebhu, zombini amaxabiso aya kuthatha iibytes ezi-2. Sibala ubude usebenzisa ifomyula ethathwe kumaxwebhu: ngeenxa zonke ( (1 + 0) / 2) +1 = 1 + 1 = 2.
7) J kunye no-X. Ixabiso kwi-J field liya kuthatha ii-bytes ezi-2, ixabiso kwintsimi ye-X liya kuthatha ii-bytes ezi-2.

Lilonke, ukhetho oluchanekileyo lu: C kunye no-K, I no-J, J kunye no-X.

Yintoni malunga nokuba yindibaniselwano yesalathisi se 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);

Malunga neshumi
Malunga namakhulu
Malunga namawaka
Malunga namashumi amawaka

PhendulaNgokutsho kwamaxwebhu e-Oracle (ecatshulwe kwi-12.1):

Kwisalathiso somthi we-B, isalathisi sokuhlanganisana silinganisa ukwahlulahlula ngokwamaqela ngokwemiqolo ngokunxulumene nexabiso lesalathisi.

Isalathiso sokuhlanganisana sinceda isilungisi isigqibo sokuba ingaba iskena sesalathisi okanye iskena esipheleleyo setafile sisebenza ngakumbi kwimibuzo ethile. Into ephantsi yokudibanisa ibonisa isalathisi esisebenzayo.

I-clustering factor ekufutshane nenani leebhloko kwitheyibhile ibonisa ukuba imiqolo ilawulwa ngokwenyama kwiibhloko zetafile ngeqhosha lesalathisi. Ukuba idatabase yenza iskena setafile epheleleyo, ngoko ke ugcino lwedatha lukholisa ukubuyisela imiqolo njengoko igcinwe kwidiski ehlelwe ngeqhosha lesalathisi. Into edibanisayo ekufutshane nenani lemiqolo ibonisa ukuba imiqolo ihlakazekile ngokungaqhelekanga kwiibhloko zesiseko sedatha ngokumalunga neqhosha lesalathisi. Ukuba uvimba weenkcukacha wenza iskeni esipheleleyo setafile, ngoko ke ugcino lwedatha alunakufumana kwakhona imiqolo kulo naluphi na ulungelelwaniso olucwangcisiweyo ngeli qhosha lesalathisi.

Kule meko, idatha ihlelwe ngokufanelekileyo, ngoko ke i-clustering factor iya kulingana okanye isondele kwinani leebhloko ezihlala kwitheyibhile. Kubungakanani obuqhelekileyo bebhloko yeekhilobhayithi ezisi-8, unokulindela ukuba malunga newaka lamanani anqabileyo amanani aya kungena kwibhloko enye, ngoko ke inani leebhloko, kwaye ngenxa yoko, into edibeneyo iya kuba. malunga neshumi.

Ngawaphi amaxabiso e-N aya kuthi oku script kulandelayo kwenziwe ngempumelelo kwisiseko sedatha esiqhelekileyo kunye nezicwangciso ezisemgangathweni?

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

PhendulaNgokutsho kwamaxwebhu e-Oracle (ecatshulwe kwi-11.2):

Imida yedatabase esengqiqweni

umcimbi
Uhlobo loMda
Ixabiso lomda

Izalathiso
Ubungakanani bubonke bekholamu enesalathisi
I-75% yobungakanani bebhloko yedatabase thabatha umphezulu othile

Ngaloo ndlela, ubungakanani obupheleleyo beekholomu ezinesalathisi akufanele zidlule i-6Kb. Kwenzeka ntoni ngokulandelayo kuxhomekeke kwisiseko esikhethiweyo sokukhowudwa. Kwi-AL32UTF8 encoding, umlinganiswa omnye unokuthatha ubuninzi be-4 bytes, ngoko ke kwimeko embi kakhulu, malunga ne-6 oonobumba bayakungena kwiikhilobhayithi ezi-1500. Ngoko ke, i-Oracle ayizukuvumela ukudalwa kwesalathiso kwi-N = 400 (xa eyona nto imbi ubude besitshixo inamagama ayi-1600 * 4 bytes + rowd length), ngelixa e-N = 200 (okanye ngaphantsi) ukudala isalathisi kuya kusebenza ngaphandle kweengxaki.

Umsebenzisi we-INSERT onengcebiso ye-APPEND yenzelwe ukulayisha idatha kwimo ethe ngqo. Kwenzeka ntoni ukuba isetyenziswe kwitafile ekuxhonywe kuyo i-trigger?

Idatha iya kulayishwa kwimodi ethe ngqo, i-trigger iya kusebenza njengoko kulindelwe
Idatha iya kulayishwa kwimodi ethe ngqo, kodwa i-trigger ayiyi kuphunyezwa
Idatha iya kulayishwa kwimodi eqhelekileyo, i-trigger iya kusebenza njengoko kufanelekile
Idatha iya kulayishwa kwimodi eqhelekileyo, kodwa i-trigger ayiyi kuphunyezwa
Idatha ayiyi kulayishwa, impazamo iya kubhalwa

PhendulaNgokusisiseko, oku kungaphezulu kombuzo wengqiqo. Ukufumana impendulo echanekileyo, ndingacebisa le modeli ilandelayo yokuqiqa:

Ukufakwa kwimodi ethe ngqo kwenziwa ngokuqulunqwa ngokuthe ngqo kwebhloko yedatha, ukudlula injini ye-SQL, eqinisekisa isantya esiphezulu. Ngaloo ndlela, ukuqinisekiswa kokuphunyezwa kwe-trigger kunzima kakhulu, ukuba akunakwenzeka, kwaye akukho ngongoma kule nto, kuba iya kuthoba ngokukhawuleza ukufakwa.
Ukungaphumeleli ukuphumeza i-trigger kuya kukhokelela kwinto yokuba, ukuba idatha kwitheyibhile iyafana, imeko yesiseko sedatha iyonke (ezinye iitafile) ziya kuxhomekeka kwindlela apho le datha ifakwe khona. Oku kuya kutshabalalisa ngokucacileyo ingqibelelo yedatha kwaye ayinakusetyenziswa njengesisombululo kwimveliso.
Ukungakwazi ukwenza umsebenzi oceliweyo kuthathwa njengempazamo. Kodwa apha kufuneka sikhumbule ukuba i-APPEND luthsuphe, kwaye ingqiqo ngokubanzi yeengcebiso kukuba zithathelwa ingqalelo ukuba kuyenzeka, kodwa ukuba akunjalo, umqhubi uyabulawa ngaphandle kokuthathela ingqalelo ingcebiso.

Ngoko impendulo elindelekileyo idatha iya kulayishwa kwimo yesiqhelo (SQL), i-trigger iya kutshisa.

Ngokutsho kwamaxwebhu e-Oracle (ecatshulwe kwi-8.04):

Ukwaphulwa kwezithintelo kuya kubangela ukuba ingxelo iqhutywe ngokulandelelana, kusetyenziswa indlela yokufaka eqhelekileyo, ngaphandle kwezilumkiso okanye imiyalezo yempazamo. Imeko eyahlukileyo kukuthintelwa kwiingxelo ezifikelela kwitheyibhile enye ngaphezu kwesihlandlo esinye kwintengiselwano, enokubangela imiyalezo yemposiso.
Umzekelo, ukuba izivuseleli okanye ingqibelelo yozalathiso ikhona kwitheyibhile, ngoko ke i-APPEND icebiso liya kuhoywa xa uzama ukusebenzisa umthwalo othe ngqo INSERT (uthotho okanye ungqamene), kunye ne-PARALLEL hint okanye igatya, ukuba likhona.

Kuya kwenzeka ntoni xa umbhalo olandelayo uphunyeziwe?

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

Ukugqitywa ngempumelelo
Ukusilela ngenxa yempazamo yesivakalisi
Imposiso: Intengiselwano eZimeleyo ayisebenzi
Imposiso enxulumene nokugqithisa ubuninzi bokufowuna kwendlwane
Imposiso yoLwaphulo-mthetho lwangaphandle
Imposiso enxulumene nezitshixo

PhendulaItheyibhile kunye ne-trigger zenziwe ngokuchanekileyo kwaye lo msebenzi akufuneki ukhokhelele kwiingxaki. Iintengiselwano ezizimeleyo kwi-trigger nazo zivumelekile, kungenjalo ukuloga ngekhe kwenzeke, umzekelo.

Emva kokufaka umqolo wokuqala, ukudubula kokudubula okuphumelelayo bekuya kubangela ukuba kufakwe umqolo wesibini, nto leyo ebangela ukuba isiqhushumbisi siqhume kwakhona, sifake umqolo wesithathu, njalo njalo de loo ngxelo yasilela ngenxa yokugqithisa ubuninzi bendlwane yokufowuna. Noko ke, kukho enye ingongoma echuliweyo. Ngexesha i-trigger isenziwa, ukuzibophelela akukagqitywa kwirekhodi yokuqala efakiweyo. Ke ngoko, i-trigger esebenza kwitransekshini yokuzimela izama ukufaka kwitheyibhile umqolo obhekisa isitshixo sangaphandle kwirekhodi engekafakwa. Oku kubangela ukulinda (i-autonomous transaction ilindele ukuba i-transaction engundoqo izinikele ukuze ibone ukuba ingafaka idatha) kwaye ngexesha elifanayo ukuthengiswa okuphambili kulinde ukuba ukuthengiswa kokuzimela kuqhubeke nokusebenza emva kwe-trigger. I-deadlock iyenzeka kwaye, ngenxa yoko, intengiselwano yokuzimela iyacinywa ngenxa yezizathu ezinxulumene nezitshixo.

Ngabasebenzisi ababhalisiweyo kuphela abanokuthatha inxaxheba kuphando. Ngena, ndiyacela.

Kwakunzima ukuba?

Njengeminwe emibini, ngokukhawuleza ndagqiba yonke into ngokuchanekileyo.
Akunjalo, bendingalunganga kwimibuzo embalwa.
Ndiyisombulule isiqingatha sayo ngokuchanekileyo.
Ndayiqikelela impendulo kabini!
Ndiza kubhala kwi-comments

Bali-14 abasebenzisi abavotileyo. Abasebenzisi abasi-10 abakhange.

umthombo: www.habr.com

Ukulandela emanyathelweni e-Highload++ yaseSiberia 2019 - 8 imisebenzi kwi-Oracle

Leliphi ixabiso eliphezulu lolandelelwano esiya kulibona njengesiphumo sokuphumeza esi script silandelayo?

Mingaphi imiqolo eyakuba kwitheyibhile njengesiphumo sokusebenzisa okushicilelweyo okulandelayo?

Mingaphi imiqolo eyakuba kwitheyibhile njengesiphumo sokusebenzisa okushicilelweyo okulandelayo?

Zeziphi izibini zamaxabiso eziya kuthatha indawo efanayo kwibhloko?

Yintoni malunga nokuba yindibaniselwano yesalathisi se T_I?

Ngawaphi amaxabiso e-N aya kuthi oku script kulandelayo kwenziwe ngempumelelo kwisiseko sedatha esiqhelekileyo kunye nezicwangciso ezisemgangathweni?

Umsebenzisi we-INSERT onengcebiso ye-APPEND yenzelwe ukulayisha idatha kwimo ethe ngqo. Kwenzeka ntoni ukuba isetyenziswe kwitafile ekuxhonywe kuyo i-trigger?

Kuya kwenzeka ntoni xa umbhalo olandelayo uphunyeziwe?

Kwakunzima ukuba?

Yongeza izimvo Phendula impendulo