🥇Highload++ Siberia 2019-ийн мөрөөр - Oracle дээрх 8 даалгавар

Сайн уу!

24-р сарын 25-2019-нд Новосибирск хотод Highload++ Сибирь XNUMX чуулга уулзалт боллоо.Манай залуус ч тэнд байсан. тайлан "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);

Хариу хариулOracle баримт бичгийн дагуу (11.2-аас иш татсан):

Аливаа SQL мэдэгдлийг гүйцэтгэхийн өмнө Oracle нь далд хадгалах цэгийг тэмдэглэдэг (таны ашиглах боломжгүй). Хэрэв мэдэгдэл амжилтгүй болвол Oracle үүнийг автоматаар буцааж, SQLCA дахь SQLCODE-д холбогдох алдааны кодыг буцаана. Жишээлбэл, хэрэв 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;
/

Хариу хариул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, 'Д', 'Вася');

А ба X
Б, Ү
С ба К
С ба З
К ба З
Би болон Ж
Ж ба 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 нь мэдээллийн санд хадгалагдана. Эдгээр тохиолдолд баганын уртыг (тэг) хадгалахын тулд 1 байт шаардагдана. Шинэ мөрийн толгой хэсэг нь өмнөх эгнээний үлдсэн баганууд хоосон байна гэсэн дохио өгдөг тул дараалсан хоосон тоонууд нь хадгалах шаардлагагүй. Жишээлбэл, хүснэгтийн сүүлийн гурван багана хоосон байвал эдгээр баганад өгөгдөл хадгалагдахгүй.

Эдгээр өгөгдөл дээр үндэслэн бид үндэслэлийг бий болгодог. Өгөгдлийн сан нь AL32UTF8 кодчилол ашигладаг гэж бид таамаглаж байна. Энэ кодчилолд орос үсэг 2 байт эзэлнэ.

1) A ба X, a 'Y' талбарын утга 1 байт, x 'D' талбарын утга 2 байт авна
2) B ба Y, b дахь 'Вася' утгыг 10 хүртэлх тэмдэгтээр дүүргэж, 14 байт, d дахь 'Вася' нь 8 байт авна.
3) C ба K. Хоёр талбар хоёулаа NULL утгатай, тэдгээрийн дараа чухал талбарууд байгаа тул 1 байт эзэлнэ.
4) C ба Z. Хоёр талбар хоёулаа NULL утгатай боловч Z талбар нь хүснэгтийн сүүлчийнх тул зай эзэлдэггүй (0 байт). C талбар нь 1 байт эзэлнэ.
5) K ба Z. Өмнөх тохиолдолтой төстэй. K талбар дахь утга нь 1 байт, Z дээр - 0 байна.
6) Би ба Ж. Баримт бичгийн дагуу хоёр утга нь 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-модны индексийн хувьд индексийн кластерын хүчин зүйл нь индексийн утгатай холбоотой мөрүүдийн физик бүлэглэлийг хэмждэг.

Индекс кластерын хүчин зүйл нь тодорхой асуулгад индекс скан хийх эсвэл хүснэгтийг бүрэн скан хийх нь илүү үр дүнтэй эсэхийг шийдэхэд оновчтой болгоход тусалдаг). Бага кластерын хүчин зүйл нь үр дүнтэй индекс скан байгааг харуулж байна.

Хүснэгт дэх блокуудын тоотой ойролцоо байгаа кластерын хүчин зүйл нь мөрүүдийг индексийн товчлуураар хүснэгтийн блокуудад физик байдлаар эрэмбэлсэн болохыг харуулж байна. Хэрэв өгөгдлийн сан нь хүснэгтийг бүрэн скан хийвэл мэдээллийн сан нь индексийн түлхүүрээр эрэмблэгдсэн дискэн дээр хадгалагдсан мөрүүдийг татаж авах хандлагатай байдаг. Мөрүүдийн тоотой ойролцоо байгаа кластерын хүчин зүйл нь мөрүүд нь индексийн түлхүүртэй холбоотой мэдээллийн сангийн блокууд дээр санамсаргүй байдлаар тархсан болохыг харуулж байна. Хэрэв өгөгдлийн сан нь хүснэгтийг бүхэлд нь скан хийвэл мэдээллийн сан нь энэ индексийн түлхүүрээр эрэмбэлэгдсэн дарааллаар мөрүүдийг татаж авахгүй.

Энэ тохиолдолд өгөгдөл нь хамгийн тохиромжтой байдлаар эрэмблэгдсэн тул кластерын хүчин зүйл нь хүснэгтэд байгаа блокуудын тоотой тэнцүү буюу ойролцоо байх болно. 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 (эсвэл бага) индекс үүсгэх нь асуудалгүй ажиллах болно.

APPEND зөвлөмж бүхий INSERT оператор нь өгөгдлийг шууд горимд ачаалахад зориулагдсан. Хэрэв гохыг өлгөх хүснэгтэд хэрэглэвэл яах вэ?

Өгөгдлийг шууд горимд ачаалах бөгөөд гох нь хүлээгдэж буй байдлаар ажиллах болно
Өгөгдлийг шууд горимд ачаалах боловч гох нь ажиллахгүй
Өгөгдлийг ердийн горимд ачаалах бөгөөд гох нь зохих ёсоор ажиллах болно
Өгөгдлийг ердийн горимд ачаалах боловч гох нь ажиллахгүй
Өгөгдөл ачаалагдахгүй, алдаа бүртгэгдэх болно

Хариу хариулҮндсэндээ энэ бол логикийн асуулт юм. Зөв хариултыг олохын тулд би дараахь үндэслэлийн загварыг санал болгож байна.

Шууд горимд оруулах нь SQL хөдөлгүүрийг тойрон өгөгдлийн блокыг шууд үүсгэх замаар хийгддэг бөгөөд энэ нь өндөр хурдыг хангадаг. Тиймээс гохын гүйцэтгэлийг хангах нь маш хэцүү, хэрэв боломжгүй юм бол энэ нь ямар ч утгагүй, учир нь энэ нь оруулгыг эрс удаашруулах болно.
Триггерийг ажиллуулахгүй байх нь хүснэгтэд байгаа өгөгдөл ижил байвал мэдээллийн сангийн төлөв байдал (бусад хүснэгтүүд) энэ өгөгдлийг оруулсан горимоос хамаарна. Энэ нь өгөгдлийн бүрэн бүтэн байдлыг устгах нь тодорхой бөгөөд үйлдвэрлэлд шийдэл болгон ашиглах боломжгүй юм.
Хүссэн үйлдлийг гүйцэтгэх боломжгүй бол ерөнхийдөө алдаа гэж үздэг. Гэхдээ энд бид APPEND нь зөвлөгөө гэдгийг санах хэрэгтэй бөгөөд зөвлөмжийн ерөнхий логик нь боломжтой бол тэдгээрийг харгалзан үздэг боловч хэрэв үгүй бол оператор нь зөвлөмжийг харгалзахгүйгээр гүйцэтгэгддэг.

Тиймээс хүлээгдэж буй хариулт байна өгөгдөл хэвийн (SQL) горимд ачаалагдах бөгөөд гох асах болно.

Oracle баримт бичгийн дагуу (8.04-аас иш татсан):

Хязгаарлалтуудыг зөрчсөн тохиолдолд мэдэгдлийг ердийн оруулах замыг ашиглан анхааруулга, алдааны мэдэгдэлгүйгээр дараалан гүйцэтгэх болно. Үл хамаарах зүйл бол гүйлгээний явцад нэг хүснэгтэд нэгээс олон удаа хандах хандалтын хязгаарлалт бөгөөд энэ нь алдааны мэдэгдлийг үүсгэж болзошгүй юм.
Жишээлбэл, хэрэв хүснэгтэд триггер эсвэл лавлагааны бүрэн бүтэн байдал байгаа бол шууд ачаалалтай INSERT (цуваа эсвэл зэрэгцээ), мөн хэрэв байгаа бол PARALLEL зөвлөмж эсвэл заалтыг ашиглахыг оролдох үед APPEND зөвлөмжийг үл тоомсорлох болно.

Дараах скриптийг гүйцэтгэхэд юу болох вэ?

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

Амжилттай гүйцэтгэл
Синтаксийн алдааны улмаас бүтэлгүйтсэн
Алдаа: Автономит гүйлгээ хүчингүй байна
Дуудлага хийх дээд хязгаарыг хэтрүүлсэнтэй холбоотой алдаа
Гадаад түлхүүрийн зөрчлийн алдаа
Түгжээтэй холбоотой алдаа

Хариу хариулХүснэгт болон гох нь маш зөв хийгдсэн бөгөөд энэ үйлдэл нь асуудал үүсгэх ёсгүй. Триггер дэх бие даасан гүйлгээг мөн зөвшөөрдөг, эс тэгвээс бүртгэл хийх боломжгүй болно.

Эхний мөрийг оруулсны дараа гохыг амжилттай ажиллуулснаар хоёр дахь мөрийг оруулснаар гох дахин асах, гурав дахь эгнээ оруулах гэх мэт дуудлагын үүрлэх дээд хэмжээнээс хэтэрсэн тул мэдэгдэл амжилтгүй болох хүртэл үргэлжлэх болно. Гэсэн хэдий ч өөр нэг нарийн зүйл гарч ирдэг. Өдөөгчийг гүйцэтгэх үед эхний оруулсан бичлэгийн үүрэг гүйцэтгэгдээгүй байна. Тиймээс, бие даасан гүйлгээнд ажиллаж буй гох нь хараахан хийгээгүй бичлэгийн гадаад түлхүүрийг иш татсан мөрийг хүснэгтэд оруулахыг оролддог. Үүний үр дүнд хүлээлт үүсдэг (бие даасан гүйлгээ нь өгөгдөл оруулах боломжтой эсэхийг шалгахын тулд үндсэн гүйлгээг хүлээдэг) бөгөөд нэгэн зэрэг үндсэн гүйлгээ нь гохын дараа бие даасан гүйлгээг үргэлжлүүлэн ажиллахыг хүлээнэ. Гацаа үүсч, улмаар түгжээтэй холбоотой шалтгааны улмаас бие даасан гүйлгээ цуцлагдана..

Зөвхөн бүртгэлтэй хэрэглэгчид санал асуулгад оролцох боломжтой. Нэвтрэх, гуйя.

Хэцүү байсан уу?

Хоёр хуруу шиг би тэр даруй бүх зүйлийг зөв шийдсэн.
Үгүй ээ, би хэд хэдэн асуултанд буруу байсан.
Би хагасыг нь зөв шийдсэн.
Би хариултыг хоёр удаа таасан!
Би сэтгэгдэл дээр бичнэ

14 хэрэглэгч санал өгсөн. 10 хэрэглэгч түдгэлзсэн.

Эх сурвалж: www.habr.com

Highload++ Siberia 2019-ийн мөрөөр - Oracle дээрх 8 даалгавар

Дараах скриптийг гүйцэтгэсний үр дүнд бидний харах хамгийн дээд дарааллын утга хэд вэ?

Дараах скриптийг ажиллуулсны үр дүнд хүснэгтэд хэдэн мөр байх вэ?

Дараах скриптийг ажиллуулсны үр дүнд хүснэгтэд хэдэн мөр байх вэ?

Аль хос утгууд блок дотор ижил хэмжээний зай эзлэх вэ?

T_I индексийн кластерын хүчин зүйл ойролцоогоор хэд байх вэ?

Дараах скриптийг N-ийн ямар утгуудад стандарт тохиргоотой ердийн мэдээллийн санд амжилттай гүйцэтгэх вэ?

APPEND зөвлөмж бүхий INSERT оператор нь өгөгдлийг шууд горимд ачаалахад зориулагдсан. Хэрэв гохыг өлгөх хүснэгтэд хэрэглэвэл яах вэ?

Дараах скриптийг гүйцэтгэхэд юу болох вэ?

Хэцүү байсан уу?

сэтгэгдэл нэмэх хариу цуцлах