Հետևելով Highload++ Siberia 2019-ի հետքերին՝ 8 առաջադրանք Oracle-ում

Hi!

Հունիսի 24-25-ը Նովոսիբիրսկում կայացավ Highload++ Siberia 2019 կոնֆերանսը, որտեղ մեր տղաներն էլ էին։ հաշվետվություն «Oracle բեռնարկղային տվյալների բազաները (CDB/PDB) և դրանց գործնական օգտագործումը ծրագրային ապահովման մշակման համար», մենք կհրապարակենք տեքստային տարբերակը քիչ ուշ: Հիասքանչ էր, շնորհակալություն օլեգբունին կազմակերպության, ինչպես նաև բոլոր եկածների համար։

Այս գրառման մեջ մենք ցանկանում ենք ձեզ հետ կիսվել մեր տաղավարում ունեցած խնդիրներով, որպեսզի կարողանաք ստուգել ձեր 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-ից).
Մեկ SQL հայտարարության շրջանակներում Oracle-ը կավելացնի հաջորդականությունը միայն մեկ տողում: Եթե ​​հայտարարությունը պարունակում է մեկից ավելի հղում դեպի NEXTVAL հաջորդականության համար, Oracle-ը մեկ անգամ ավելացնում է հաջորդականությունը և վերադարձնում է նույն արժեքը NEXTVAL-ի բոլոր դեպքերի համար: Եթե ​​հայտարարությունը պարունակում է հղումներ և՛ CURRVAL-ին, և՛ NEXTVAL-ին, Oracle-ն ավելացնում է հաջորդականությունը և վերադարձնում է նույն արժեքը և՛ CURRVAL-ի, և՛ NEXTVAL-ի համար՝ անկախ հայտարարությունում դրանց հերթականությունից:

Այսպիսով, առավելագույն արժեքը կհամապատասխանի տողերի քանակին, այսինքն՝ 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-ից).

Նախքան որևէ SQL հայտարարություն կատարելը, Oracle-ը նշում է անուղղակի պահպանման կետը (ձեզ հասանելի չէ): Այնուհետև, եթե հայտարարությունը ձախողվի, Oracle-ն այն ավտոմատ կերպով հետ է գլորում և վերադարձնում է կիրառելի սխալի կոդը SQLCODE-ին SQLCA-ում: Օրինակ, եթե INSERT հայտարարությունը սխալ է առաջացնում՝ փորձելով կրկնօրինակ արժեք տեղադրել եզակի ինդեքսում, հայտարարությունը հետ է վերադարձվում:

Հաճախորդից HP զանգահարելը նույնպես համարվում և մշակվում է որպես մեկ հայտարարություն: Այսպիսով, HP-ի առաջին զանգը հաջողությամբ ավարտվում է՝ տեղադրելով երեք գրառում. HP-ի երկրորդ զանգն ավարտվում է սխալով և հետ է գլորում չորրորդ գրառումը, որը նրան հաջողվել է տեղադրել. երրորդ զանգը ձախողվում է, և աղյուսակում երեք գրառում կա.

Քանի՞ տող կլինի աղյուսակում հետևյալ սցենարը գործարկելու արդյունքում.

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-ից).

Ստուգման սահմանափակումը թույլ է տալիս նշել մի պայման, որը պետք է բավարարի աղյուսակի յուրաքանչյուր տող: Սահմանափակումը բավարարելու համար աղյուսակի յուրաքանչյուր տող պետք է պայմանը դարձնի ՃԻՇՏ կամ անհայտ (զրոյականի պատճառով): Երբ Oracle-ը գնահատում է ստուգման սահմանափակման պայմանը որոշակի տողի համար, պայմանի ցանկացած սյունակի անվանումը վերաբերում է այդ շարքի սյունակների արժեքներին:

Այսպիսով, 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
• Բ և Յ
• Գ և Կ
• Գ և Զ
• Կ և Զ
• Ես և Ջ
• J և X
• Բոլոր թվարկվածները

ՊատասխանԱհա հատվածներ Oracle-ում տարբեր տեսակի տվյալների պահպանման վերաբերյալ փաստաթղթերից (12.1.0.2):

CHAR տվյալների տեսակը
CHAR տվյալների տեսակը նշում է ֆիքսված երկարությամբ նիշերի տողը տվյալների բազայի նիշերի հավաքածուում: Դուք նշում եք տվյալների բազայի նիշերի հավաքածուն, երբ ստեղծում եք ձեր տվյալների բազան: Oracle-ն ապահովում է, որ CHAR սյունակում պահվող բոլոր արժեքներն ունենան ընտրված երկարության իմաստաբանության մեջ նշված չափի երկարությունը: Եթե ​​դուք տեղադրեք արժեք, որն ավելի կարճ է, քան սյունակի երկարությունը, ապա Oracle-ի դատարկ բարձիկներն այդ արժեքը փոխում է սյունակի երկարության վրա:

VARCHAR2 Տվյալների տեսակը
VARCHAR2 տվյալների տեսակը նշում է տվյալների բազայի նիշերի հավաքածուի փոփոխական երկարությամբ նիշերի տողը: Դուք նշում եք տվյալների բազայի նիշերի հավաքածուն, երբ ստեղծում եք ձեր տվյալների բազան: Oracle-ը VARCHAR2 սյունակում պահում է նիշերի արժեքը ճիշտ այնպես, ինչպես դուք եք նշել, առանց որևէ դատարկ լցոնման, պայմանով, որ արժեքը չի գերազանցում սյունակի երկարությունը:

NUMBER Տվյալների տեսակը
NUMBER տվյալների տեսակը պահում է զրո, ինչպես նաև դրական և բացասական ֆիքսված թվեր՝ բացարձակ արժեքներով 1.0 x 10-130-ից մինչև 1.0 x 10126-ը, բայց չներառյալ: Եթե նշեք թվաբանական արտահայտություն, որի արժեքը մեծ կամ հավասար է 1.0 x 10126, ապա Oracle-ը վերադարձնում է սխալ: Յուրաքանչյուր NUMBER արժեք պահանջում է 1-ից մինչև 22 բայթ: Հաշվի առնելով դա, սյունակի չափը բայթերով որոշակի թվային տվյալների NUMBER(p) արժեքի համար, որտեղ p-ը տվյալ արժեքի ճշգրտությունն է, կարող է հաշվարկվել հետևյալ բանաձևով. ROUND((երկարություն(p)+s)/2))+1 որտեղ s-ը հավասար է զրոյի, եթե թիվը դրական է, իսկ s-ը հավասար է 1-ի, եթե թիվը բացասական է:

Բացի այդ, եկեք մի հատված վերցնենք Null արժեքները պահելու մասին փաստաթղթերից։

Null-ը սյունակում արժեքի բացակայությունն է: Null-ները ցույց են տալիս բացակայող, անհայտ կամ անկիրառելի տվյալներ: Null-ները պահվում են տվյալների բազայում, եթե դրանք ընկնում են տվյալների արժեքներով սյունակների միջև: Այս դեպքերում նրանք պահանջում են 1 բայթ սյունակի երկարությունը (զրո) պահելու համար։ Անընդմեջ հաջորդող զրոյականները պահեստավորում չեն պահանջում, քանի որ նոր տողի վերնագիրն ազդարարում է, որ նախորդ տողում մնացած սյունակները զրոյական են: Օրինակ, եթե աղյուսակի վերջին երեք սյունակները զրոյական են, ապա այս սյունակների համար տվյալներ չեն պահվում:

Այս տվյալների հիման վրա մենք հիմնավորում ենք հիմնավորում: Մենք ենթադրում ենք, որ տվյալների բազան օգտագործում է AL32UTF8 կոդավորումը: Այս կոդավորման մեջ ռուսերեն տառերը կզբաղեցնեն 2 բայթ։

1) A և X, a 'Y' դաշտի արժեքը վերցնում է 1 բայթ, x դաշտի արժեքը՝ 2 բայթ
2) B և Y, «Vasya» b-ում արժեքը լրացվելու է մինչև 10 նիշ բացատներով և կտևի 14 բայթ, «Vasya»-ն d-ում՝ 8 բայթ:
3) C և K. Երկու դաշտերն էլ ունեն NULL արժեքը, դրանցից հետո զգալի դաշտեր են, ուստի դրանք զբաղեցնում են 1 բայթ:
4) C և Z: Երկու դաշտերն էլ ունեն NULL արժեքը, բայց Z դաշտը վերջինն է աղյուսակում, ուստի այն տեղ չի զբաղեցնում (0 բայթ): C դաշտը զբաղեցնում է 1 բայթ:
5) Կ և Զ. Նախորդ դեպքի նման. K դաշտում արժեքը զբաղեցնում է 1 բայթ, Z-ում՝ 0:
6) I և J. Փաստաթղթերի համաձայն, երկու արժեքները կկազմեն 2 բայթ: Մենք հաշվարկում ենք երկարությունը՝ օգտագործելով փաստաթղթերից վերցված բանաձևը՝ կլոր( (1 + 0)/2) +1 = 1 + 1 = 2:
7) J և X. J դաշտի արժեքը կկազմի 2 բայթ, X դաշտում արժեքը՝ 2 բայթ:

Ընդհանուր առմամբ, ճիշտ տարբերակներն են՝ C և K, I և J, J և X:

Մոտավորապես որքա՞ն կլինի 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 ինդեքսի համար ինդեքսի կլաստերավորման գործոնը չափում է տողերի ֆիզիկական խմբավորումը ինդեքսի արժեքի նկատմամբ:

Ինդեքսի կլաստերավորման գործոնն օգնում է օպտիմիզատորին որոշել՝ արդյոք ինդեքսի սկանավորումը կամ ամբողջական աղյուսակի սկանավորումն ավելի արդյունավետ է որոշակի հարցումների համար): Ցածր խմբավորման գործակիցը ցույց է տալիս արդյունավետ ինդեքսի սկանավորում:

Կլաստերավորման գործակիցը, որը մոտ է աղյուսակի բլոկների թվին, ցույց է տալիս, որ տողերը ֆիզիկապես դասավորված են աղյուսակի բլոկներում ինդեքսի ստեղնով: Եթե ​​տվյալների բազան կատարում է աղյուսակի ամբողջական սկանավորում, ապա տվյալների բազան ձգտում է առբերել տողերը, քանի որ դրանք պահվում են սկավառակի վրա՝ դասավորված ըստ ինդեքսի բանալիով: Կլաստերավորման գործակիցը, որը մոտ է տողերի թվին, ցույց է տալիս, որ տողերը պատահականորեն ցրված են տվյալների բազայի բլոկների վրա՝ կապված ինդեքսի բանալի հետ: Եթե ​​տվյալների բազան կատարում է աղյուսակի ամբողջական սկանավորում, ապա տվյալների բազան չի առբերի տողերը որևէ տեսակավորված հերթականությամբ այս ինդեքսային բանալիով:

Այս դեպքում տվյալները իդեալականորեն դասավորված են, ուստի կլաստերավորման գործոնը հավասար կամ մոտ կլինի աղյուսակում զբաղեցրած բլոկների թվին: 8 կիլոբայթ ստանդարտ բլոկի չափի համար կարող եք ակնկալել, որ մոտ հազար նեղ թվային արժեքներ կտեղավորվեն մեկ բլոկի մեջ, ուստի բլոկների քանակը և արդյունքում կլաստերավորման գործոնը կլինի. մոտ տասնյակ.

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-ից).

Տրամաբանական տվյալների բազայի սահմանները

կետ
Սահմանաչափի տեսակը
Սահմանային արժեքը

Ինդեքսը
Ինդեքսավորված սյունակի ընդհանուր չափը
Տվյալների բազայի բլոկի չափի 75%-ը՝ հանած որոշ վերադիր ծախսեր

Այսպիսով, ինդեքսավորված սյունակների ընդհանուր չափը չպետք է գերազանցի 6 Կբ: Ինչ կլինի հետո, կախված է ընտրված բազային կոդավորումից: AL32UTF8 կոդավորման համար մեկ նիշը կարող է զբաղեցնել առավելագույնը 4 բայթ, այնպես որ վատագույն դեպքում 6 կիլոբայթը կտեղավորվի մոտ 1500 նիշ: Հետևաբար, Oracle-ը թույլ չի տա ինդեքսի ստեղծումը N = 400 (երբ ամենավատ բանալի երկարությունը 1600 նիշ է * 4 բայթ + շարքի երկարություն), մինչդեռ N = 200 (կամ ավելի քիչ) ինդեքսի ստեղծումը կաշխատի առանց խնդիրների:

INSERT օպերատորը APPEND հուշումով նախատեսված է տվյալների ուղղակի ռեժիմում բեռնելու համար: Ի՞նչ կլինի, եթե այն կիրառվի սեղանի վրա, որից կախված է ձգանը:

• Տվյալները կբեռնվեն ուղիղ ռեժիմով, ձգան կաշխատի ինչպես սպասվում էր
• Տվյալները կբեռնվեն ուղիղ ռեժիմով, բայց գործարկիչը չի գործարկվի
• Տվյալները կբեռնվեն սովորական ռեժիմով, ձգան կաշխատի այնպես, ինչպես պետք է
• Տվյալները կբեռնվեն սովորական ռեժիմով, բայց գործարկիչը չի գործարկվի
• Տվյալները չեն բեռնվի, սխալ կգրանցվի

ՊատասխանՀիմնականում սա ավելի շատ տրամաբանության հարց է։ Ճիշտ պատասխանը գտնելու համար ես կառաջարկեի հետևյալ հիմնավորման մոդելը.

1. Ուղղակի ռեժիմում տեղադրումը կատարվում է տվյալների բլոկի ուղղակի ձևավորմամբ՝ շրջանցելով SQL շարժիչը, որն ապահովում է բարձր արագություն։ Այսպիսով, ձգանի գործարկումը ապահովելը շատ դժվար է, եթե ոչ անհնար, և դա իմաստ չունի, քանի որ այն դեռ արմատապես կդանդաղեցնի տեղադրումը:
2. Գործարկիչը չկատարելը կհանգեցնի նրան, որ եթե աղյուսակի տվյալները նույնն են, տվյալների բազայի վիճակը որպես ամբողջություն (այլ աղյուսակներ) կախված կլինի այն ռեժիմից, որով տեղադրվել են այս տվյալները: Սա ակնհայտորեն կկործանի տվյալների ամբողջականությունը և չի կարող կիրառվել որպես լուծում արտադրության մեջ:
3. Պահանջվող գործողությունն իրականացնելու անկարողությունը սովորաբար դիտվում է որպես սխալ: Բայց այստեղ պետք է հիշել, որ APPEND-ը ակնարկ է, և ակնարկների ընդհանուր տրամաբանությունն այն է, որ հնարավորության դեպքում դրանք հաշվի են առնվում, իսկ եթե ոչ, ապա օպերատորը կատարվում է առանց ակնարկը հաշվի առնելու։

Այսպիսով, սպասված պատասխանն է տվյալները կբեռնվեն նորմալ (SQL) ռեժիմով, ձգանը կաշխատի:

Ըստ Oracle-ի փաստաթղթերի (մեջբերված 8.04-ից).

Սահմանափակումների խախտումները կհանգեցնեն նրան, որ քաղվածքը կկատարվի սերիական՝ օգտագործելով ավանդական ներդիրի ուղին, առանց նախազգուշացումների կամ սխալի հաղորդագրությունների: Բացառություն է գործարքի ընթացքում միևնույն աղյուսակը մեկից ավելի անգամ մուտք գործելու քաղվածքների սահմանափակումը, որը կարող է առաջացնել սխալ հաղորդագրություններ:
Օրինակ, եթե աղյուսակում առկա են գործարկիչներ կամ հղումային ամբողջականություն, ապա APPEND հուշումը անտեսվելու է, երբ փորձեք օգտագործել ուղղակի բեռնված INSERT (սերիական կամ զուգահեռ), ինչպես նաև 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);

• Հաջող ավարտ
• Չհաջողվեց շարահյուսական սխալի պատճառով
• Սխալ․ ինքնավար գործարքը վավեր չէ
• Սխալ՝ կապված զանգերի տեղադրման առավելագույն չափը գերազանցելու հետ
• Օտարերկրյա բանալիների խախտում
• Սխալ՝ կապված կողպեքների հետ

ՊատասխանՍեղանն ու ձգանը բավականին ճիշտ են ստեղծված, և այս գործողությունը չպետք է հանգեցնի խնդիրների: Ինքնավար գործարքները նույնպես թույլատրվում են ձգանով, հակառակ դեպքում գրանցումը հնարավոր չէր լինի, օրինակ:

Առաջին շարքը ներդնելուց հետո ձգանի հաջող կրակումը կհանգեցնի երկրորդ շարքի տեղադրմանը, որի արդյունքում ձգանը նորից կաշխատի, երրորդ տողը տեղադրվի և այսպես շարունակ, մինչև հայտարարությունը ձախողվի՝ զանգերի առավելագույն չափը գերազանցելու պատճառով: Այնուամենայնիվ, մեկ այլ նուրբ կետ հայտնվում է խաղի մեջ. Այն պահին, երբ գործարկվում է, commit-ը դեռ չի ավարտվել առաջին ներդրված ձայնագրության համար: Հետևաբար, ինքնավար գործարքում գործարկվող գործարկիչը փորձում է աղյուսակում ներդնել մի տող, որը հղում է անում օտար բանալիին այն գրառումին, որը դեռ չի կատարվել: Սա հանգեցնում է սպասման (ինքնավար գործարքը սպասում է, որ հիմնական գործարքը կատարվի, որպեսզի տեսնի, թե արդյոք այն կարող է տեղադրել տվյալներ), և միևնույն ժամանակ հիմնական գործարքը սպասում է, որ ինքնավար գործարքը շարունակի աշխատել գործարկելուց հետո: Առաջանում է փակուղի, և արդյունքում ինքնավար գործարքը չեղյալ է հայտարարվում կողպեքների հետ կապված պատճառներով.

Հարցմանը կարող են մասնակցել միայն գրանցված օգտվողները։ Մուտք գործել, խնդրում եմ:

Դժվար էր?

• Երկու մատի պես ես անմիջապես ամեն ինչ ճիշտ որոշեցի։

• Իրականում չէ, մի երկու հարցում սխալվեցի։

• կեսը ճիշտ եմ լուծել։

• Ես երկու անգամ կռահեցի պատասխանը։

• Կգրեմ մեկնաբանություններում

Քվեարկել է 14 օգտատեր։ 10 օգտատեր ձեռնպահ է մնացել։

Source: www.habr.com