ProHoster > Blog > Utongi > Kutevera mumakwara eHighload ++ Siberia 2019 - 8 mabasa paOracle

Kutevera mumakwara eHighload ++ Siberia 2019 - 8 mabasa paOracle

Nhai!

Musi waChikumi 24-25, musangano weHighload++ Siberia 2019 wakaitirwa muNovosibirsk. Vakomana vedu vaivepowo. report "Oracle mudziyo dhatabhesi (CDB/PDB) uye mashandisiro azvo anoshanda mukuvandudza software", tichaburitsa chinyorwa chinyorwa gare gare. Zvaitonhorera, ndatenda olegbunin nokuda kwesangano, uye nokuna vose vakauya.

Kutevera mumakwara eHighload ++ Siberia 2019 - 8 mabasa paOracle
Mune ino post, tinoda kugoverana newe matambudziko ataive nawo padumba redu kuitira kuti uedze ruzivo rwako rweOracle. Pazasi pekuchekwa pane 8 matambudziko, mhinduro sarudzo uye tsananguro.

Ndeipi iyo yakanyanya kutevedzana kukosha kwatichaona semhedzisiro yekuita inotevera script?

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
  • Kwete, pachava nekukanganisa

ReplyZvinoenderana neOracle zvinyorwa (zvakatorwa kubva ku8.1.6):
Mukati mechirevo chimwe cheSQL, Oracle ichawedzera kutevedzana kamwe chete pamutsara. Kana chirevo chiine kanopfuura kamwechete kunoreva NEXTVAL yenhevedzano, Oracle inowedzera kutevedzana kamwe uye inodzosa kukosha kwakafanana pazviitiko zvese zveNEXTVAL. Kana chirevo chiine mareferenzi kune ese ari maviri CURRVAL neNEXTVAL, Oracle inowedzera kutevedzana uye inodzosera kukosha kwakafanana kune zvese CURRVAL neNEXTVAL zvisinei nekurongeka kwavo mukati mechirevo.

Nokudaro, kukosha kwepamusoro kuchaenderana nehuwandu hwemitsara, iyo 5.

Mitsetse mingani ichave mutafura semhedzisiro yekumhanyisa script inotevera?

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

ReplyZvinoenderana neOracle zvinyorwa (zvakatorwa kubva ku11.2):

Usati waita chero chirevo cheSQL, Oracle inomaka yakasarudzika yekuchengetedza (isipo kwauri). Zvino, kana chirevo chikatadza, Oracle anochidzosera otomatiki uye anodzosera iyo inoshanda yekukanganisa kodhi kuSQLCODE muSQLCA. Semuenzaniso, kana INSERT statement ikakonzeresa kukanganisa nekuyedza kuisa kukosha kweduplicate muindex yakasarudzika, chirevo chinodzoserwa kumashure.

Kufonera HP kubva kumutengi kunotariswawo uye kugadziridzwa sechirevo chimwe chete. Saka, yekutanga HP kufona inopedza zvinobudirira, yaisa marekodhi matatu; yechipiri HP kufona inopera nekukanganisa uye inodzosera kumashure rekodhi rechina iyo yakakwanisa kuisa; runhare rwechitatu rwakundikana, uye pane zvinyorwa zvitatu patafura.

Mitsetse mingani ichave mutafura semhedzisiro yekumhanyisa script inotevera?

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

ReplyZvinoenderana neOracle zvinyorwa (zvakatorwa kubva ku11.2):

Cheki chinokumanikidza chinokuita kuti utaure mamiriro ayo mutsara wega wega mutafura unofanirwa kugutsa. Kugutsa chinomanikidza, mutsara wega wega patafura unofanirwa kuita kuti mamiriro acho ave TRUE kana asingazivikanwe (nekuda kwekushaikwa). Kana Oracle inoongorora cheki yekumanikidza mamiriro eimwe mutsara, chero mazita emakoroni ari mumamiriro ezvinhu anoreva makoramu akomu mumutsara iwoyo.

Nokudaro, kukosha null kuchapfuura cheki, uye chivharo chisingazivikanwi chichaitwa zvinobudirira kusvikira kuedza kuisa kukosha 3. Mushure meizvi, kukanganisa kwekubata chivharo kuchabvisa kunze, hapana rollback ichaitika, uye pachasara misara mina patafura ine kukosha 1, null, 2 uye null zvakare.

Ndeapi mapairi emhando achatora huwandu hwakafanana hwenzvimbo mubhuroka?

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 uye X
  • B uye Y
  • C uye K
  • C uye Z
  • K uye Z
  • Ini uye J
  • J uye X
  • Zvose zvakanyorwa

ReplyHezvino zvinyorwa kubva muzvinyorwa (12.1.0.2) pakuchengetedza marudzi akasiyana-siyana e data muOracle.

CHAR Data Type
Iyo CHAR data mhando inotsanangura yakatarwa-urefu hunhu tambo mune yedatabase mavara set. Iwe unotsanangura iyo dhatabhesi yemhando yakatarwa paunogadzira yako database. Oracle inova nechokwadi chekuti ese ma values ​​akachengetwa muCHAR column ane hurefu hwakatsanangurwa nehukuru mune yakasarudzwa kureba semantics. Kana iwe ukaisa kukosha kupfupi pane kureba kwekoramu, ipapo Oracle blank-pads kukosha kune kureba kwekoramu.

VARCHAR2 Data Type
Iyo VARCHAR2 data yerudzi inotsanangudza mutsara-urefu mutsara mudura re data set. Iwe unotsanangura iyo dhatabhesi yemhando yakatarwa paunogadzira yako database. Oracle inochengeta kukosha kwehunhu muVARCHAR2 koramu chaizvo sezvaunotsanangura, pasina chero blank-padding, chero kukosha kukasapfuura kureba kwekoramu.

NUMBER Data Type
Rudzi rwe NUMBER data rinochengeta zero pamwe nechakanaka nechakaipa chakasimwa manhamba ane absolute values ​​​​kubva pa1.0 x 10-130 kuenda asi zvisingasanganisire 1.0 x 10126. 1.0 x 10126, ipapo Oracle inodzosa kukanganisa. Imwe neimwe NUMBER kukosha inoda kubva pa1 kusvika pa22 bytes. Tichifunga izvi, saizi yekoramu mumabhaiti kune imwe nhamba yedhata kukosha NUMBER(p), apo p ndiyo chaiyo yemutengo wakapihwa, inogona kuverengerwa uchishandisa inotevera formula: RUNDONGO((kureba(p)+s)/2))+1 apo s akaenzana ne zero kana nhamba iri positive, uye s akaenzana ne 1 kana nhamba iri negative.

Mukuwedzera, ngatitorei chidimbu kubva pane zvinyorwa nezve kuchengetedza Null tsika.

A null kusavapo kweukoshi muchikamu. Nulls inoratidza dhata risipo, risingazivikanwe, kana risingashandiswe. Nulls inochengetwa mudhatabhesi kana ikawira pakati pemakoramu ane data data. Muzviitiko izvi, vanoda 1 byte kuchengetedza kureba kwekoramu (zero). Kutevera null mumutsara hakudi kuchengetedza nekuti mutsva wemutsara wemusoro unoratidza kuti makoramu asara mumutsara wapfuura haana. Semuenzaniso, kana makoramu matatu ekupedzisira etafura asina, saka hapana data inochengeterwa makoramu aya.

Kubva pane idzi data, tinovaka kufunga. Isu tinofungidzira kuti dhatabhesi inoshandisa AL32UTF8 encoding. Mune encoding iyi, mavara echiRussia anotora 2 bytes.

1) A uye X, kukosha kwemunda 'Y' kunotora 1 byte, kukosha kwemunda x 'D' kunotora 2 bytes.
2) B uye Y, 'Vasya' mu b kukosha kuchaputirwa nenzvimbo dzinosvika kumavara gumi uye zvinotora mabhaiti gumi nemana, 'Vasya' muna d achatora 10 bytes.
3) C uye K. Zvose minda dzine kukosha NULL, shure kwavo kune minda inokosha, saka inotora 1 byte.
4) C uye Z. Minda miviri ine kukosha NULL, asi munda Z ndiyo yekupedzisira patafura, saka haitore nzvimbo (0 bytes). Munda C unotora 1 byte.
5) K uye Z. Zvakafanana nezvakaitika kare. Kukosha mumunda weK kunotora 1 byte, muZ - 0.
6) Ini naJ. Zvinoenderana nezvinyorwa, zvese zvakakosha zvinotora 2 bytes. Isu tinoverenga hurefu tichishandisa fomula yakatorwa kubva muzvinyorwa: kutenderera ((1 + 0)/2) +1 = 1 + 1 = 2.
7) J uye X. Kukosha mumunda weJ kuchatora 2 bytes, kukosha mundima X kuchatora 2 bytes.

Pakazara, sarudzo dzakakodzera ndeidzi: C uye K, I naJ, J uye X.

Chii chingave chinenge chiri clustering factor yeT_I index?

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

  • Anenge makumi
  • Anenge mazana
  • Zvinenge zviuru
  • Zvinenge makumi ezviuru

ReplyZvinoenderana neOracle zvinyorwa (zvakatorwa kubva ku12.1):

Kune B-muti indekisi, index clustering factor inoyera kuunganidzwa chaiko kwemitsara maererano nehukoshi hweindex.

Iyo index clustering factor inobatsira optimizer kusarudza kana index index kana yakazara tafura scan inonyatso shanda kune mimwe mibvunzo). A low clustering factor inoratidza kunyatsoita index scan.

A clustering factor iri padyo nenhamba yezvivharo mutafura inoratidza kuti mitsara yakarongedzerwa munyama mumabhuroko etafura nekiyi index. Kana iyo dhatabhesi ikaita yakazara tafura scan, saka iyo dhatabhesi inoda kutora mitsara sezvainochengetwa padhisiki yakarongwa nekiyi index. A clustering factor iri padyo nenhamba yemitsara inoratidza kuti mitsara yakapararira zvisina tsarukano pamabhuroko edhatabhesi maererano nekiyi ye index. Kana iyo dhatabhesi ikaita yakazara tafura scan, saka dhatabhesi yaisazotora mitsara mune chero yakarongedzwa kurongeka neyiyi index kiyi.

Muchiitiko ichi, iyo data yakarongedzwa zvakanaka, saka iyo clustering factor ichave yakaenzana kana pedyo nenhamba yezvivharo zvakagarwa mutafura. Kune yakajairwa block saizi ye8 kilobytes, unogona kutarisira kuti ingangoita chiuru yakamanikana nhamba kukosha ichakwana mubhuroko rimwe, saka huwandu hwezvivharo, uye semhedzisiro, iyo clustering factor ichave. anenge makumi.

Ndedzipi tsika dzeN iyo inotevera script ichaitwa zvinobudirira mune yenguva dzose dhatabhesi ine yakajairwa marongero?

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

ReplyZvinoenderana neOracle zvinyorwa (zvakatorwa kubva ku11.2):

Logical Database Limits

chinhu
Type of Limit
Limit Value

Indexes
Saizi yese ye column yakarongwa
75% yedatabase block saizi kubvisa zvimwe pamusoro

Saka, saizi yese yemakoramu ane indexed haifanire kudarika 6Kb. Chii chinoitika chinotevera zvinoenderana neyakasarudzwa base encoding. Kune AL32UTF8 encoding, munhu mumwe chete anogona kutora anodarika mana mabhayiti, saka mumamiriro ezvinhu akaipa kwazvo, mavara angangoita 4 anokwana mu6 kilobytes. Naizvozvo, Oracle haibvumiri kusikwa kwe index paN = 1500 (apo iyo yakaipisisa kesi kiyi kureba iri 400 mavara * 1600 bytes + rowd kureba), nepo. paN = 200 (kana pasi) kugadzira index kuchashanda pasina matambudziko.

Iyo INSERT opareta ine APPEND hint yakagadzirirwa kurodha data mune yakananga modhi. Chii chinoitika kana chikaiswa patafura inoturikwa chinokonzeresa?

  • Iyo data ichatakurwa mune yakananga modhi, iyo inokonzeresa ichashanda sezvaitarisirwa
  • Iyo data ichatakurwa mune yakananga modhi, asi chinokonzeresa hachizoitwa
  • Iyo data ichatakurwa mune yakajairika mode, iyo inokonzeresa ichashanda sezvainofanirwa
  • Iyo data ichaiswa mune yakajairwa modhi, asi chinokonzeresa hachizoitwa
  • Iyo data haizotakurwa, kukanganisa kucharekodhwa

ReplyChaizvoizvo, uyu ndiwo mubvunzo wepfungwa. Kuti ndiwane mhinduro chaiyo, ini ndingapa zano rinotevera rekufunga modhi:

  1. Kupinza mune yakananga modhi kunoitwa nekuumbwa kwakananga kwechivharo che data, ichipfuura iyo SQL injini, iyo inovimbisa kumhanya kukuru. Nokudaro, kuvimbisa kuurayiwa kwechigadziro kwakaoma zvikuru, kana zvisingaiti, uye hapana chikonzero mune izvi, sezvo zvicharamba zvichinonoka kuisa pasi.
  2. Kukundikana kuita chinokonzeresa kuchaita kuti chokwadi chekuti, kana iyo data iri mutafura yakafanana, mamiriro edhatabhesi sese (mamwe matafura) anozoenderana neiyo nzira iyo data iyi yakaiswa. Izvi zvichava pachena kuparadza kuvimbika kwedata uye hazvigone kushandiswa semhinduro mukugadzira.
  3. Kutadza kuita basa rakumbirwa kunowanzotorwa semhosho. Asi pano tinofanira kuyeuka kuti APPEND izano, uye pfungwa huru yezviyero ndeyokuti ivo vanotariswa kana zvichibvira, asi kana zvisingaiti, mutyairi anourayiwa pasina kutora zano.

Saka mhinduro inotarisirwa ndeye iyo data ichaiswa mune yakajairika (SQL) modhi, iyo inokonzeresa ichapisa.

Zvinoenderana neOracle zvinyorwa (zvakatorwa kubva ku8.04):

Kutyorwa kwezvirambidzo kuchaita kuti chirevo chiite seri, uchishandisa yakajairika nzira yekuisa, pasina yambiro kana mhosho. Chinosarudzika chirambidzo pazvirevo zvinosvika patafura imwechete kanopfuura kamwe mukutengeserana, izvo zvinogona kukonzera kukanganisa meseji.
Semuyenzaniso, kana zvinokonzeresa kana kutendeseka kuripo patafura, ipapo iyo APPEND hint icharegererwa paunoyedza kushandisa yakananga-mutoro INSERT (serial kana parallel), pamwe nePARALLEL hint kana clause, kana iripo.

Chii chichaitika kana chinyorwa chinotevera chaitwa?

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

  • Kupedzwa kwakabudirira
  • Kutadza nekuda kwekukanganisa kwe syntax
  • Mhosho: Autonomous Transaction Haisi Kushanda
  • Mhosho inechekuita nekudarika uwandu hwekudanwa nesting
  • Kukanganisa Kukanganisa Kukosha Kwekunze
  • Mhosho ine chekuita nekukiya

ReplyIyo tafura uye inokonzeresa yakasikwa nemazvo uye kushanda uku hakufanire kutungamirira kumatambudziko. Autonomous transactions mune trigger inobvumidzwawo, zvikasadaro kutema miti hakugone, semuenzaniso.

Mushure mekuisa mutsara wekutanga, kupfura kunobudirira kwaizokonzera kuti mutsara wechipiri uiswe, zvichiita kuti chibatiso chipfute zvakare, chichiisa mutsara wechitatu, zvichingodaro kusvikira chirevo chacho chakundikana nekuda kwekupfuura hukuru hwematendere ekufona. Zvisinei, imwe pfungwa yakavanzika inopinda. Panguva iyo trigger inoitwa, kuzvipira hakusati kwapedzwa kune rekodhi rekutanga rakaiswa. Naizvozvo, chinokonzeresa chinomhanya mukuzvimiririra kutengeserana chinoedza kuisa mutafura mutsara unoreva kiyi yekune imwe nyika kune rekodhi risati raitwa. Izvi zvinoguma nekumirira (kuzvimirira kwekutengeserana kunomirira kuti kutengeserana kukuru kuite kuti kuone kana kuchigona kuisa data) uye panguva imwe chete iyo huru yekutengeserana inomirira iyo inozvimiririra yekutengeserana kuti ienderere mberi kushanda mushure mekutsvaga. Kufa kunoitika uye, semhedzisiro, iyo inozvimiririra transaction inodzimwa nekuda kwezvikonzero zvine chekuita nekukiya.

Vashandisi vakanyoresa chete ndivo vanogona kutora chikamu muongororo. Nyorera mu, Munogamuchirwa.

Zvakanga zvakaoma?

  • Sezvigunwe zviviri, ndakabva ndangosarudza zvese nemazvo.

  • Kwete chaizvo, ndakanga ndisina kururama pamibvunzo miviri.

  • Ndakagadzirisa hafu yacho nemazvo.

  • Ndakafungidzira mhinduro kaviri!

  • Ndichanyora mumacomments

14 vashandisi vakavhota. 10 vashandisi vakaramba.

Source: www.habr.com

Voeg