🥇Highload++ Siberia 2019 izidan - Oracle-da 8 ta vazifa

Salom!

24-25 iyun kunlari Novosibirskda Highload++ Sibir 2019 konferensiyasi bo‘lib o‘tdi.U yerda yigitlarimiz ham bo‘lishdi. hisobot "Oracle konteyner ma'lumotlar bazalari (CDB/PDB) va ulardan dasturiy ta'minotni ishlab chiqishda amaliy foydalanish" matnli versiyasini birozdan keyin e'lon qilamiz. Bu ajoyib edi, rahmat olegbunin tashkilot uchun, shuningdek, kelgan har bir kishi uchun.

Ushbu postda siz Oracle bilimingizni sinab ko'rishingiz uchun stendimizda bo'lgan muammolarni siz bilan baham ko'rmoqchimiz. Kesim ostida 8 ta muammo, javob variantlari va tushuntirish mavjud.

Quyidagi skriptni bajarish natijasida biz ko'radigan maksimal ketma-ketlik qiymati qanday?

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
Yo'q, xato bo'ladi

Javob beringOracle hujjatlariga muvofiq (8.1.6 dan keltirilgan):
Bitta SQL bayonotida Oracle ketma-ketlikni har bir satrda faqat bir marta oshiradi. Agar bayonot ketma-ketlik uchun NEXTVAL ga bir nechta havolani o'z ichiga olsa, Oracle ketma-ketlikni bir marta oshiradi va NEXTVAL ning barcha holatlari uchun bir xil qiymatni qaytaradi. Agar bayonot CURRVAL va NEXTVAL ga havolalarni o'z ichiga olsa, Oracle ketma-ketlikni oshiradi va bayonotdagi tartibidan qat'i nazar, CURRVAL va NEXTVAL uchun bir xil qiymatni qaytaradi.

Shunday qilib, maksimal qiymat qatorlar soniga to'g'ri keladi, ya'ni 5.

Quyidagi skriptni ishga tushirish natijasida jadvalda nechta qator bo'ladi?

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

Javob beringOracle hujjatlariga muvofiq (11.2 dan keltirilgan):

Har qanday SQL bayonotini bajarishdan oldin, Oracle yashirin saqlash nuqtasini belgilaydi (siz uchun mavjud emas). Keyin, agar bayonot bajarilmasa, Oracle uni avtomatik ravishda qaytaradi va tegishli xato kodini SQLCA-dagi SQLCODE-ga qaytaradi. Misol uchun, agar INSERT iborasi noyob indeksga takroriy qiymat kiritishga urinib, xatolikka sabab bo'lsa, bayonot orqaga qaytariladi.

Mijozdan HPga qo'ng'iroq qilish ham bitta bayonot sifatida ko'rib chiqiladi va qayta ishlanadi. Shunday qilib, birinchi HP qo'ng'irog'i uchta yozuvni kiritgan holda muvaffaqiyatli yakunlanadi; ikkinchi HP qo'ng'irog'i xato bilan tugaydi va u kiritishga muvaffaq bo'lgan to'rtinchi yozuvni orqaga qaytaradi; uchinchi qo'ng'iroq muvaffaqiyatsiz tugadi, va jadvalda uchta yozuv mavjud.

Quyidagi skriptni ishga tushirish natijasida jadvalda nechta qator bo'ladi?

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

Javob beringOracle hujjatlariga muvofiq (11.2 dan keltirilgan):

Tekshirish cheklovi jadvaldagi har bir qatorni qondirishi kerak bo'lgan shartni belgilash imkonini beradi. Cheklovni qondirish uchun jadvaldagi har bir qator shartni TRUE yoki noma'lum (nol tufayli) qilish kerak. Oracle ma'lum bir satr uchun chek cheklash shartini baholaganda, shartdagi har qanday ustun nomlari ushbu qatordagi ustun qiymatlariga ishora qiladi.

Shunday qilib, null qiymati tekshiruvdan o'tadi va anonim blok 3 qiymatini kiritishga urinish bo'lgunga qadar muvaffaqiyatli bajariladi. Shundan so'ng, xatolarni qayta ishlash bloki istisnoni o'chiradi, hech qanday orqaga qaytish sodir bo'lmaydi va jadvalda to'rtta qator qoladi 1, null, 2 va yana null qiymatlari bilan.

Qaysi juft qiymatlar blokda bir xil joy egallaydi?

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 va X
B va Y
C va K
C va Z
K va Z
Men va J
J va X
Hammasi sanab o'tilgan

Javob beringBu erda Oracle'da har xil turdagi ma'lumotlarni saqlash bo'yicha hujjatlardan (12.1.0.2) parchalar keltirilgan.

CHAR ma'lumotlar turi
CHAR ma'lumotlar turi ma'lumotlar bazasi belgilar to'plamida belgilangan uzunlikdagi belgilar qatorini belgilaydi. Ma'lumotlar bazasini yaratishda siz ma'lumotlar bazasi belgilar to'plamini belgilaysiz. Oracle CHAR ustunida saqlangan barcha qiymatlar tanlangan uzunlik semantikasida o'lcham bo'yicha belgilangan uzunlikka ega bo'lishini ta'minlaydi. Agar siz ustun uzunligidan qisqaroq qiymat qo'shsangiz, Oracle qiymatni ustun uzunligiga bo'sh joylashtiradi.

VARCHAR2 ma'lumotlar turi
VARCHAR2 ma'lumotlar turi ma'lumotlar bazasi belgilar to'plamida o'zgaruvchan uzunlikdagi belgilar qatorini belgilaydi. Ma'lumotlar bazasini yaratishda siz ma'lumotlar bazasi belgilar to'plamini belgilaysiz. Oracle belgi qiymatini VARCHAR2 ustunida aynan siz ko'rsatgandek, hech qanday bo'sh joy qo'ymasdan saqlaydi, agar qiymat ustun uzunligidan oshmasa.

NUMBER maʼlumotlar turi
NUMBER maʼlumotlar turi 1.0 x 10-130 dan 1.0 x 10126 gacha boʻlgan mutlaq qiymatlari bilan bir qatorda musbat va manfiy sobit raqamlar bilan bir qatorda nolni saqlaydi. Agar siz qiymati mutlaq qiymatdan katta yoki teng boʻlgan arifmetik ifodani koʻrsatsangiz. 1.0 x 10126, keyin Oracle xatolik qaytaradi. Har bir NUMBER qiymati 1 baytdan 22 baytgacha talab qiladi. Buni hisobga olgan holda, ma'lum bir raqamli ma'lumotlar qiymati NUMBER(p) uchun baytlardagi ustun o'lchami, bu erda p - berilgan qiymatning aniqligi quyidagi formula yordamida hisoblanishi mumkin: ROUND((uzunlik(p)+s)/2))+1 Bu erda s musbat son bo'lsa nolga, manfiy bo'lsa s 1 ga teng.

Bundan tashqari, null qiymatlarni saqlash haqidagi hujjatlardan ko'chirma olaylik.

Null - ustunda qiymat yo'qligi. Nulllar etishmayotgan, noma'lum yoki qo'llanilmaydigan ma'lumotlarni bildiradi. Agar ular ma'lumotlar qiymatlari bo'lgan ustunlar orasiga tushsa, nulllar ma'lumotlar bazasida saqlanadi. Bunday hollarda ustun uzunligini saqlash uchun 1 bayt kerak bo'ladi (nol). Qatordagi nulllar saqlashni talab qilmaydi, chunki yangi satr sarlavhasi oldingi qatordagi qolgan ustunlar null ekanligini bildiradi. Misol uchun, agar jadvalning oxirgi uchta ustuni null bo'lsa, bu ustunlar uchun hech qanday ma'lumot saqlanmaydi.

Ushbu ma'lumotlarga asoslanib, biz fikr yuritamiz. Biz ma'lumotlar bazasi AL32UTF8 kodlashdan foydalanadi deb taxmin qilamiz. Ushbu kodlashda rus harflari 2 baytni egallaydi.

1) A va X, a "Y" maydonining qiymati 1 baytni, x "D" maydonining qiymati 2 baytni oladi
2) B va Y, b dagi “Vasya” qiymati 10 ta belgigacha bo‘sh joylar bilan to‘ldiriladi va 14 baytni, d dagi “Vasya” 8 baytni oladi.
3) C va K. Ikkala maydon ham NULL qiymatiga ega, ulardan keyin muhim maydonlar bor, shuning uchun ular 1 baytni egallaydi.
4) C va Z. Ikkala maydon ham NULL qiymatiga ega, lekin Z maydoni jadvaldagi oxirgi, shuning uchun u joy egallamaydi (0 bayt). C maydoni 1 baytni egallaydi.
5) K va Z. Oldingi holatga o'xshash. K maydonidagi qiymat 1 baytni egallaydi, Zda - 0.
6) I va J. Hujjatlarga ko'ra, ikkala qiymat ham 2 baytni oladi. Hujjatlardan olingan formuladan foydalanib uzunlikni hisoblaymiz: round( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J va X. J maydonidagi qiymat 2 baytni, X maydonidagi qiymat 2 baytni oladi.

Umuman olganda, to'g'ri variantlar: C va K, I va J, J va X.

T_I indeksining klasterlash omili taxminan qancha bo'ladi?

create table t (i integer);
 
insert into t select rownum from dual connect by level <= 10000;
 
create index t_i on t(i);

Taxminan o'nlab
Taxminan yuzlab
Taxminan minglab
Taxminan o'n minglab

Javob beringOracle hujjatlariga muvofiq (12.1 dan keltirilgan):

B-daraxt indeksi uchun indeks klasterlash omili indeks qiymatiga nisbatan qatorlarning jismoniy guruhlanishini o'lchaydi.

Indekslarni klasterlash omili optimallashtiruvchiga ma'lum so'rovlar uchun indekslarni skanerlash yoki to'liq jadvalni skanerlash samaraliroq ekanligini aniqlashga yordam beradi). Past klaster koeffitsienti samarali indeksni skanerlashni ko'rsatadi.

Jadvaldagi bloklar soniga yaqin bo'lgan klaster koeffitsienti indeks kaliti bo'yicha jadval bloklarida qatorlar jismoniy tartiblanganligini ko'rsatadi. Agar ma'lumotlar bazasi jadvalni to'liq skanerlashni amalga oshirsa, u holda ma'lumotlar bazasi indeks kaliti bo'yicha tartiblangan diskda saqlangan qatorlarni olishga intiladi. Qatorlar soniga yaqin bo'lgan klaster koeffitsienti satrlar indeks kalitiga nisbatan ma'lumotlar bazasi bloklari bo'ylab tasodifiy tarqalganligini ko'rsatadi. Agar ma'lumotlar bazasi jadvalni to'liq skanerdan o'tkazsa, ma'lumotlar bazasi ushbu indeks kaliti bo'yicha har qanday tartiblangan tartibda qatorlarni ololmaydi.

Bunday holda, ma'lumotlar ideal tarzda tartiblangan, shuning uchun klaster koeffitsienti jadvaldagi ishg'ol qilingan bloklar soniga teng yoki yaqin bo'ladi. 8 kilobaytlik standart blok hajmi uchun siz mingga yaqin tor raqam qiymatlari bitta blokga to'g'ri kelishini kutishingiz mumkin, shuning uchun bloklar soni va natijada klasterlash omili bo'ladi. o'nlab.

Quyidagi skript standart sozlamalarga ega oddiy ma'lumotlar bazasida N ning qaysi qiymatlarida muvaffaqiyatli bajariladi?

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

Javob beringOracle hujjatlariga muvofiq (11.2 dan keltirilgan):

Mantiqiy ma'lumotlar bazasi chegaralari

buyum
Limit turi
Cheklangan qiymat

katalog
Indekslangan ustunning umumiy hajmi
Ma'lumotlar bazasi blokining o'lchamining 75% minus qo'shimcha xarajatlar

Shunday qilib, indekslangan ustunlarning umumiy hajmi 6Kb dan oshmasligi kerak. Keyinchalik nima sodir bo'lishi tanlangan asosiy kodlashga bog'liq. AL32UTF8 kodlash uchun bitta belgi maksimal 4 baytni egallashi mumkin, shuning uchun eng yomon holatda, taxminan 6 ta belgi 1500 kilobaytga to'g'ri keladi. Shuning uchun, Oracle N = 400 da indeks yaratishga ruxsat bermaydi (eng yomon holatda kalit uzunligi 1600 belgi * 4 bayt + qator uzunlik bo'lsa), N = 200 (yoki undan kam) da indeks yaratish muammosiz ishlaydi.

APPEND maslahatiga ega INSERT operatori ma'lumotlarni to'g'ridan-to'g'ri rejimda yuklash uchun mo'ljallangan. Agar u trigger osilgan stolga qo'llanilsa nima bo'ladi?

Ma'lumotlar to'g'ridan-to'g'ri rejimda yuklanadi, trigger kutilganidek ishlaydi
Ma'lumotlar to'g'ridan-to'g'ri rejimda yuklanadi, lekin trigger bajarilmaydi
Ma'lumotlar an'anaviy rejimda yuklanadi, trigger kerakli tarzda ishlaydi
Ma'lumotlar an'anaviy rejimda yuklanadi, lekin trigger bajarilmaydi
Ma'lumotlar yuklanmaydi, xatolik qayd etiladi

Javob beringAsosan, bu ko'proq mantiq masalasidir. To'g'ri javobni topish uchun men quyidagi fikrlash modelini taklif qilaman:

To'g'ridan-to'g'ri rejimda kiritish yuqori tezlikni ta'minlaydigan SQL dvigatelini chetlab o'tib, ma'lumotlar blokini to'g'ridan-to'g'ri shakllantirish orqali amalga oshiriladi. Shunday qilib, triggerning bajarilishini ta'minlash juda qiyin, agar imkonsiz bo'lsa va buning ma'nosi yo'q, chunki u hali ham kiritishni tubdan sekinlashtiradi.
Triggerning bajarilmasligi, agar jadvaldagi ma'lumotlar bir xil bo'lsa, umuman ma'lumotlar bazasining holati (boshqa jadvallar) ushbu ma'lumotlar kiritilgan rejimga bog'liq bo'lishiga olib keladi. Bu aniq ma'lumotlar yaxlitligini buzadi va ishlab chiqarishda yechim sifatida qo'llanilmaydi.
So'ralgan operatsiyani bajara olmaslik odatda xato deb hisoblanadi. Lekin bu yerda shuni unutmasligimiz kerakki, APPEND ishoradir va maslahatlarning umumiy mantig'i shundan iboratki, agar iloji bo'lsa, ular hisobga olinadi, lekin agar bo'lmasa, operator maslahatni hisobga olmasdan bajariladi.

Shunday qilib, kutilgan javob ma'lumotlar normal (SQL) rejimida yuklanadi, trigger ishga tushadi.

Oracle hujjatlariga muvofiq (8.04 dan keltirilgan):

Cheklovlarning buzilishi bayonotning odatiy qo'shish yo'lidan foydalangan holda, ogohlantirishlar yoki xato xabarlarisiz ketma-ket bajarilishiga olib keladi. Istisno - bu xato xabarlarni keltirib chiqarishi mumkin bo'lgan tranzaktsiyada bir xil jadvalga bir necha marta kirishni cheklash.
Misol uchun, agar jadvalda triggerlar yoki referent yaxlitligi mavjud bo'lsa, to'g'ridan-to'g'ri yuklangan INSERT (ketma-ket yoki parallel), shuningdek, agar mavjud bo'lsa, PARALLEL ko'rsatmasi yoki bandidan foydalanishga harakat qilganingizda APPEND maslahati e'tiborga olinmaydi.

Quyidagi skript bajarilganda nima bo'ladi?

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

Muvaffaqiyatli yakunlash
Sintaksis xatosi tufayli xato
Xato: avtonom tranzaksiya haqiqiy emas
Qo‘ng‘iroqlarni joylashtirishning maksimal chegarasidan oshib ketish bilan bog‘liq xato
Tashqi kalitni buzish xatosi
Qulflar bilan bog'liq xato

Javob beringJadval va trigger juda to'g'ri yaratilgan va bu operatsiya muammolarga olib kelmasligi kerak. Triggerda avtonom tranzaktsiyalarga ham ruxsat beriladi, aks holda, masalan, jurnalga kirish mumkin bo'lmaydi.

Birinchi qatorni kiritgandan so'ng, muvaffaqiyatli ishga tushirish ikkinchi qatorning kiritilishiga olib keladi, bu esa qo'ng'iroqlarning maksimal joylashuvidan oshib ketganligi sababli bayonot muvaffaqiyatsizlikka uchraguncha, triggerning yana yonishi, uchinchi qatorni kiritish va hokazo. Biroq, yana bir nozik nuqta o'ynaydi. Trigger ishga tushirilganda, birinchi kiritilgan yozuv uchun majburiyat hali tugallanmagan. Shuning uchun, avtonom tranzaksiyada ishlaydigan trigger jadvalga tashqi kalitga hali amalga oshirilmagan yozuvga havola qiluvchi qatorni kiritishga harakat qiladi. Bu kutishga olib keladi (avtonom tranzaksiya asosiy tranzaksiya ma'lumotlarni kirita oladimi yoki yo'qligini ko'rish uchun bajarilishini kutadi) va shu bilan birga asosiy tranzaksiya ishga tushirilgandan so'ng avtonom tranzaksiya ishlashni davom ettirishni kutadi. Tugallanish yuzaga keladi va buning natijasida avtonom tranzaksiya qulflar bilan bog'liq sabablarga ko'ra bekor qilinadi..

So'rovda faqat ro'yxatdan o'tgan foydalanuvchilar ishtirok etishlari mumkin. tizimga kirishiltimos.

Bu qiyin edi?

Ikki barmoq kabi, men darhol hamma narsani to'g'ri qaror qildim.
Haqiqatan ham emas, men bir nechta savolda xato qildim.
Men yarmini to'g'ri hal qildim.
Men javobni ikki marta taxmin qildim!
Izohlarda yozaman

14 foydalanuvchi ovoz berdi. 10 nafar foydalanuvchi betaraf qolgan.

Manba: www.habr.com

Highload++ Siberia 2019 izidan borish - Oracle-da 8 ta vazifa

Quyidagi skriptni bajarish natijasida biz ko'radigan maksimal ketma-ketlik qiymati qanday?

Quyidagi skriptni ishga tushirish natijasida jadvalda nechta qator bo'ladi?

Quyidagi skriptni ishga tushirish natijasida jadvalda nechta qator bo'ladi?

Qaysi juft qiymatlar blokda bir xil joy egallaydi?

T_I indeksining klasterlash omili taxminan qancha bo'ladi?

Quyidagi skript standart sozlamalarga ega oddiy ma'lumotlar bazasida N ning qaysi qiymatlarida muvaffaqiyatli bajariladi?

APPEND maslahatiga ega INSERT operatori ma'lumotlarni to'g'ridan-to'g'ri rejimda yuklash uchun mo'ljallangan. Agar u trigger osilgan stolga qo'llanilsa nima bo'ladi?

Quyidagi skript bajarilganda nima bo'ladi?

Bu qiyin edi?

a Izoh qo'shish Bu savolga javob bekor