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-დან):

შემოწმების შეზღუდვა საშუალებას გაძლევთ მიუთითოთ პირობა, რომელიც უნდა აკმაყოფილებდეს ცხრილის თითოეულ მწკრივს. შეზღუდვის დასაკმაყოფილებლად, ცხრილის თითოეული მწკრივი უნდა იყოს პირობა TRUE ან უცნობი (ნულის გამო). როდესაც 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
• B და Y
• C და K
• C და Z
• კ და ზ
• მე და ჯ
• J და X
• ყველა ჩამოთვლილი

პასუხიაქ მოცემულია ნაწყვეტები დოკუმენტაციის (12.1.0.2) Oracle-ში სხვადასხვა ტიპის მონაცემების შენახვის შესახებ.

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 არის სვეტში მნიშვნელობის არარსებობა. ნული მიუთითებს დაკარგული, უცნობი ან შეუსაბამო მონაცემებზე. ნული ინახება მონაცემთა ბაზაში, თუ ისინი მოხვდება მონაცემთა მნიშვნელობების მქონე სვეტებს შორის. ამ შემთხვევაში, მათ სჭირდებათ 1 ბაიტი სვეტის სიგრძის შესანახად (ნულოვანი). ზედიზედ მიმავალი ნული არ საჭიროებს შენახვას, რადგან ახალი მწკრივის სათაური მიანიშნებს, რომ წინა მწკრივში დარჩენილი სვეტები ნულოვანია. მაგალითად, თუ ცხრილის ბოლო სამი სვეტი ნულოვანია, მაშინ ამ სვეტებისთვის მონაცემები არ ინახება.

ამ მონაცემებზე დაყრდნობით ვაშენებთ მსჯელობას. ჩვენ ვვარაუდობთ, რომ მონაცემთა ბაზა იყენებს AL32UTF8 დაშიფვრას. ამ კოდირებით რუსული ასოები დაიკავებს 2 ბაიტს.

1) A და X, ველის მნიშვნელობა a 'Y' იღებს 1 ბაიტს, ველის მნიშვნელობა x 'D' იღებს 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 და Z. წინა შემთხვევის მსგავსი. მნიშვნელობა 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-ხის ინდექსისთვის, ინდექსის კლასტერული ფაქტორი ზომავს მწკრივების ფიზიკურ დაჯგუფებას ინდექსის მნიშვნელობასთან მიმართებაში.

ინდექსის დაჯგუფების ფაქტორი ეხმარება ოპტიმიზატორს გადაწყვიტოს, უფრო ეფექტურია თუ არა ინდექსის სკანირება ან სრული ცხრილის სკანირება გარკვეული მოთხოვნებისთვის). დაბალი კლასტერული ფაქტორი მიუთითებს ინდექსის ეფექტურ სკანირებაზე.

კლასტერული ფაქტორი, რომელიც ახლოსაა ცხრილის ბლოკების რაოდენობასთან, მიუთითებს, რომ სტრიქონები ფიზიკურად დალაგებულია ცხრილის ბლოკებში ინდექსის გასაღებით. თუ მონაცემთა ბაზა ასრულებს ცხრილის სრულ სკანირებას, მაშინ მონაცემთა ბაზა მიდრეკილია მოიძიოს რიგები, რადგან ისინი ინახება დისკზე დალაგებული ინდექსის გასაღებით. კლასტერული ფაქტორი, რომელიც ახლოსაა მწკრივების რაოდენობასთან, მიუთითებს, რომ რიგები შემთხვევით მიმოფანტულია მონაცემთა ბაზის ბლოკებში ინდექსის გასაღებთან მიმართებაში. თუ მონაცემთა ბაზა ასრულებს ცხრილის სრულ სკანირებას, მაშინ მონაცემთა ბაზა არ მოიძიებს სტრიქონებს ამ ინდექსის კლავიშის მიხედვით დალაგებული თანმიმდევრობით.

ამ შემთხვევაში, მონაცემები იდეალურად არის დალაგებული, ასე რომ, კლასტერული ფაქტორი იქნება ცხრილის დაკავებული ბლოკების რაოდენობის ტოლი ან ახლოს. 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-დან):

მონაცემთა ბაზის ლოგიკური ლიმიტები

Item
ლიმიტის ტიპი
Limit Value

ინდექსები
ინდექსირებული სვეტის მთლიანი ზომა
მონაცემთა ბაზის ბლოკის ზომის 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);

• წარმატებული აღსრულება
• წარუმატებლობა სინტაქსის შეცდომის გამო
• შეცდომა: ავტონომიური ტრანზაქცია არასწორია
• შეცდომა, რომელიც დაკავშირებულია მაქსიმალური ზარის ჩადგმის გადაჭარბებასთან
• უცხო გასაღების დარღვევის შეცდომა
• საკეტებთან დაკავშირებული შეცდომა

პასუხიცხრილი და ტრიგერი საკმაოდ სწორად არის შექმნილი და ამ ოპერაციამ არ უნდა გამოიწვიოს პრობლემები. ასევე დასაშვებია ავტონომიური ტრანზაქციები ტრიგერში, სხვაგვარად შეუძლებელი იქნება, მაგალითად, ლოგირება.

პირველი რიგის ჩასმის შემდეგ, ტრიგერის წარმატებული გასროლა გამოიწვევს მეორე რიგის ჩასმას, რაც გამოიწვევს ტრიგერის ხელახლა გაშვებას, მესამე რიგის ჩასმას და ა.შ. თუმცა, კიდევ ერთი დახვეწილი პუნქტი მოქმედებს. ტრიგერის შესრულების დროს, დასრულება ჯერ არ დასრულებულა პირველი ჩასმული ჩანაწერისთვის. მაშასადამე, ავტონომიურ ტრანზაქციაში გაშვებული ტრიგერი ცდილობს ცხრილში ჩასვას მწკრივი, რომელიც მიმართავს უცხო კლავიშს ჩანაწერზე, რომელიც ჯერ არ არის ჩადენილი. ეს იწვევს ლოდინს (ავტონომიური ტრანზაქცია ელოდება მთავარ ტრანზაქციას, რათა დაინახოს, შეუძლია თუ არა მონაცემების ჩასმა) და ამავდროულად მთავარი ტრანზაქცია ელოდება ავტონომიური ტრანზაქციის მუშაობას ტრიგერის შემდეგ. ხდება ჩიხი და, შედეგად, ავტონომიური ტრანზაქცია გაუქმებულია საკეტებთან დაკავშირებული მიზეზების გამო.

გამოკითხვაში მონაწილეობა შეუძლიათ მხოლოდ დარეგისტრირებულ მომხმარებლებს. Შებრძანდითგთხოვთ

რთული იყო?

• ორი თითივით მაშინვე სწორად გადავწყვიტე ყველაფერი.

• ნამდვილად არა, რამდენიმე კითხვაზე ვცდებოდი.

• ნახევარი სწორად მოვაგვარე.

• პასუხი ორჯერ გამოვიცანი!

• კომენტარებში დავწერ

მისცა ხმა 14 მომხმარებელმა. 10-მა მომხმარებელმა თავი შეიკავა.

წყარო: www.habr.com