Highload++ Siberia 2019-un izi ilə - Oracle-da 8 tapşırıq

Привет!

24-25 iyun tarixlərində Novosibirskdə “Highload++ Siberia 2019” konfransı keçirildi.Uşaqlarımız da orada idilər. hesabat “Oracle konteyner verilənlər bazaları (CDB/PDB) və onların proqram təminatının inkişafı üçün praktiki istifadəsi”, bir az sonra mətn versiyasını dərc edəcəyik. Gözəl idi, təşəkkürlər oleqbunin təşkilata, eləcə də gələn hər kəsə.

Bu yazıda Oracle biliklərinizi sınaya bilməniz üçün stendimizdə qarşılaşdığımız problemləri sizinlə bölüşmək istərdik. Kəsimin altında 8 problem, cavab variantları və izahat var.

Aşağıdakı skriptin icrası nəticəsində görəcəyimiz maksimum ardıcıllıq dəyəri nədir?

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
• Xeyr, xəta olacaq

CavabOracle sənədlərinə əsasən (8.1.6-dan sitat gətirilmişdir):
Tək SQL ifadəsi daxilində Oracle ardıcıllığı hər sətirdə yalnız bir dəfə artıracaq. Əgər bəyanatda ardıcıllıq üçün NEXTVAL-a birdən çox istinad varsa, Oracle ardıcıllığı bir dəfə artırır və NEXTVAL-ın bütün hadisələri üçün eyni dəyəri qaytarır. İfadə həm CURRVAL, həm də NEXTVAL-a istinadlar ehtiva edərsə, Oracle ardıcıllığı artırır və bəyanatdakı sıradan asılı olmayaraq həm CURRVAL, həm də NEXTVAL üçün eyni dəyəri qaytarır.

Belə ki, maksimum dəyər xətlərin sayına uyğun olacaq, yəni 5.

Aşağıdakı skriptin icrası nəticəsində cədvəldə neçə sıra olacaq?

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

CavabOracle sənədlərinə əsasən (11.2-dan sitat gətirilmişdir):

Hər hansı SQL ifadəsini yerinə yetirməzdən əvvəl Oracle gizli saxlama nöqtəsini qeyd edir (sizin üçün əlçatan deyil). Sonra, bəyanat uğursuz olarsa, Oracle onu avtomatik olaraq geri qaytarır və müvafiq xəta kodunu SQLCA-da SQLCODE-a qaytarır. Məsələn, INSERT ifadəsi unikal indeksə dublikat dəyər daxil etməyə cəhd edərək xətaya səbəb olarsa, bəyanat geri qaytarılır.

Müştəridən HP-yə zəng etmək də vahid bəyanat kimi qəbul edilir və işlənir. Beləliklə, ilk HP zəngi üç qeyd daxil etməklə uğurla tamamlanır; ikinci HP çağırışı xəta ilə başa çatır və daxil edə bildiyi dördüncü qeydi geri qaytarır; üçüncü zəng uğursuz oldu, və cədvəldə üç qeyd var.

Aşağıdakı skriptin icrası nəticəsində cədvəldə neçə sıra olacaq?

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

CavabOracle sənədlərinə əsasən (11.2-dan sitat gətirilmişdir):

Yoxlama məhdudiyyəti sizə cədvəldəki hər bir sıranın təmin etməli olduğu şərti təyin etməyə imkan verir. Məhdudiyyəti təmin etmək üçün cədvəldəki hər bir sətir şərti ya TRUE, ya da naməlum (null səbəbindən) etməlidir. Oracle müəyyən bir sətir üçün yoxlama məhdudiyyəti şərtini qiymətləndirdikdə, şərtdəki hər hansı sütun adları həmin sətirdəki sütun dəyərlərinə istinad edir.

Beləliklə, null dəyəri yoxlamadan keçəcək və anonim blok 3 dəyərini daxil etməyə cəhd edilənə qədər uğurla icra ediləcək. Bundan sonra, xətanın idarə edilməsi bloku istisnanı təmizləyəcək, heç bir geri çəkilmə baş verməyəcək və cədvəldə dörd cərgə qalacaq 1, null, 2 və yenidən null dəyərləri ilə.

Hansı dəyər cütləri blokda eyni miqdarda yer tutacaq?

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 və X
• B və Y
• C və K
• C və Z
• K və Z
• Mən və J
• J və X
• Hamısı sadalanıb

CavabBurada Oracle-da müxtəlif növ məlumatların saxlanmasına dair sənədlərdən (12.1.0.2) çıxarışlar verilmişdir.

CHAR məlumat növü
CHAR məlumat növü verilənlər bazası simvol dəstində sabit uzunluqlu simvol sətirini təyin edir. Siz verilənlər bazanızı yaratdığınız zaman verilənlər bazası simvol dəstini təyin edirsiniz. Oracle CHAR sütununda saxlanılan bütün dəyərlərin seçilmiş uzunluq semantikasında ölçü ilə müəyyən edilmiş uzunluğa malik olmasını təmin edir. Sütun uzunluğundan daha qısa bir dəyər daxil etsəniz, Oracle dəyəri sütun uzunluğuna boşaldır.

VARCHAR2 Məlumat növü
VARCHAR2 məlumat növü verilənlər bazası simvol dəstində dəyişən uzunluqlu simvol sətirini təyin edir. Siz verilənlər bazanızı yaratdığınız zaman verilənlər bazası simvol dəstini təyin edirsiniz. Oracle simvol dəyərini VARCHAR2 sütununda sizin göstərdiyiniz kimi, heç bir boşluq olmadan saxlayır, bu şərtlə ki, dəyər sütunun uzunluğundan çox olmasın.

NUMBER Data Növü
NUMBER məlumat növü sıfır, eləcə də 1.0 x 10-130-dan 1.0 x 10126 daxil olmaqla, mütləq dəyərləri olan müsbət və mənfi sabit ədədləri saxlayır. Dəyəri mütləq dəyəri ondan böyük və ya ona bərabər olan arifmetik ifadəni göstərsəniz 1.0 x 10126, sonra Oracle xəta qaytarır. Hər NUMBER dəyəri 1 ilə 22 bayt tələb edir. Bunu nəzərə alaraq, müəyyən bir rəqəmsal məlumat dəyəri NUMBER(p) üçün baytlarla sütun ölçüsü, burada p verilmiş dəyərin dəqiqliyi aşağıdakı düsturla hesablana bilər: ROUND((uzunluq(p)+s)/2))+1 burada s sıfıra bərabərdir, əgər ədəd müsbətdirsə, s 1-ə bərabərdir, əgər ədəd mənfi olarsa.

Bundan əlavə, Null dəyərlərin saxlanması ilə bağlı sənədlərdən bir parça götürək.

Null sütunda dəyərin olmamasıdır. Nulllar çatışmayan, naməlum və ya uyğun olmayan məlumatları göstərir. Nulllar verilənlər bazasında saxlanılır, əgər onlar verilənlərin dəyərləri olan sütunlar arasında qalırlar. Bu hallarda, sütunun uzunluğunu (sıfır) saxlamaq üçün 1 bayt tələb olunur. Sıradakı boşluqlar yaddaş tələb etmir, çünki yeni sətir başlığı əvvəlki sətirdə qalan sütunların sıfır olduğunu bildirir. Məsələn, cədvəlin son üç sütunu boşdursa, bu sütunlar üçün heç bir məlumat saxlanılmır.

Bu məlumatlara əsaslanaraq, məntiq qururuq. Güman edirik ki, verilənlər bazası AL32UTF8 kodlaşdırmasından istifadə edir. Bu kodlaşdırmada rus hərfləri 2 bayt tutacaq.

1) A və X, a 'Y' sahəsinin dəyəri 1 bayt, x 'D' sahəsinin dəyəri 2 bayt alır
2) B və Y, b-də 'Vasya' dəyər 10 simvola qədər boşluqla doldurulacaq və 14 bayt, d-də 'Vasya' 8 bayt alacaq.
3) C və K. Hər iki sahə NULL dəyərinə malikdir, onlardan sonra əhəmiyyətli sahələr var, ona görə də onlar 1 bayt tutur.
4) C və Z. Hər iki sahə NULL dəyərinə malikdir, lakin Z sahəsi cədvəldə sonuncudur, ona görə də yer tutmur (0 bayt). C sahəsi 1 bayt yer tutur.
5) K və Z. Əvvəlki hal kimi. K sahəsindəki dəyər 1 bayt, Z-də isə 0-dır.
6) I və J. Sənədlərə görə, hər iki dəyər 2 bayt tutacaq. Sənədlərdən götürülmüş düsturdan istifadə edərək uzunluğu hesablayırıq: dəyirmi( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J və X. J sahəsindəki qiymət 2 bayt, X sahəsindəki qiymət 2 bayt alacaq.

Ümumilikdə düzgün variantlar bunlardır: C və K, I və J, J və X.

T_I indeksinin qruplaşma faktoru təxminən nə qədər olacaq?

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

• Təxminən onlarla
• Yüzlərlə
• Təxminən minlərlə
• Təxminən on minlərlə

CavabOracle sənədlərinə əsasən (12.1-dan sitat gətirilmişdir):

B-ağac indeksi üçün indeks klasterləşdirmə faktoru indeks dəyərinə münasibətdə cərgələrin fiziki qruplaşdırılmasını ölçür.

İndeks klasterləşdirmə faktoru optimallaşdırıcıya müəyyən sorğular üçün indeks skanının və ya tam cədvəlin skanının daha səmərəli olub-olmadığına qərar verməyə kömək edir). Aşağı qruplaşma faktoru effektiv indeks taramasını göstərir.

Cədvəldəki blokların sayına yaxın olan klaster faktoru, sıraların indeks açarı ilə cədvəl bloklarında fiziki olaraq sıralandığını göstərir. Əgər verilənlər bazası cədvəlin tam skanını həyata keçirirsə, verilənlər bazası sətirləri indeks açarı ilə çeşidlənmiş diskdə saxlandıqları üçün geri almağa meyllidir. Satırların sayına yaxın olan klaster faktoru satırların indeks açarı ilə əlaqəli olaraq verilənlər bazası blokları arasında təsadüfi səpələndiyini göstərir. Əgər verilənlər bazası cədvəlin tam skanını həyata keçirirsə, verilənlər bazası bu indeks açarı ilə hər hansı çeşidlənmiş sıra ilə sətirləri əldə etməyəcəkdir.

Bu halda, məlumatlar ideal şəkildə çeşidlənir, buna görə də klasterləşmə faktoru cədvəldəki işğal edilmiş blokların sayına bərabər və ya yaxın olacaqdır. 8 kilobaytlıq standart blok ölçüsü üçün təxminən min dar nömrə dəyərinin bir bloka sığacağını gözləmək olar, buna görə də blokların sayı və nəticədə qruplaşma amili olacaqdır. onlarla.

Aşağıdakı skript standart parametrləri olan adi verilənlər bazasında N-nin hansı dəyərlərində uğurla yerinə yetiriləcək?

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

CavabOracle sənədlərinə əsasən (11.2-dan sitat gətirilmişdir):

Məntiqi verilənlər bazası limitləri

maddə
Limit növü
Limit Dəyəri

İndekslər
İndekslənmiş sütunun ümumi ölçüsü
Verilənlər bazası blokunun ölçüsünün 75% -i mənfi bir qədər əlavə xərc

Beləliklə, indeksləşdirilmiş sütunların ümumi ölçüsü 6Kb-dən çox olmamalıdır. Bundan sonra baş verənlər seçilmiş əsas kodlaşdırmadan asılıdır. AL32UTF8 kodlaşdırması üçün bir simvol maksimum 4 bayt tuta bilər, buna görə də ən pis halda, təxminən 6 simvol 1500 kilobayta sığacaq. Buna görə də, Oracle N = 400 (ən pis açar açar uzunluğu 1600 simvol * 4 bayt + sıra uzunluğu olduqda) indeks yaratmağa icazə verməyəcək. N = 200 (və ya daha az) indeksin yaradılması problemsiz işləyəcək.

APPEND işarəsi ilə INSERT operatoru verilənlərin birbaşa rejimdə yüklənməsi üçün nəzərdə tutulub. Tətiyin asılı olduğu masaya tətbiq edilərsə nə olar?

• Məlumat birbaşa rejimdə yüklənəcək, tətik gözlənildiyi kimi işləyəcək
• Məlumat birbaşa rejimdə yüklənəcək, lakin tetikleyici icra edilməyəcək
• Məlumatlar adi rejimdə yüklənəcək, trigger lazım olduğu kimi işləyəcək
• Məlumatlar adi rejimdə yüklənəcək, lakin tetikleyici icra edilməyəcək
• Məlumat yüklənməyəcək, xəta qeydə alınacaq

CavabƏsasən, bu daha çox məntiq məsələsidir. Düzgün cavabı tapmaq üçün aşağıdakı əsaslandırma modelini təklif edərdim:

1. Birbaşa rejimdə daxiletmə yüksək sürəti təmin edən SQL mühərrikindən yan keçərək məlumat blokunun birbaşa formalaşdırılması ilə həyata keçirilir. Beləliklə, tetikleyicinin icrasını təmin etmək çox çətindir, hətta qeyri-mümkündür və bunun heç bir mənası yoxdur, çünki o, hələ də daxiletməni kökündən yavaşlatacaq.
2. Tətiyi yerinə yetirməmək ona gətirib çıxaracaq ki, cədvəldəki məlumatlar eyni olarsa, bütövlükdə verilənlər bazasının vəziyyəti (digər cədvəllər) bu məlumatların daxil edildiyi rejimdən asılı olacaq. Bu, açıq şəkildə məlumatların bütövlüyünü məhv edəcək və istehsalda həll yolu kimi tətbiq edilə bilməz.
3. Tələb olunan əməliyyatı yerinə yetirə bilməmək ümumiyyətlə səhv kimi qəbul edilir. Amma burada yadda saxlamalıyıq ki, APPEND işarədir və göstərişlərin ümumi məntiqi ondan ibarətdir ki, mümkün olduqda onlar nəzərə alınır, yoxsa, operator işarə nəzərə alınmadan yerinə yetirilir.

Beləliklə, gözlənilən cavab budur məlumatlar normal (SQL) rejimində yüklənəcək, tətik işə düşəcək.

Oracle sənədlərinə əsasən (8.04-dan sitat gətirilmişdir):

Məhdudiyyətlərin pozulması bəyanatın xəbərdarlıq və ya səhv mesajı olmadan şərti daxiletmə yolundan istifadə edərək ardıcıl icrasına səbəb olacaq. İstisna, xəta mesajlarına səbəb ola biləcək əməliyyatda eyni cədvələ bir dəfədən çox daxil olan ifadələrin məhdudlaşdırılmasıdır.
Məsələn, cədvəldə tetikler və ya istinad bütövlüyü varsa, onda siz birbaşa yüklü INSERT (seriyalı və ya paralel), eləcə də əgər varsa, PARALLEL işarəsi və ya bəndindən istifadə etməyə çalışdığınız zaman APPEND ipucu nəzərə alınmayacaq.

Aşağıdakı skript icra edildikdə nə baş verəcək?

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

• Uğurlu icra
• Sintaksis səhvinə görə uğursuzluq
• Xəta: Avtonom Tranzaksiya Etibarlı Deyil
• Maksimum zəng yuvasının keçməsi ilə bağlı xəta
• Xarici Açarın pozulması xətası
• Kilidlərlə əlaqəli xəta

CavabCədvəl və tetikleyici olduqca düzgün yaradılmışdır və bu əməliyyat problemlərə səbəb olmamalıdır. Tətikdə avtonom əməliyyatlara da icazə verilir, əks halda, məsələn, giriş mümkün olmazdı.

Birinci cərgə daxil edildikdən sonra müvəffəqiyyətli tətik atəşi ikinci sıranın daxil edilməsinə səbəb olacaq, bu da tətiyin yenidən işə düşməsinə, üçüncü sıranın daxil edilməsinə və s. zənglərin maksimum yuvasını aşması səbəbindən ifadə uğursuzluğa düçar olacaq. Bununla belə, başqa bir incə məqam ortaya çıxır. Tətik icra edildiyi zaman ilk daxil edilmiş qeyd üçün öhdəlik hələ tamamlanmayıb. Buna görə də, avtonom tranzaksiyada işləyən trigger cədvələ xarici açarı hələ imzalanmamış qeydə istinad edən bir sıra daxil etməyə çalışır. Bu, gözləmə ilə nəticələnir (avtonom əməliyyat məlumat daxil edə bilməyəcəyini görmək üçün əsas əməliyyatın öhdəliyini gözləyir) və eyni zamanda əsas tranzaksiya tətikdən sonra avtonom əməliyyatın işləməsini gözləyir. Çıxılmaz vəziyyət yaranır və nəticədə kilidlərlə bağlı səbəblərə görə avtonom əməliyyat ləğv edilir..

Sorğuda yalnız qeydiyyatdan keçmiş istifadəçilər iştirak edə bilər. Daxil olunxahiş edirəm.

Çətin idi?

• İki barmaq kimi dərhal hər şeyi düzgün qərar verdim.

• Əslində yox, bir neçə sualda yanılmışam.

• Yarısını düzgün həll etdim.

• Cavabı iki dəfə təxmin etdim!

• Şərhlərdə yazacam

14 istifadəçi səs verib. 10 istifadəçi bitərəf qalıb.

Mənbə: www.habr.com