Па слядах Highload++ Siberia 2019 - 8 задач па Oracle

Прывітанне!

24-25 чэрвеня ў Новасібірску прайшла канферэнцыя Highload++ Siberia 2019. Нашы хлопцы таксама там былі дакладам «Кантэйнерныя базы Oracle (CDB/PDB) і іх практычнае выкарыстанне для распрацоўкі ПЗ», мы выкладзем тэкставую версію крыху пазней. Было крута, дзякуй olegbunin за арганізацыю, а таксама ўсім, хто прыйшоў.

У гэтым пасце мы хацелі б падзяліцца з вамі задачамі, якія былі на нашым стэндзе, каб вы маглі праверыць свае веды ў Oracle. Пад катом - 8 задач, варыянты адказаў і тлумачэнне.

Якое максімальнае значэнне сіквенса мы ўбачым у выніку выканання наступнага скрыпту?

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
• Ніякага, будзе памылка

АдказЗгодна з дакументацыяй Oracle (цытуецца з 8.1.6):
Within a single SQL statement, Oracle будзе increment the sequence only once per row. Калі статыстыка спісу больш чым адна ацэнка NEXTVAL для экзамену, Oracle increments the sequence once and returns the value for all occurrences of NEXTVAL. Калі стан статкаў абмяжоўвае, што да CURRVAL and NEXTVAL, Oracle increments the sequence and returns the value for both CURRVAL and NEXTVAL regardless of their order within the statement.

Такім чынам, максімальнае значэнне будзе адпавядаць колькасці радкоў, гэта значыць 5.

Колькі радкоў апынецца ў табліцы ў выніку выканання наступнага скрыпта?

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

АдказЗгодна з дакументацыяй Oracle (цытуецца з 11.2):

Згодна з executing any SQL statement, Oracle marks implicit savepoint (no no available to you). Існуе, калі статыстыка небяспечных, Oracle rolls it back automatically and returns the applicable error code to SQLCODE in the SQLCA. Для прыкладу, калі ў INSERT statement causas error error trying to insert duplicate value in unique index, statement is rolled back.

Выклік ХП з кліента таксама разглядаецца і апрацоўваецца як single statement. Такім чынам, першы выклік ХП шчасна завяршаецца, уставіўшы тры запісы; другі выклік ХП завяршаецца з памылкай і адкочвае чацвёрты запіс, які паспеў уставіць; трэці выклік завяршаецца з памылкай, і ў табліцы аказваюцца тры запісы.

Колькі радкоў апынецца ў табліцы ў выніку выканання наступнага скрыпта?

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

АдказЗгодна з дакументацыяй Oracle (цытуецца з 11.2):

Check konstraint lets you specify a condition that you row in the table must satisfy. Для таго, каб адбіць, кожная роў у табло павінна быць умова яе TRUE або недастаткова (due to a null). When Oracle выражае check constraint condition for particular row, any column name in the condition refer to the column values ​​in that row.

Такім чынам, значэнне null пройдзе праверку, і ананімны блок будзе паспяхова выконвацца аж да спробы ўставіць значэнне 3. Пасля гэтага блок апрацоўкі памылак пагасіць выключэнне, адкату не адбудзецца і у табліцы застануцца чатыры радкі са значэннямі 1, null, 2 і зноў null.

Якія пары значэнняў зоймуць аднолькавыя аб'ёмы месца ў блоку?

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 і X
• B і Y
• C і K
• C і Z
• K і Z
• I і J
• J і X
• усе пералічаныя

АдказПрывядзем вытрымкі з дакументацыі (12.1.0.2) па захоўванні розных тыпаў дадзеных у Oracle.

CHAR Data Type
CHAR data type specifies fixed-length string character in the database character set. Вы мяркуеце, што database character set when you create your database. Oracle выявы, што ўсе цэны стаяць у column CHAR, маюць сярэдняе значэнне, якое выдзяляецца ў памеры, выбранай агульнай сярэдняй. Калі вы мяркуеце, што значэнне цэлебруецца, не толькі цэх column length, але і Oracle бланк-сховішча значэнне column length.

VARCHAR2 Data Type
VARCHAR2 specifies data typ type a variable-length character string in database character set. Вы мяркуеце, што database character set when you create your database. Oracle stores charakteru value v columnu VARCHAR2, як правіла, вы імкнецеся да яго, без якога-небудзь бланк-паддзялення, выкананы значэнне цаны не exceed length of column.

NUMBER Data Type
NUMBER data type stores zero je dobře a negative fixed numbers with absolute values ​​from 1.0 x 10-130 to no including 1.0 x 10126. x 1.0, then Oracle returns an error. Each NUMBER кошты ад 10126 to 1 bytes. Taking this into account, the column size in bytes for particular numerical data value NUMBER(p), дзе p es precision of given value, can be calculated using the following formula: ROUND((length(p)+s)/2))+1 where s equals zero if the number is positive, and s equals 1 if the number is negative.

Акрамя таго, возьмем вытрымку з дакументацыі наконт захоўвання Null-значэнняў.

Null is absence of value in a column. Nulls indicate missing, unknown, ці inapplicable data. nulls знаходзяцца ў database і яны сядзяць паміж columns with data values. У гэтых выпадках, яны патрабуюць адно месца да плошчы column (zero). Прайдючы nulls у роўных патрабаваннях не стаўлення да таго, што новыя роўныя лічбы signals, што зменшыны columns ў папярэдняга шэрагу null. Для прыкладу, калі апошнія тры columns table null, no data is stored for these columns.

Зыходзячы з гэтых дадзеных, які будуецца развагі. Лічым, што ў БД выкарыстоўваецца кадоўка AL32UTF8. У гэтай кадоўцы рускія літары будуць займаць 2 байта.

1) A і X, значэнне поля a 'Y' займае 1 байт, значэнне поля x 'Д' - 2 байта
2) B і Y, 'Вася' у b значэнне дапоўніцца прабеламі да 10 сімвалаў і зойме 14 байт, 'Вася' у d - зойме 8 байт.
3) C і K. Абодва палі маюць значэнне NULL, пасля іх ёсць значныя палі, таму займаюць па 1 байце.
4) C і Z. Абодва палі маюць значэнне NULL, але поле Z - апошняе ў табліцы, таму месца не займае (0 байт). Поле З займае 1 байт.
5) K і Z. Аналагічна папярэдняй нагоды. Значэнне ў поле K займае 1 байт, у Z - 0.
6) I і J. Згодна з дакументацыяй, абодва значэння зоймуць па 2 байта. Даўжыню лічым па ўзятай з дакументацыі формулы: round((1 + 0)/2) +1 = 1 + 1 = 2.
7) J і X. Значэнне ў полі J зойме 2 байт, значэнне ў полі X зойме 2 байта.

Разам, правільныя варыянты: З і K, I і J, J і X.

Які прыкладна будзе clustering factor у азначніка 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);

• Каля дзесяткаў
• Каля сотняў
• Каля тысяч
• Каля дзесяткаў тысяч

АдказЗгодна з дакументацыяй Oracle (цытуецца з 12.1):

Для B-tree index, index clustering factor measures physical grouping of rows in relation to index value.

index clustering factor helps optimizer decide whether index scan or full table scan is more efficient for certain queries). Нізкі clustering factor лічбы на эфектыўны index scan.

clustering factor that is close to number of blocks in table indicates that the rows are physically ordered in table blocks by the index key. Калі вашыя стандартныя памеры плана цалкам складаюць, лічыцца аналітычным аналітычным анатомічным планам. clustering factor that is close number of rows indicates that the rows are scattered randomly across the database blocks in relation to the index key. If the database performs a full table scan, then the database would no retrieve rows in any sorted order by index key.

У дадзеным выпадку дадзеныя ідэальна адсартаваны, таму clustering factor будзе роўны ці блізкі да колькасці занятых блокаў у табліцы. Для стандартнага памеру блока ў 8 кілабайт можна чакаць, што ў адзін блок змесціцца каля тысячы вузкіх number значэнняў, таму колькасць блокаў, і як следства clustering factor будзе парадку дзясяткаў.

Пры якіх значэннях N наступны скрыпт паспяхова выканаецца ў звычайнай БД са стандартнымі наладамі?

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

АдказЗгодна з дакументацыяй Oracle (цытуецца з 11.2):

Logical Database Limits

пункт
Type of Limit
Лімітавае значэнне

Індэксы
Total size of indexed column
75% of database block size minus some overhead

Такім чынам, сумарны памер індэксаваць калонак не павінен пераўзыходзіць 6Кб. Далейшае залежыць ад абранай кадоўкі базы. Для кадоўкі AL32UTF8 адзін сімвал можа займаць максімум 4 байта, такім чынам, у 6 кілабайт у горшым варыянце змесціцца каля 1500 сімвалаў. Таму Oracle забароніць стварэнне азначніка пры N = 400 (калі даўжыня ключа ў горшым выпадку будзе 1600 знакаў * 4 байта + даўжыня rowid), у той час як пры N = 200 (і менш) стварэнне індэкса адпрацуе без праблем.

Аператар INSERT з хінтом APPEND прызначаны для загрузкі дадзеных у direct-рэжыме. Што адбудзецца, калі ён будзе ўжыты да табліцы, на якой вісіць трыгер?

• Дадзеныя будуць загружаны ў direct-рэжыме, трыгер спрацуе як павінен
• Дадзеныя будуць загружаны ў direct-рэжыме, але трыгер выкананы не будзе
• Дадзеныя будуць загружаны ў conventional-рэжыме, трыгер спрацуе як павінен
• Дадзеныя будуць загружаны ў conventional-рэжыме, але трыгер выкананы не будзе
• Дадзеныя не будуць загружаныя, будзе зафіксаваная памылка

АдказУ прынцыпе, гэтае пытанне больш на логіку. Для знаходжання правільнага адказу я прапанаваў бы наступную мадэль разваг:

1. Устаўка ў direct рэжыме выконваецца прамым фармаваннем блока дадзеных, міма SQL-рухавічка, што і забяспечвае высокую хуткасць. Такім чынам, забяспечыць выкананне трыгера вельмі складана, калі наогул магчыма, і сэнсу ў гэтым няма, бо ён усё роўна кардынальна затармозіць устаўку.
2. Невыкананне трыгера прывядзе да таго, што пры аднолькавых дадзеных у табліцы стан базы ў цэлым (іншых табліц) будзе залежаць ад таго, у якім менавіта рэжыме ўстаўлены гэтыя дадзеныя. Гэта відавочна разбурыць цэласнасць дадзеных і не можа быць прыменена як рашэнне ў production.
3. Немагчымасць выканаць запытаную аперацыю, наогул кажучы, тлумачыцца як памылка. Але тут варта ўспомніць аб тым, што APPEND - хінт, а агульная логіка хінтов заключаецца ў тым, што яны ўлічваюцца калі гэта магчыма, калі ж не - аператар выконваецца без уліку хінта.

Такім чынам, чаканы адказ - дадзеныя будуць загружаны ў звычайным (SQL) рэжыме, трыгер спрацуе.

Згодна з дакументацыяй Oracle (цытуецца з 8.04):

Violations of restrictions мусяць быць у сістэме ажыццяўлення ажыццяўлення, шляхам кансультацыйнага insert path, bez výstrahy nebo omylu messages. Exception je restriction on statements accessing the same table more than once in transaction, which can cause error messages.
Для прыкладу, калі заваёўнікі або парадная ўнутранасць з'яўляюцца сапраўднымі на пляцы, то для таго, каб спыніцца, было б непрытомным, калі вы выкарыстоўваеце direct-load INSERT (serial або parallel), як добра, як PARALLEL, або клавія, калі.

Што адбудзецца пры выкананні наступнага скрыпта?

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

• Паспяховае выкананне
• Збой з-за сінтаксічнай памылкі
• Памылка, звязаная з недапушчальнасцю аўтаномнай транзакцыі
• Памылка, звязаная з перавышэннем максімальнай укладзенасці выклікаў
• Памылка, звязаная з парушэннем знешняга ключа
• Памылка, звязаная з блакіроўкамі

АдказТабліца і трыгер ствараюцца суцэль карэктна і гэтая аперацыя не павінна прывесці да праблем. Аўтаномныя транзакцыі ў трыгеры таксама дазволеныя, інакш было б немагчымым, напрыклад, лагіраванне.

Пасля ўстаўкі першага радка паспяховае спрацоўванне трыгера прывяло б да ўстаўкі другога радка, у сувязі з чым зноў спрацаваў бы трыгер, уставіў бы трэці радок і гэтак далей датуль, пакуль statement не зваліўся б з-за перавышэнні максімальнай укладзенасці выклікаў. Аднак спрацоўвае яшчэ адзін тонкі момант. У момант выканання трыгера для першага ўстаўленага запісу яшчэ не выкананы commit. Таму трыгер, які працуе ў аўтаномнай транзакцыі, спрабуе ўставіць у табліцу радок, якая спасылаецца па вонкавым ключы на ​​яшчэ не закаммічаны запіс. Гэта прыводзіць да чакання (аўтаномная транзакцыя чакае комміта асноўнай, каб зразумець, ці можна ўставіць дадзеныя) і адначасова асноўная транзакцыя чакае комміта аўтаномнай, каб працягнуць працу пасля трыгера. Узнікае deadlock і як следства - аўтаномная транзакцыя адстрэльваецца па чынніку, звязанай з блакіроўкамі..

Толькі зарэгістраваныя карыстачы могуць удзельнічаць у апытанні. Увайдзіце, Калі ласка.

Цяжка было?

• Як два пальцы, адразу вырашыў усё правільна.

• Не надта, памыліўся ў пары пытанняў.

• Вырашыў палову правільна.

• Два разы адгадаў адказ!

• Напішу ў каментарах

Прагаласавалі 14 карыстальнікаў. Устрымаліся 10 карыстальнікаў.

Крыніца: habr.com