{"id":36171,"date":"2019-10-31T22:09:58","date_gmt":"2019-10-31T19:09:58","guid":{"rendered":"https:\/\/prohoster.info\/blog\/po-sledam-highload-siberia-2019-8-zadach-po-oracle\/"},"modified":"2019-10-31T22:09:58","modified_gmt":"2019-10-31T19:09:58","slug":"po-sledam-highload-siberia-2019-8-zadach-po-oracle","status":"publish","type":"post","link":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/po-sledam-highload-siberia-2019-8-zadach-po-oracle","title":{"rendered":"Na stop\u011b Highload++ Siberia 2019 \u2014 8 \u00fakol\u016f v Oracle.","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>Ahoj!<\/p>\n<p>Ve dnech 24-25 \u010dervna se v Novosibirsku konala konference Highload++ Siberia 2019. Na\u0161i kluci tam tak\u00e9 byli. <noindex><a rel=\"nofollow\" href=\"https:\/\/www.highload.ru\/siberia\/2019\/abstracts\/5211\">p\u0159edn\u00e1\u0161kou<\/a><\/noindex> T\u00e9ma \"Oracle Container Databases (CDB\/PDB) a jejich praktick\u00e9 vyu\u017eit\u00ed pro v\u00fdvoj softwaru\", textovou verzi zve\u0159ejn\u00edme trochu pozd\u011bji. Bylo to skv\u011bl\u00e9, d\u011bkujeme. <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/users\/olegbunin\/\" class=\"user_link\">olegbunin<\/a><\/noindex> za organizaci, a tak\u00e9 v\u0161em, kdo p\u0159i\u0161li. <\/p>\n<p><img decoding=\"async\" alt=\"Na stop\u011b Highload++ Siberia 2019 \u2014 8 \u00fakol\u016f v Oracle.\" src=\"\/wp-content\/uploads\/2019\/07\/27f4707d4bb5968eaaa5c4748ff467cd.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nV tomto p\u0159\u00edsp\u011bvku bychom se s v\u00e1mi cht\u011bli pod\u011blit o \u00fakoly, kter\u00e9 byly na na\u0161em st\u00e1nku, abyste si mohli otestovat sv\u00e9 znalosti v Oracle. Pod katem \u2013 8 \u00fakol\u016f, varianty odpov\u011bd\u00ed a vysv\u011btlen\u00ed.<br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h3>Jak\u00e1 je maxim\u00e1ln\u00ed hodnota sekvence, kterou uvid\u00edme jako v\u00fdsledek proveden\u00ed n\u00e1sleduj\u00edc\u00edho skriptu?<\/h3>\n<p><\/p>\n<pre><code class=\"sql\">create sequence s start with 1;\n \nselect s.currval, s.nextval, s.currval, s.nextval, s.currval\nfrom dual\nconnect by level &lt;= 5;\n<\/code><\/pre>\n<p><\/p>\n<ul>\n<li>1<\/li>\n<li>5<\/li>\n<li>10<\/li>\n<li>25<\/li>\n<li>\u017d\u00e1dn\u00e1, dojde k chyb\u011b.<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">Odpov\u011b\u010f<\/b>Podle dokumentace Oracle (citovan\u00e9 z 8.1.6):<br \/>\nV r\u00e1mci jednoho SQL p\u0159\u00edkazu Oracle inkrementuje sekvenci pouze jednou na \u0159\u00e1dek. Pokud p\u0159\u00edkaz obsahuje v\u00edce odkaz\u016f na NEXTVAL pro sekvenci, Oracle sekvenci inkrementuje jednou a vr\u00e1t\u00ed stejnou hodnotu pro v\u0161echny v\u00fdskyty NEXTVAL. Pokud p\u0159\u00edkaz obsahuje odkazy jak na CURRVAL, tak na NEXTVAL, Oracle sekvenci inkrementuje a vr\u00e1t\u00ed stejnou hodnotu pro CURRVAL i NEXTVAL bez ohledu na jejich po\u0159ad\u00ed v p\u0159\u00edkazu.<\/p>\n<p>Tedy, <b>maxim\u00e1ln\u00ed hodnota odpov\u00edd\u00e1 po\u010dtu \u0159\u00e1dk\u016f, tedy 5.<\/b>.<\/p>\n<h3>Kolik \u0159\u00e1dk\u016f bude v tabulce jako v\u00fdsledek proveden\u00ed n\u00e1sleduj\u00edc\u00edho skriptu?<\/h3>\n<p><\/p>\n<pre><code class=\"sql\">create table t(i integer check (i &lt; 5));\n \ncreate procedure p(p_from integer, p_to integer) as\nbegin\n    for i in p_from .. p_to loop\n        insert into t values (i);\n    end loop;\nend;\n\/\n \nexec p(1, 3);\nexec p(4, 6);\nexec p(7, 9);<\/code><\/pre>\n<p><\/p>\n<ul>\n<li>0<\/li>\n<li>3<\/li>\n<li>4<\/li>\n<li>5<\/li>\n<li>6<\/li>\n<li>9<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">Odpov\u011b\u010f<\/b>Podle dokumentace Oracle (citovan\u00e9 z 11.2):<\/p>\n<p>P\u0159ed proveden\u00edm jak\u00e9hokoli SQL p\u0159\u00edkazu ozna\u010duje Oracle implicitn\u00ed bod ulo\u017een\u00ed (kter\u00fd nen\u00ed k dispozici pro v\u00e1s). Pokud p\u0159\u00edkaz sel\u017ee, Oracle jej automaticky vr\u00e1t\u00ed zp\u011bt a vr\u00e1t\u00ed p\u0159\u00edslu\u0161n\u00fd chybov\u00fd k\u00f3d do SQLCODE v SQLCA. Nap\u0159\u00edklad, pokud p\u0159\u00edkaz INSERT zp\u016fsob\u00ed chybu t\u00edm, \u017ee se pokus\u00ed vlo\u017eit duplicitn\u00ed hodnotu do unik\u00e1tn\u00edho indexu, p\u0159\u00edkaz je vr\u00e1cen zp\u011bt.<\/p>\n<p>Vol\u00e1n\u00ed stored procedury z klienta je tak\u00e9 pova\u017eov\u00e1no a zpracov\u00e1v\u00e1no jako jedin\u00e9 prohl\u00e1\u0161en\u00ed. Tedy prvn\u00ed vol\u00e1n\u00ed stored procedury \u00fasp\u011b\u0161n\u011b kon\u010d\u00ed vlo\u017een\u00edm t\u0159\u00ed z\u00e1znam\u016f; druh\u00e9 vol\u00e1n\u00ed stored procedury kon\u010d\u00ed chybou a vrac\u00ed \u010dtvrt\u00fd z\u00e1znam zp\u011bt, kter\u00fd se poda\u0159ilo vlo\u017eit; t\u0159et\u00ed vol\u00e1n\u00ed kon\u010d\u00ed chybou, <b>a v tabulce z\u016fst\u00e1vaj\u00ed t\u0159i z\u00e1znamy.<\/b>.<\/p>\n<h3>Kolik \u0159\u00e1dk\u016f bude v tabulce jako v\u00fdsledek proveden\u00ed n\u00e1sleduj\u00edc\u00edho skriptu?<\/h3>\n<p><\/p>\n<pre><code class=\"sql\">create table t(i integer, constraint i_ch check (i &lt; 3));\n \nbegin\n    insert into t values (1);\n    insert into t values (null);\n    insert into t values (2);\n    insert into t values (null);\n    insert into t values (3);\n    insert into t values (null);\n    insert into t values (4);\n    insert into t values (null);\n    insert into t values (5);\nexception\n    when others then\n        dbms_output.put_line(&#039;Oops!&#039;);\nend;\n\/<\/code><\/pre>\n<p><\/p>\n<ul>\n<li>1<\/li>\n<li>2<\/li>\n<li>3<\/li>\n<li>4<\/li>\n<li>5<\/li>\n<li>6<\/li>\n<li>7<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">Odpov\u011b\u010f<\/b>Podle dokumentace Oracle (citovan\u00e9 z 11.2):<\/p>\n<p>Omezen\u00ed typu check umo\u017e\u0148uje specifikovat podm\u00ednku, kterou mus\u00ed ka\u017ed \u0159\u00e1dek v tabulce spl\u0148ovat. Aby byla podm\u00ednka spln\u011bna, mus\u00ed ka\u017ed\u00fd \u0159\u00e1dek v tabulce u\u010dinit podm\u00ednku bu\u010f PRAVDIVOU, nebo nezn\u00e1mou (kv\u016fli null). Kdy\u017e Oracle vyhodnocuje podm\u00ednku omezen\u00ed typu check pro konkr\u00e9tn\u00ed \u0159\u00e1dek, jak\u00e9koli n\u00e1zvy sloupc\u016f v podm\u00ednce odkazuj\u00ed na hodnoty sloupc\u016f v tomto \u0159\u00e1dku.<\/p>\n<p>Tak\u017ee hodnota null projde kontrolou a anonymn\u00ed blok se \u00fasp\u011b\u0161n\u011b vykon\u00e1 a\u017e do pokusu vlo\u017eit hodnotu 3. Po tomto bloku zpracov\u00e1n\u00ed chyb vyzvedne v\u00fdjimku, k rollbacku nedojde a <b>v tabulce z\u016fstanou \u010dty\u0159i \u0159\u00e1dky<\/b> s hodnotami 1, null, 2 a znovu null.<\/p>\n<h3>Jak\u00e9 dvojice hodnot zab\u00edraj\u00ed stejn\u00fd objem m\u00edsta v bloku?<\/h3>\n<p><\/p>\n<pre><code class=\"sql\">create table t (\n    a char(1 char),\n    b char(10 char),\n    c char(100 char),\n    i number(4),\n    j number(14),\n    k number(24),\n    x varchar2(1 char),\n    y varchar2(10 char),\n    z varchar2(100 char));\n \ninsert into t (a, b, i, j, x, y)\n    values ('Y', 'V\u00e1clav', 10, 10, 'D', 'V\u00e1clav');\n<\/code><\/pre>\n<p><\/p>\n<ul>\n<li>A a X<\/li>\n<li>B a Y<\/li>\n<li>C a K<\/li>\n<li>C a Z<\/li>\n<li>K a Z<\/li>\n<li>I a J<\/li>\n<li>J a X<\/li>\n<li>V\u0161echny uveden\u00e9<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">Odpov\u011b\u010f<\/b>Uv\u00e1d\u00edme \u00faryvky z dokumentace (12.1.0.2) o ukl\u00e1d\u00e1n\u00ed r\u016fzn\u00fdch typ\u016f dat v Oracle.<\/p>\n<p><b>CHAR Datov\u00fd typ<\/b><br \/>\nDatov\u00fd typ CHAR specifikuje \u0159et\u011bzec znak\u016f pevn\u00e9 d\u00e9lky v znakov\u00e9 sad\u011b datab\u00e1ze. Znakovou sadu datab\u00e1ze specifikujete p\u0159i vytv\u00e1\u0159en\u00ed datab\u00e1ze. Oracle zaji\u0161\u0165uje, \u017ee v\u0161echny hodnoty ulo\u017een\u00e9 v sloupci CHAR maj\u00ed d\u00e9lku ur\u010denou velikost\u00ed v zvolen\u00fdch pravidlech d\u00e9lky. Pokud vlo\u017e\u00edte hodnotu, kter\u00e1 je krat\u0161\u00ed ne\u017e d\u00e9lka sloupce, Oracle tuto hodnotu dopln\u00ed pr\u00e1zdn\u00fdmi znaky na d\u00e9lku sloupce.<\/p>\n<p><b>VARCHAR2 Datov\u00fd typ<\/b><br \/>\nDatov\u00fd typ VARCHAR2 specifikuje \u0159et\u011bzec znak\u016f prom\u011bnn\u00e9 d\u00e9lky v znakov\u00e9 sad\u011b datab\u00e1ze. Znakovou sadu datab\u00e1ze specifikujete p\u0159i vytv\u00e1\u0159en\u00ed datab\u00e1ze. Oracle ukl\u00e1d\u00e1 znakovou hodnotu v sloupci VARCHAR2 p\u0159esn\u011b tak, jak ji specifikujete, bez jak\u00e9hokoli dopl\u0148ov\u00e1n\u00ed pr\u00e1zdn\u00fdmi znaky, pokud hodnota nep\u0159es\u00e1hne d\u00e9lku sloupce.<\/p>\n<p><b>NUMBER Datov\u00fd typ<\/b><br \/>\nDatov\u00fd typ NUMBER ukl\u00e1d\u00e1 nulu, stejn\u011b jako kladn\u00e1 a z\u00e1porn\u00e1 \u010d\u00edsla s absolutn\u00edmi hodnotami od 1.0 x 10-130 do, ale nikoli v\u010detn\u011b, 1.0 x 10^126. Pokud specifikujete aritmetick\u00fd v\u00fdraz, jeho\u017e hodnota m\u00e1 absolutn\u00ed hodnotu v\u011bt\u0161\u00ed nebo rovnu 1.0 x 10^126, Oracle vr\u00e1t\u00ed chybu. Ka\u017ed\u00e1 hodnota NUMBER vy\u017eaduje od 1 do 22 byt\u016f. Vzhledem k tomu lze velikost sloupce v bytech pro konkr\u00e9tn\u00ed \u010d\u00edselnou hodnotu NUMBER(p), kde p je p\u0159esnost dan\u00e9 hodnoty, vypo\u010d\u00edtat pomoc\u00ed n\u00e1sleduj\u00edc\u00edho vzorce: <i>ROUND((length(p)+s)\/2))+1<\/i> kde s je nula, pokud je \u010d\u00edslo kladn\u00e9, a s je 1, pokud je \u010d\u00edslo z\u00e1porn\u00e9.<\/p>\n<p>D\u00e1le uvedeme \u00faryvek z dokumentace o ukl\u00e1d\u00e1n\u00ed hodnot Null.<\/p>\n<p>Null je absence hodnoty ve sloupci. Nully ozna\u010duj\u00ed chyb\u011bj\u00edc\u00ed, nezn\u00e1m\u00e1 nebo nevhodn\u00e1 data. Nully jsou ukl\u00e1d\u00e1ny v datab\u00e1zi, pokud spadaj\u00ed mezi sloupce s hodnotami dat. V t\u011bchto p\u0159\u00edpadech vy\u017eaduj\u00ed 1 byte pro ulo\u017een\u00ed d\u00e9lky sloupce (nula). Koncov\u00e9 nully v \u0159\u00e1dku nevy\u017eaduj\u00ed \u00falo\u017ei\u0161t\u011b, proto\u017ee nov\u00fd z\u00e1hlav\u00ed \u0159\u00e1dku signalizuje, \u017ee zb\u00fdvaj\u00edc\u00ed sloupce v p\u0159edchoz\u00edm \u0159\u00e1dku jsou null. Nap\u0159\u00edklad, pokud jsou posledn\u00ed t\u0159i sloupce tabulky null, pak pro tyto sloupce nejsou \u017e\u00e1dn\u00e1 data ulo\u017eena.<\/p>\n<p>Na z\u00e1klad\u011b t\u011bchto \u00fadaj\u016f formulujeme \u00favahu. P\u0159edpokl\u00e1d\u00e1me, \u017ee v BD se pou\u017e\u00edv\u00e1 k\u00f3dov\u00e1n\u00ed AL32UTF8. V tomto k\u00f3dov\u00e1n\u00ed budou rusk\u00e9 znaky zab\u00edrat 2 bajty.<\/p>\n<p>1) A a X, hodnota pole a 'Y' zab\u00edr\u00e1 1 bajt, hodnota pole x '\u0414' \u2013 2 bajty<br \/>\n2) B a Y, 'V\u00e1clav' v b bude dopln\u011bn mezerami do 10 znak\u016f a zabere 14 bajt\u016f, 'V\u00e1clav' v d \u2013 zabere 8 bajt\u016f.<br \/>\n3) C a K. Ob\u011b pole maj\u00ed hodnotu NULL, za nimi jsou smyslupln\u00e1 pole, proto zab\u00edraj\u00ed po 1 bajtu.<br \/>\n4) C a Z. Ob\u011b pole maj\u00ed hodnotu NULL, ale pole Z je posledn\u00ed v tabulce, proto m\u00edsto nezab\u00edr\u00e1 (0 bajt\u016f). Pole C zab\u00edr\u00e1 1 bajt.<br \/>\n5) K a Z. Podobn\u011b jako v p\u0159edchoz\u00edm p\u0159\u00edpad\u011b. Hodnota v poli K zab\u00edr\u00e1 1 bajt, v Z \u2013 0.<br \/>\n6) I a J. Podle dokumentace ob\u011b hodnoty zab\u00edraj\u00ed po 2 bajtech. D\u00e9lku po\u010d\u00edt\u00e1me podle vzorce z dokumentace: round((1 + 0)\/2) + 1 = 1 + 1 = 2.<br \/>\n7) J a X. Hodnota v poli J zabere 2 bajty, hodnota v poli X tak\u00e9 zabere 2 bajty.<\/p>\n<p><b>Celkov\u011b spr\u00e1vn\u00e9 varianty: C a K, I a J, J a X.<\/b><\/p>\n<p><\/p>\n<h3>Jak\u00fd bude p\u0159ibli\u017en\u011b clustering factor u indexu T_I?<\/h3>\n<p><\/p>\n<pre><code class=\"sql\">create table t (i integer);\n \ninsert into t select rownum from dual connect by level &lt;= 10000;\n \ncreate index t_i on t(i);\n<\/code><\/pre>\n<p><\/p>\n<ul>\n<li>Po\u010det des\u00edtek<\/li>\n<li>Po\u010det stovek<\/li>\n<li>Po\u010det tis\u00edc<\/li>\n<li>Po\u010det des\u00edtek tis\u00edc<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">Odpov\u011b\u010f<\/b>Podle dokumentace Oracle (cituje se z 12.1):<\/p>\n<p>Pro B-stromov\u00fd index m\u011b\u0159\u00ed clustering factor fyzick\u00e9 seskupen\u00ed \u0159\u00e1dk\u016f vzhledem k hodnot\u011b indexu.<\/p>\n<p>Clustering factor pom\u00e1h\u00e1 optimaliz\u00e1toru rozhodnout, zda je efektivn\u011bj\u0161\u00ed indexov\u00e9 skenov\u00e1n\u00ed nebo \u00fapln\u00e9 skenov\u00e1n\u00ed tabulky pro ur\u010dit\u00e9 dotazy. N\u00edzk\u00fd clustering factor nazna\u010duje efektivn\u00ed indexov\u00e9 skenov\u00e1n\u00ed.<\/p>\n<p>Clustering factor, kter\u00fd je bl\u00edzk\u00fd po\u010dtu blok\u016f v tabulce, nazna\u010duje, \u017ee \u0159\u00e1dky jsou fyzicky se\u0159azeny v blok\u00e1ch tabulky podle kl\u00ed\u010de indexu. Pokud datab\u00e1ze prov\u00e1d\u00ed \u00fapln\u00e9 skenov\u00e1n\u00ed tabulky, pak m\u00e1 tendenci z\u00edsk\u00e1vat \u0159\u00e1dky tak, jak jsou na disku ulo\u017eeny se\u0159azen\u00e9 podle kl\u00ed\u010de indexu. Clustering factor, kter\u00fd je bl\u00edzk\u00fd po\u010dtu \u0159\u00e1dk\u016f, nazna\u010duje, \u017ee \u0159\u00e1dky jsou n\u00e1hodn\u011b rozpt\u00fdleny nap\u0159\u00ed\u010d bloky datab\u00e1ze vzhledem k kl\u00ed\u010di indexu. Pokud datab\u00e1ze prov\u00e1d\u00ed \u00fapln\u00e9 skenov\u00e1n\u00ed tabulky, pak by nevytahovala \u0159\u00e1dky v \u017e\u00e1dn\u00e9m se\u0159azen\u00e9m po\u0159ad\u00ed podle tohoto kl\u00ed\u010de indexu.<\/p>\n<p>V tomto p\u0159\u00edpad\u011b jsou data ide\u00e1ln\u011b se\u0159azena, tak\u017ee clustering factor bude roven nebo bl\u00edzk\u00fd po\u010dtu obsazen\u00fdch blok\u016f v tabulce. P\u0159i standardn\u00ed velikosti bloku 8 kilobajt\u016f lze o\u010dek\u00e1vat, \u017ee do jednoho bloku se vejde p\u0159ibli\u017en\u011b tis\u00edc \u00fazk\u00fdch \u010d\u00edseln\u00fdch hodnot, proto po\u010det blok\u016f a t\u00edm i clustering factor bude <b>po\u010det des\u00edtek<\/b>.<\/p>\n<h3>P\u0159i jak\u00fdch hodnot\u00e1ch N bude n\u00e1sleduj\u00edc\u00ed skript \u00fasp\u011b\u0161n\u011b proveden ve standardn\u00ed datab\u00e1zi s b\u011b\u017en\u00fdm nastaven\u00edm?<\/h3>\n<p><\/p>\n<pre><code class=\"sql\">create table t (\n    a varchar2(N char),\n    b varchar2(N char),\n    c varchar2(N char),\n    d varchar2(N char));\n \ncreate index t_i on t (a, b, c, d);\n<\/code><\/pre>\n<p><\/p>\n<ul>\n<li>100<\/li>\n<li>200<\/li>\n<li>400<\/li>\n<li>800<\/li>\n<li>1600<\/li>\n<li>3200<\/li>\n<li>6400<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">Odpov\u011b\u010f<\/b>Podle dokumentace Oracle (citovan\u00e9 z 11.2):<\/p>\n<p>Logick\u00e9 limity datab\u00e1ze<\/p>\n<p><strong>Polo\u017eka<\/strong><br \/>\n<strong>Typ limitu<\/strong><br \/>\n<strong>Hodnota limitu<\/strong><\/p>\n<p>Indexy<br \/>\nCelkov\u00e1 velikost indexovan\u00e9ho sloupce<br \/>\n75 % velikosti datab\u00e1zov\u00e9ho bloku minus n\u011bjak\u00e9 re\u017eijn\u00ed n\u00e1klady<\/p>\n<p>\nCelkov\u00e1 velikost indexovan\u00fdch sloupc\u016f by nem\u011bla p\u0159ekro\u010dit 6 KB. Dal\u0161\u00ed z\u00e1vis\u00ed na vybran\u00e9m k\u00f3dov\u00e1n\u00ed datab\u00e1ze. Pro k\u00f3dov\u00e1n\u00ed AL32UTF8 m\u016f\u017ee jeden znak zab\u00edrat maxim\u00e1ln\u011b 4 bajty, tak\u017ee do 6 kilobajt\u016f se v nejhor\u0161\u00edm p\u0159\u00edpad\u011b vejde p\u0159ibli\u017en\u011b 1500 znak\u016f. Oracle proto zak\u00e1\u017ee vytvo\u0159en\u00ed indexu p\u0159i N = 400 (kdy d\u00e9lka kl\u00ed\u010de v nejhor\u0161\u00edm p\u0159\u00edpad\u011b bude 1600 znak\u016f * 4 bajty + d\u00e9lka rowid), zat\u00edmco <b>p\u0159i N = 200 (a m\u00e9n\u011b)<\/b> vytvo\u0159en\u00ed indexu prob\u011bhne bez probl\u00e9m\u016f.<\/p>\n<h3>Oper\u00e1tor INSERT s hintonem APPEND je ur\u010den k na\u010d\u00edt\u00e1n\u00ed dat v p\u0159\u00edm\u00e9m re\u017eimu. Co se stane, pokud bude pou\u017eit na tabulce, na kter\u00e9 je trigger?<\/h3>\n<p><\/p>\n<ul>\n<li>Data budou na\u010dtena v p\u0159\u00edm\u00e9m re\u017eimu, trigger se aktivuje, jak m\u00e1.<\/li>\n<li>Data budou na\u010dtena v p\u0159\u00edm\u00e9m re\u017eimu, ale trigger se neprovede.<\/li>\n<li>Data budou na\u010dtena v konven\u010dn\u00edm re\u017eimu, trigger se aktivuje, jak m\u00e1.<\/li>\n<li>Data budou na\u010dtena v konven\u010dn\u00edm re\u017eimu, ale trigger se neprovede.<\/li>\n<li>Data nebudou na\u010dtena, dojde k chyb\u011b.<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">Odpov\u011b\u010f<\/b>V podstat\u011b je to ot\u00e1zka v\u00edce logiky. Pro nalezen\u00ed spr\u00e1vn\u00e9 odpov\u011bdi bych navrhl n\u00e1sleduj\u00edc\u00ed model uva\u017eov\u00e1n\u00ed:<\/p>\n<ol>\n<li>Vlo\u017een\u00ed v p\u0159\u00edm\u00e9m re\u017eimu se prov\u00e1d\u00ed p\u0159\u00edm\u00fdm vytv\u00e1\u0159en\u00edm bloku dat, mimo SQL engine, co\u017e zaji\u0161\u0165uje vysokou rychlost. T\u00edm p\u00e1dem je zaji\u0161t\u011bn\u00ed proveden\u00ed triggeru velmi obt\u00ed\u017en\u00e9, pokud v\u016fbec mo\u017en\u00e9, a nem\u00e1 to smysl, proto\u017ee by to stejn\u011b z\u00e1sadn\u011b zpomalilo vlo\u017een\u00ed.<\/li>\n<li>Nepou\u017eit\u00ed triggeru povede k tomu, \u017ee p\u0159i stejn\u00fdch datech v tabulce bude celkov\u00fd stav datab\u00e1ze (dal\u0161\u00edch tabulek) z\u00e1visl\u00fd na tom, v jak\u00e9m re\u017eimu byla tato data vlo\u017eena. To zjevn\u011b naru\u0161\u00ed integritu dat a nem\u016f\u017ee b\u00fdt aplikov\u00e1no jako \u0159e\u0161en\u00ed v produk\u010dn\u00edm prost\u0159ed\u00ed.<\/li>\n<li>Nemo\u017enost prov\u00e9st po\u017eadovanou operaci se obecn\u011b ch\u00e1pe jako chyba. Ale zde je t\u0159eba si p\u0159ipomenout, \u017ee APPEND je hint a obecn\u00e1 logika hint\u016f spo\u010d\u00edv\u00e1 v tom, \u017ee se zohled\u0148uj\u00ed, pokud je to mo\u017en\u00e9, pokud ne, oper\u00e1tor se prov\u00e1d\u00ed bez ohledu na hint.<\/li>\n<\/ol>\n<p>\nTak\u017ee o\u010dek\u00e1van\u00e1 odpov\u011b\u010f je: <b>data budou na\u010dtena v b\u011b\u017en\u00e9m (SQL) re\u017eimu, trigger se aktivuje.<\/b><\/p>\n<p>Podle dokumentace Oracle (citov\u00e1no z 8.04):<\/p>\n<p>Poru\u0161en\u00ed omezen\u00ed zp\u016fsob\u00ed, \u017ee se p\u0159\u00edkaz provede s\u00e9riov\u011b, pomoc\u00ed konven\u010dn\u00edho vkl\u00e1dac\u00edho postupu, bez varov\u00e1n\u00ed nebo chybov\u00fdch zpr\u00e1v. V\u00fdjimkou je omezen\u00ed na p\u0159\u00edkazy p\u0159istupuj\u00edc\u00ed k t\u00e9 stejn\u00e9 tabulce v\u00edce ne\u017e jednou v transakci, co\u017e m\u016f\u017ee zp\u016fsobit chybov\u00e9 zpr\u00e1vy.<br \/>\nNap\u0159\u00edklad pokud se na tabulce nach\u00e1zej\u00ed spou\u0161t\u011b nebo referen\u010dn\u00ed integrita, bude n\u00e1znak APPEND ignorov\u00e1n, kdy\u017e se pokus\u00edte pou\u017e\u00edt p\u0159\u00edm\u00e9 na\u010d\u00edt\u00e1n\u00ed INSERT (seri\u00e1ln\u011b nebo paraleln\u011b), stejn\u011b jako jak\u00fdkoli n\u00e1znak nebo klauzule PARALLEL.<\/p>\n<h3>Co se stane p\u0159i proveden\u00ed n\u00e1sleduj\u00edc\u00edho skriptu?<\/h3>\n<p><\/p>\n<pre><code class=\"sql\">create table t(i integer not null primary key, j integer references t);\n \ncreate trigger t_a_i after insert on t for each row\ndeclare\n    pragma autonomous_transaction;\nbegin\n    insert into t values (:new.i + 1, :new.i);\n    commit;\nend;\n\/\n \ninsert into t values (1, null);\n<\/code><\/pre>\n<p><\/p>\n<ul>\n<li>\u00dasp\u011b\u0161n\u00e9 proveden\u00ed<\/li>\n<li>Selh\u00e1n\u00ed kv\u016fli syntaktick\u00e9 chyb\u011b<\/li>\n<li>Chyba souvisej\u00edc\u00ed s neplatnost\u00ed autonomn\u00ed transakce<\/li>\n<li>Chyba souvisej\u00edc\u00ed s p\u0159ekro\u010den\u00edm maxim\u00e1ln\u00ed hloubky vol\u00e1n\u00ed<\/li>\n<li>Chyba souvisej\u00edc\u00ed s poru\u0161en\u00edm ciz\u00edho kl\u00ed\u010de<\/li>\n<li>Chyba souvisej\u00edc\u00ed se z\u00e1mky<\/li>\n<\/ul>\n<p>\n<b class=\"spoiler_title\">Odpov\u011b\u010f<\/b>Tabulka a spou\u0161t\u011b\u010d jsou vytvo\u0159eny zcela spr\u00e1vn\u011b a tato operace by nem\u011bla zp\u016fsobit probl\u00e9my. Autonomn\u00ed transakce ve spou\u0161t\u011b\u010di jsou tak\u00e9 povoleny, jinak by nap\u0159\u00edklad nebylo mo\u017en\u00e9 logov\u00e1n\u00ed.<\/p>\n<p>Po vlo\u017een\u00ed prvn\u00edho \u0159\u00e1dku \u00fasp\u011b\u0161n\u00e9 spu\u0161t\u011bn\u00ed spou\u0161t\u011b\u010de by vedlo k vlo\u017een\u00ed druh\u00e9ho \u0159\u00e1dku, co\u017e by znovu vyvolalo spou\u0161t\u011b\u010d, vlo\u017eilo by se t\u0159et\u00ed \u0159\u00e1dek a tak d\u00e1le, dokud by prohl\u00e1\u0161en\u00ed nepadlo kv\u016fli p\u0159ekro\u010den\u00ed maxim\u00e1ln\u00ed hloubky vol\u00e1n\u00ed. Nicm\u00e9n\u011b, nast\u00e1v\u00e1 je\u0161t\u011b jeden jemn\u00fd moment. V okam\u017eiku, kdy se spou\u0161t\u011b\u010d vykon\u00e1v\u00e1, pro prvn\u00ed vlo\u017een\u00fd z\u00e1znam je\u0161t\u011b nebyl proveden commit. Proto se spou\u0161t\u011b\u010d, pracuj\u00edc\u00ed v autonomn\u00ed transakci, sna\u017e\u00ed vlo\u017eit do tabulky \u0159\u00e1dek, kter\u00fd se odkazuje ciz\u00edm kl\u00ed\u010dem na je\u0161t\u011b nezakompitovan\u00fd z\u00e1znam. To vede k \u010dek\u00e1n\u00ed (autonomn\u00ed transakce \u010dek\u00e1 na commit hlavn\u00ed, aby pochopila, zda m\u016f\u017ee vlo\u017eit data) a sou\u010dasn\u011b \u010dek\u00e1 hlavn\u00ed transakce na commit autonomn\u00ed, aby mohla pokra\u010dovat po spou\u0161t\u011b\u010di. <b>Nast\u00e1v\u00e1 deadlock a n\u00e1sledn\u011b \u2013 autonomn\u00ed transakce je p\u0159eru\u0161ena z d\u016fvodu souvisej\u00edc\u00edho se z\u00e1mky.<\/b>.<\/p>\n<p class=\"for_users_only_msg\">Pouze registrovan\u00ed u\u017eivatel\u00e9 se mohou \u00fa\u010dastnit ankety. <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/auth\/login\/\">P\u0159ihlaste se<\/a><\/noindex>, pros\u00edm.<\/p>\n<h2 class=\"default-block__polling-title\">Bylo to t\u011b\u017ek\u00e9?<\/h2>\n<ul class=\"content-list content-list_polling\">\n<li class=\"content-list__item content-list__item_polling\">\n<p>                    Jako dva prsty, hned jsem to v\u0161echno vy\u0159e\u0161il spr\u00e1vn\u011b.<\/p>\n<\/li>\n<li class=\"content-list__item content-list__item_polling\">\n<p>                    Nijak zvl\u00e1\u0161\u0165, ud\u011blal jsem chybu v p\u00e1r ot\u00e1zk\u00e1ch.<\/p>\n<\/li>\n<li class=\"content-list__item content-list__item_polling\">\n<p>                    Vy\u0159e\u0161il jsem polovinu spr\u00e1vn\u011b.<\/p>\n<\/li>\n<li class=\"content-list__item content-list__item_polling\">\n<p>                    Dvakr\u00e1t jsem uhodl odpov\u011b\u010f!<\/p>\n<\/li>\n<li class=\"content-list__item content-list__item_polling\">\n<p>                    Nap\u00ed\u0161u v koment\u00e1\u0159\u00edch<\/p>\n<\/li>\n<\/ul>\n<p>    Hlasovalo 14 u\u017eivatel\u016f. Zdr\u017eelo se 10 u\u017eivatel\u016f.<br \/>\n<br \/>Zdroj: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/sportmaster_lab\/blog\/459680\/\">habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041f\u0440\u0438\u0432\u0435\u0442! 24-25 \u0438\u044e\u043d\u044f \u0432 \u041d\u043e\u0432\u043e\u0441\u0438\u0431\u0438\u0440\u0441\u043a\u0435 \u043f\u0440\u043e\u0448\u043b\u0430 \u043a\u043e\u043d\u0444\u0435\u0440\u0435\u043d\u0446\u0438\u044f Highload++ Siberia 2019. \u041d\u0430\u0448\u0438 \u0440\u0435\u0431\u044f\u0442\u0430 \u0442\u043e\u0436\u0435 \u0442\u0430\u043c \u0431\u044b\u043b\u0438 \u0434\u043e\u043a\u043b\u0430\u0434\u043e\u043c \u00ab\u041a\u043e\u043d\u0442\u0435\u0439\u043d\u0435\u0440\u043d\u044b\u0435 \u0431\u0430\u0437\u044b Oracle (CDB\/PDB) \u0438 \u0438\u0445 \u043f\u0440\u0430\u043a\u0442\u0438\u0447\u0435\u0441\u043a\u043e\u0435 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0435 \u0434\u043b\u044f \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0438 \u041f\u041e\u00bb, \u043c\u044b \u0432\u044b\u043b\u043e\u0436\u0438\u043c \u0442\u0435\u043a\u0441\u0442\u043e\u0432\u0443\u044e \u0432\u0435\u0440\u0441\u0438\u044e \u043d\u0435\u043c\u043d\u043e\u0433\u043e \u043f\u043e\u0437\u0436\u0435. \u0411\u044b\u043b\u043e \u043a\u0440\u0443\u0442\u043e, \u0441\u043f\u0430\u0441\u0438\u0431\u043e olegbunin \u0437\u0430 \u043e\u0440\u0433\u0430\u043d\u0438\u0437\u0430\u0446\u0438\u044e, \u0430 \u0442\u0430\u043a\u0436\u0435 \u0432\u0441\u0435\u043c, \u043a\u0442\u043e \u043f\u0440\u0438\u0448\u0451\u043b. \u0412 \u044d\u0442\u043e\u043c \u043f\u043e\u0441\u0442\u0435 \u043c\u044b \u0445\u043e\u0442\u0435\u043b\u0438 \u0431\u044b \u043f\u043e\u0434\u0435\u043b\u0438\u0442\u044c\u0441\u044f \u0441 \u0432\u0430\u043c\u0438 \u0437\u0430\u0434\u0430\u0447\u0430\u043c\u0438, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":27053,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-36171","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.3 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u041f\u0440\u0438\u0432\u0435\u0442! 24-25 \u0438\u044e\u043d\u044f \u0432 \u041d\u043e\u0432\u043e\u0441\u0438\u0431\u0438\u0440\u0441\u043a\u0435 \u043f\u0440\u043e\u0448\u043b\u0430 \u043a\u043e\u043d\u0444\u0435\u0440\u0435\u043d\u0446\u0438\u044f Highload++ Siberia 2019.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/po-sledam-highload-siberia-2019-8-zadach-po-oracle\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.3\" \/>\n\t\t<meta property=\"og:locale\" content=\"cs_CZ\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u041f\u043e \u0441\u043b\u0435\u0434\u0430\u043c Highload++ Siberia 2019 \u2014 8 \u0437\u0430\u0434\u0430\u0447 \u043f\u043e Oracle | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041f\u0440\u0438\u0432\u0435\u0442! 24-25 \u0438\u044e\u043d\u044f \u0432 \u041d\u043e\u0432\u043e\u0441\u0438\u0431\u0438\u0440\u0441\u043a\u0435 \u043f\u0440\u043e\u0448\u043b\u0430 \u043a\u043e\u043d\u0444\u0435\u0440\u0435\u043d\u0446\u0438\u044f Highload++ Siberia 2019.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/po-sledam-highload-siberia-2019-8-zadach-po-oracle\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2019-10-31T19:09:58+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2019-10-31T19:09:58+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47Po stop\u00e1ch Highload++ Siberia 2019 \u2014 8 \u00faloh na Oracle | ProHoster","description":"Ahoj! 24-25 \u010dervna prob\u011bhla v Novosibirsku konference Highload++ Siberia 2019.","canonical_url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/po-sledam-highload-siberia-2019-8-zadach-po-oracle","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"cs_CZ","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u041f\u043e \u0441\u043b\u0435\u0434\u0430\u043c Highload++ Siberia 2019 \u2014 8 \u0437\u0430\u0434\u0430\u0447 \u043f\u043e Oracle | ProHoster","og:description":"\u041f\u0440\u0438\u0432\u0435\u0442! 24-25 \u0438\u044e\u043d\u044f \u0432 \u041d\u043e\u0432\u043e\u0441\u0438\u0431\u0438\u0440\u0441\u043a\u0435 \u043f\u0440\u043e\u0448\u043b\u0430 \u043a\u043e\u043d\u0444\u0435\u0440\u0435\u043d\u0446\u0438\u044f Highload++ Siberia 2019.","og:url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/po-sledam-highload-siberia-2019-8-zadach-po-oracle","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T19:09:58+00:00","article:modified_time":"2019-10-31T19:09:58+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"36171","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-22 02:18:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 01:49:43","updated":"2026-01-22 02:18:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/36171","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=36171"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/36171\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media\/27053"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=36171"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=36171"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=36171"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}