Pulogalamu ya ProHoster > Blog > Ulamuliro > Kutsatira mapazi a Highload++ Siberia 2019 - 8 ntchito pa Oracle

Kutsatira mapazi a Highload++ Siberia 2019 - 8 ntchito pa Oracle

ΠŸΡ€ΠΈΠ²Π΅Ρ‚!

Pa June 24-25, msonkhano wa Highload++ Siberia 2019 unachitikira ku Novosibirsk. Anyamata athu analinso kumeneko. lipoti "Zosungirako zotengera za Oracle (CDB/PDB) ndi kugwiritsa ntchito kwawo pakupanga mapulogalamu", tidzasindikiza zolemba pambuyo pake. Zinali zabwino, zikomo olegbunin za bungwe, komanso kwa aliyense amene anabwera.

Kutsatira mapazi a Highload++ Siberia 2019 - 8 ntchito pa Oracle
Mu positi iyi, tikufuna kugawana nanu zovuta zomwe tinali nazo kunyumba yathu kuti mutha kuyesa chidziwitso chanu cha Oracle. Pansi pa odulidwawo pali mavuto 8, mayankho ndi mafotokozedwe.

Kodi mulingo wapamwamba kwambiri wotsatizana ndi uti womwe tiwona chifukwa chotsatira zolemba zotsatirazi?

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
  • Ayi, padzakhala cholakwika

YankhaniMalinga ndi zolemba za Oracle (zochokera ku 8.1.6):
Mkati mwa mawu amodzi a SQL, Oracle idzawonjezera kutsatizana kamodzi kokha pamzere. Ngati chiganizo chili ndi mawu ochulukirachulukira a NEXTVAL potsatira ndondomekoyi, Oracle amawonjezera mndandandawo kamodzi ndi kubweza mtengo womwewo pazochitika zonse za NEXTVAL. Ngati chiganizo chili ndi maumboni a onse CURRVAL ndi NEXTVAL, Oracle amawonjezera kutsatizana ndi kubweza mtengo womwewo pa onse CURRVAL ndi NEXTVAL mosasamala kanthu za dongosolo lawo mkati mwa mawuwo.

Motero, mtengo wapamwamba umagwirizana ndi kuchuluka kwa mizere, ndiye 5.

Ndi mizere ingati yomwe idzakhale patebulo chifukwa chotsatira zolemba zotsatirazi?

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

YankhaniMalinga ndi zolemba za Oracle (zochokera ku 11.2):

Asanapereke chiganizo chilichonse cha SQL, Oracle imayika chizindikiro chosungira (chosapezeka kwa inu). Kenako, mawuwo akakanika, Oracle amawatembenuza okha ndikubweza khodi yolakwika ku SQLCODE mu SQLCA. Mwachitsanzo, ngati mawu a INSERT ayambitsa cholakwika poyesa kuyika mtengo wobwereza mu index yapadera, mawuwo amabwereranso.

Kuyimbira HP kuchokera kwa kasitomala kumaganiziridwanso ndikusinthidwa ngati mawu amodzi. Chifukwa chake, kuyimba koyamba kwa HP kumatha bwino, kuyika zolemba zitatu; kuyimba kwachiwiri kwa HP kumatha ndi cholakwika ndikubweza mbiri yachinayi yomwe idakwanitsa kuyika; kuyitana kwachitatu kulephera, ndipo pali zolembedwa zitatu mu gome.

Ndi mizere ingati yomwe idzakhale patebulo chifukwa chotsatira zolemba zotsatirazi?

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

YankhaniMalinga ndi zolemba za Oracle (zochokera ku 11.2):

Cholepheretsa chekeni chimakupatsani mwayi wofotokozera zomwe mzere uliwonse patebulo uyenera kukwaniritsa. Kuti mukwaniritse zoletsazo, mzere uliwonse patebulo uyenera kupanga ZOONA kapena zosadziwika (chifukwa chachabechabe). Oracle akamawunika cheke pamzere wina, mayina aliwonse omwe ali mumkhalidwewo amatanthawuza zikhalidwe zomwe zili mumzerewu.

Choncho, mtengo wamtengo wapatali udzadutsa cheke, ndipo chipika chosadziwika chidzachitidwa bwino mpaka kuyesa kuyika mtengo wa 3. Pambuyo pa izi, chotchinga chogwiritsira ntchito cholakwika chidzachotsa chosiyana, palibe kubweza komwe kudzachitika, ndipo padzakhala mizere inayi patebulopo ndi mfundo 1, null, 2 ndi null kachiwiri.

Ndi mitundu iti yomwe ingatenge malo ofanana mu block?

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 ndi X
  • B ndi Y
  • C ndi K
  • C ndi Z
  • K ndi Z
  • Ine ndi J
  • J ndi X
  • Zonse zomwe zalembedwa

YankhaniNawa zotuluka mu zolembedwa (12.1.0.2) pa kusunga mitundu yosiyanasiyana ya data mu Oracle.

Mtundu wa data wa CHAR
Mtundu wa data wa CHAR umatchula zingwe zautali wokhazikika muzolemba za database. Mumatchula mawonekedwe a database pamene mukupanga database yanu. Oracle imawonetsetsa kuti zikhalidwe zonse zomwe zasungidwa pamzere wa CHAR zimakhala ndi kutalika kofotokozedwa ndi kukula mu semantics yosankhidwa. Ngati muyika mtengo womwe uli waufupi kuposa kutalika kwa gawolo, ndiye kuti Oracle imachotsa mtengo wofikira kutalika kwa gawo.

Mtundu wa data wa VARCHAR2
Mtundu wa data wa VARCHAR2 umatanthawuza chingwe chautali wosiyanasiyana muzolemba zamtundu wa database. Mumatchula mawonekedwe a database pamene mukupanga database yanu. Oracle imasunga mtengo wamtundu mugawo la VARCHAR2 ndendende momwe mumafotokozera, popanda cholembera chilichonse, malinga ngati mtengowo sudutsa kutalika kwa gawolo.

NUMBER Mtundu wa Data
Mtundu wa data wa NUMBER umasunga ziro komanso manambala okhazikika komanso olakwika okhala ndi mayendedwe kuyambira 1.0 x 10-130 mpaka koma osaphatikiza 1.0 x 10126. 1.0 x 10126, ndiye Oracle imabweretsa zolakwika. Mtengo uliwonse NUMBER umafunika kuyambira 1 mpaka 22 mabayiti. Poganizira izi, kukula kwa mzati mu ma byte pa mtengo wamtengo wapatali wa nambala NUMBER(p), pomwe p ndi kulondola kwa mtengo womwe wapatsidwa, ukhoza kuwerengedwa pogwiritsa ntchito njira iyi: ZOCHITA((kutalika(p)+s)/2))+1 pamene s akufanana ndi ziro ngati nambala ili positive, ndi 1 ngati nambala ili negative.

Kuphatikiza apo, tiyeni titenge kachigawo kakang'ono ka zolemba za kusunga ma Null values.

A null ndi kusowa kwa mtengo mumgawo. Nulls akuwonetsa zomwe zikusowa, zosadziwika, kapena zosagwiritsidwa ntchito. Nulls amasungidwa mu nkhokwe ngati agwera pakati pa mizati yokhala ndi ma data. Pazifukwa izi, amafunikira 1 byte kuti asunge kutalika kwa ndime (zero). Ma null otsatizana pamzere safuna kusungidwa chifukwa mutu watsopano wa mzere umawonetsa kuti mizere yotsala pamzere wapitawo ndi opanda pake. Mwachitsanzo, ngati mizati itatu yomaliza ya tebulo ilibe kanthu, ndiye kuti palibe deta yomwe imasungidwa pamizereyi.

Kutengera ndi datayi, timapanga malingaliro. Tikuganiza kuti database imagwiritsa ntchito encoding AL32UTF8. Mu encoding iyi, zilembo zaku Russia zizitenga ma byte awiri.

1) A ndi X, mtengo wa 'Y' umatenga 1 byte, mtengo wa x 'D' umatenga 2 mabayiti
2) B ndi Y, 'Vasya' mu b mtengo udzakhala ndi mipata mpaka zilembo 10 ndipo zidzatenga ma byte 14, 'Vasya' mu d idzatenga ma byte 8.
3) C ndi K. Minda yonseyi ili ndi mtengo wa NULL, pambuyo pawo pali minda yofunikira, choncho imakhala ndi 1 byte.
4) C ndi Z. Magawo onsewa ali ndi mtengo wa NULL, koma munda Z ndi womaliza patebulo, choncho sichitenga malo (0 bytes). Field C imakhala ndi 1 byte.
5) K ndi Z. Zofanana ndi zomwe zachitika kale. Mtengo m'munda wa K umatenga 1 byte, mu Z - 0.
6) Ine ndi J. Malinga ndi zolembedwa, zikhalidwe zonse zitenga ma byte 2. Timawerengera kutalika kwake pogwiritsa ntchito chilinganizo chomwe chatengedwa kuchokera pazolembedwa: kuzungulira ((1 + 0)/2) +1 = 1 + 1 = 2.
7) J ndi X. Mtengo m'munda wa J udzatenga ma byte 2, mtengo wa X munda udzatenga 2 byte.

Pazonse, zosankha zolondola ndi: C ndi K, I ndi J, J ndi X.

Ndi chiyani chomwe chingakhale clustering factor ya T_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);

  • Pafupifupi makumi
  • Pafupifupi mazana
  • Pafupifupi zikwi
  • Pafupifupi zikwi makumi

YankhaniMalinga ndi zolemba za Oracle (zochokera ku 12.1):

Pamlozera wa mtengo wa B, index clustering factor imayesa kusanjika kwa mizere molingana ndi mtengo wa index.

Chophatikiza cholozera chimathandizira wowongolera kuti asankhe ngati kusanthula kwa index kapena tebulo lonse ndikothandiza pamafunso ena). Kutsika kwa clustering factor kumawonetsa kusanja koyenera.

Chinthu chophatikizira chomwe chili pafupi ndi kuchuluka kwa midadada patebulo chikuwonetsa kuti mizereyo imayendetsedwa mwakuthupi pamabuloko a tebulo ndi kiyi ya index. Ngati nkhokwe ikuchita jambulani patebulo lonse, ndiye kuti databaseyo imakonda kubweza mizere momwe imasungidwa pa disk yosankhidwa ndi kiyi ya index. Chophatikiza chomwe chili pafupi ndi kuchuluka kwa mizere chikuwonetsa kuti mizereyo imamwazikana mwachisawawa pama block a database mogwirizana ndi kiyi ya index. Ngati nkhokweyo ichita sikani ya tebulo lonse, ndiye kuti malo osungirako zinthu sangatenge mizere mwadongosolo lililonse ndi kiyi ya index iyi.

Pachifukwa ichi, deta imasanjidwa bwino, kotero kuti chinthu chogwirizanitsa chidzakhala chofanana kapena pafupi ndi chiwerengero cha midadada yomwe ili patebulo. Pa kukula kwa block ya ma kilobytes 8, mutha kuyembekezera kuti pafupifupi chikwi chimodzi chocheperako chidzakwanira mu block imodzi, kotero kuchuluka kwa midadada, ndipo chifukwa chake, chinthu chophatikizika chidzakhala. pafupifupi makumi.

Ndi mfundo ziti za N zomwe malemba otsatirawa alembedwa bwino mu nkhokwe yanthawi zonse yokhala ndi makonda okhazikika?

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

YankhaniMalinga ndi zolemba za Oracle (zochokera ku 11.2):

Malire a Logical Database

katunduyo
Mtundu wa Malire
Malire Mtengo

Index
Kukula konse kwa gawo lolondolera
75% ya kukula kwa block block kuchotsera pamutu

Chifukwa chake, kukula konse kwa mizere yolondoleredwa sikuyenera kupitilira 6Kb. Zomwe zimachitika kenako zimatengera encoding yosankhidwa. Pa encoding ya AL32UTF8, munthu m'modzi amatha kukhala ndi ma byte 4, kotero muzochitika zoyipa kwambiri, zilembo pafupifupi 6 zikwanira ma kilobytes 1500. Chifukwa chake, Oracle salola kulengedwa kwa index pa N = 400 (pamene choyipa kwambiri kutalika ndi zilembo 1600 * 4 mabayiti + kutalika kwa mzere), pomwe pa N = 200 (kapena zochepa) kupanga index idzagwira ntchito popanda mavuto.

Wogwiritsa ntchito INSERT wokhala ndi lingaliro la APPEND adapangidwa kuti azilowetsa deta mwachindunji. Kodi chimachitika ndi chiyani ngati itayikidwa patebulo pomwe choyambitsacho chimapachikidwa?

  • Deta idzayikidwa mwachindunji, choyambitsacho chidzagwira ntchito monga momwe zikuyembekezeredwa
  • Deta idzayikidwa mwachindunji, koma choyambitsacho sichidzachitidwa
  • Deta idzayikidwa mumayendedwe ochiritsira, choyambitsa chidzagwira ntchito momwe chiyenera kukhalira
  • Deta idzayikidwa mumayendedwe ochiritsira, koma choyambitsa sichidzachitidwa
  • Deta sidzakwezedwa, cholakwika chidzalembedwa

YankhaniKwenikweni, ili ndi funso la logic. Kuti ndipeze yankho lolondola, ndingapangire chitsanzo chotsatirachi:

  1. Kuyika mumayendedwe olunjika kumachitidwa ndi kupanga mwachindunji kwa chipika cha data, kudutsa injini ya SQL, yomwe imatsimikizira kuthamanga kwambiri. Chifukwa chake, kuwonetsetsa kuti choyambitsacho chimakhala chovuta kwambiri, ngati sichingachitike, ndipo palibe chifukwa cha izi, chifukwa chidzachepetsa kwambiri kuyikapo.
  2. Kulephera kuchita choyambitsacho kudzatsogolera ku mfundo yakuti, ngati deta yomwe ili patebulo ili yofanana, chikhalidwe cha deta yonse (matebulo ena) chidzadalira momwe detayi inayikidwa. Izi mwachiwonekere zidzawononga kukhulupirika kwa deta ndipo sizingagwiritsidwe ntchito ngati yankho pakupanga.
  3. Kulephera kugwira ntchito yomwe mwapemphedwa kumawonedwa ngati cholakwika. Koma apa tiyenera kukumbukira kuti APPEND ndi lingaliro, ndipo malingaliro ake onse ndikuti amaganiziridwa ngati n'kotheka, koma ngati sichoncho, wogwiritsa ntchitoyo amaphedwa popanda kuganizira.

Kotero yankho loyembekezeredwa ndilo deta idzayikidwa mumayendedwe abwinobwino (SQL), choyambitsacho chidzawotcha.

Malinga ndi zolemba za Oracle (zochokera ku 8.04):

Kuphwanya zoletsa kumapangitsa kuti mawuwo agwire ntchito mosalekeza, pogwiritsa ntchito njira wamba, popanda machenjezo kapena mauthenga olakwika. Kupatulapo ndikuletsa mawu ofikira patebulo lomwelo kangapo pakugulitsa, zomwe zingayambitse mauthenga olakwika.
Mwachitsanzo, ngati zoyambitsa kapena kukhulupirika kwazomwe zilipo patebulo, ndiye kuti chidziwitso cha APPEND chidzanyalanyazidwa mukayesa kugwiritsa ntchito INSERT yachindunji (serial kapena parallel), komanso lingaliro la PARALLEL kapena ndime, ngati ilipo.

Kodi chidzachitika ndi chiyani script yotsatira ikaperekedwa?

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

  • Kumaliza bwino
  • Kulephera chifukwa cha zolakwika za mawu
  • Cholakwika: Zochita Pawokha Sizovomerezeka
  • Cholakwika chokhudzana ndi kupitilira muyeso wokulirapo
  • Vuto Lophwanya Makiyi Akunja
  • Zolakwika zokhudzana ndi maloko

YankhaniGome ndi choyambitsa amapangidwa molondola ndithu ndipo ntchito imeneyi siyenera kubweretsa mavuto. Zochita zodziyimira pawokha pakuyambitsanso zimaloledwa, apo ayi kudula mitengo sikungatheke, mwachitsanzo.

Pambuyo polowetsa mzere woyamba, kuwombera koyambitsa bwino kungachititse kuti mzere wachiwiri ulowetsedwe, kuchititsa kuti chowomberacho chiwombenso, kulowetsa mzere wachitatu, ndi zina zotero mpaka mawuwo analephera chifukwa cha kupyola chisa chachikulu cha kuyitana. Komabe, pali mfundo ina yosaoneka bwino. Pa nthawi yomwe choyambitsacho chikuchitidwa, kudzipereka sikunamalizidwe pa mbiri yoyamba yoyikidwa. Chifukwa chake, choyambitsa chomwe chimagwira ntchito yodziyimira payokha chimayesa kuyika mutebulo mzere womwe umalozera makiyi akunja ku mbiri yomwe sinapangidwebe. Izi zimabweretsa kudikirira (ntchito yodziyimira payokha imadikirira kuti ntchito yayikulu iperekedwe kuti ione ngati ingalowetse deta) ndipo nthawi yomweyo ntchito yayikulu ikudikirira kuti ntchito yodziyimira payokha ipitilize kugwira ntchito pambuyo poyambitsa. Kusakhazikika kumachitika ndipo, chifukwa chake, ntchito yodziyimira payokha imathetsedwa chifukwa chazifukwa zokhudzana ndi maloko.

Ogwiritsa ntchito olembetsedwa okha ndi omwe angatenge nawo gawo pa kafukufukuyu. Lowani muakauntichonde.

Zinali zovuta kuti?

  • Monga zala ziwiri, nthawi yomweyo ndinaganiza zonse molondola.

  • Osati kwenikweni, ndinalakwitsa pa mafunso angapo.

  • Ndinathetsa theka lake molondola.

  • Ndinalingalira yankho kawiri!

  • Ndilemba mu ndemanga

Ogwiritsa 14 adavota. Ogwiritsa ntchito 10 adakana.

Source: www.habr.com

Kuwonjezera ndemanga